ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

How to aggregate adjustments based on date?

sheidari
8 - Asteroid

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:

  1. 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.
  2. 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).

 

sheidari_0-1620164008359.png

 

phottovy
11 - Bolide

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.

arathirajeev
6 - Meteoroid

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

sheidari
8 - Asteroid

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
8 - Asteroid

@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. 

phottovy
11 - Bolide

@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. 

sheidari
8 - Asteroid

@phottovy 

 

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. 

 

THanks for the assist on this one!

Labels