I have a dataset that has the following fields: * ID * Period * Adjustment * Sales For every adjustment, I want to adjust the sales based on the adjustment by doing a reverse calculation for each record but only beginning from the date of the adjustment. Example: * Adjustment 1 is for $3,122 posted on 2021006 so i want to calculate the % of total beginning from 2021006 going backwards until the earliest date. * Adjustment 2 is for $15,242 posted on 2019012 so i want to calculate the % of total beginning from 2019012 going backwards until the earliest date. Below is an example of the original sample data and expected output: (if there's a way to keep the % of total in a single column for all adjustments, that would be ideal, given that there is no way to know how many adjustments a particular ID can have).

How to aggregate adjustments based on date?

I have a dataset that has the following fields:

ID
Period
Adjustment
Sales

For every adjustment, I want to adjust the sales based on the adjustment by doing a reverse calculation for each record but only beginning from the date of the adjustment.

Example:

Adjustment 1 is for $3,122 posted on 2021006 so i want to calculate the % of total beginning from 2021006 going backwards until the earliest date.
Adjustment 2 is for $15,242 posted on 2019012 so i want to calculate the % of total beginning from 2019012 going backwards until the earliest date.

Below is an example of the original sample data and expected output: (if there's a way to keep the % of total in a single column for all adjustments, that would be ideal, given that there is no way to know how many adjustments a particular ID can have).

Macros

Dynamic Processing

Preparation

Iterative Macro

Accepted answers

phottovy

@sheidari

Yep, we should get rid of the append tool. Attached is slightly modified version that uses the "Generate Rows" tool instead of the append tool. To get it to work, I had to convert your period column into a date field, then I calculate all of the dates that come before the adjustment row. This allows for a normal join instead of the append tool. Hopefully this runs faster.

Aggregate Adjustments V2.yxmd

All comments

phottovy

Hi @sheidari

This was a fun one to try and tackle. The attached workflow should get you what you are looking for. I used an append tool which might cause some issues if you have a much larger data set but this seems to work with the info you provided.

Aggregate Adjustments.yxmd

arathirajeev

I enjoyed solving this one! This is just my approach and maybe there's a much simpler way to do it.

Adjustments_aggregation.yxmd

sheidari

Hi @phottovy,

This works exactly as I need it but unfortunately the append tool is creating way too many records. My dataset is around 230K records so I let it run for about 10 minutes and it was up to 98 GB at 55% completion.

Thank you for taking a stab at it - I really appreciate the assist!

Any other way to solve for this more efficiently?

sheidari

@arathirajeev Thanks for trying this one out - it's definitely a challenging one.

Yours works well but it stops short of calculating all the way through. The key is that the reverse calculation begin on the date of the adjustment but should continue going backwards until the earliest date.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703