This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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).
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.
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!
@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.
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.
Looks like it's still exponentially growing (up to 300 GB and still going). i switched it to a join tool and that seems to be working ok. it's dropping some records but looks like it should as expected.