I am trying to create a moving running total across a grouped by subset of data. The table below gives an example of the type of data set I am trying to summarise. I want to create a rolling "Last 12 Month Total" for the Value column, grouped by the Entity ID, Attribute 1 and Attribute 2 columns, where the total in the Running LTM Total column is the sum of all values in the group by set where the Month is within 12 months (or 365 days) of the month on the row.
I am new to Alteryx and so I am hoping that I missing something quite simple, but I cannot figure out how to do it.
Row ID | Entity ID | Month | Attribute 1 | Attribute 2 | Value | Running LTM Total | Notes |
1 | 123 | 31-01-17 | BBB | CCC | 5 | 5 | No previous month's values for this group by set and so the total is the value |
2 | 123 | 31-01-17 | BBB | DDD | 10 | 10 | No previous month's values for this group by set and so the total is the value |
3 | 123 | 31-12-17 | BBB | CCC | 10 | 15 | Row 1 matches the group by set and the month on row 1 is within the period 12 months before the month of the current row and so the total is row 1 + row 3 |
4 | 123 | 31-12-17 | BBB | DDD | 15 | 25 | Row 2 matches the group by set and the month on row 2 is within the period 12 months before the month of the current row and so the total is row 2 + row 4 |
5 | 123 | 31-01-18 | BBB | CCC | 15 | 25 | Row 1 matches the group by set, but the month on row 1 is not within the period of 12 months before the month of the current row and so it is dropped. Row 3 matches the group by set and is within the 12 month period and is included. |
6 | 123 | 31-01-18 | BBB | DDD | 20 | 34 | Row 2 matches the group by set, but the month on row 2 is not within the period of 12 months before the month of the current row and so it is dropped. Row 4 matches the group by set and is within the 12 month period and is included. |
Solved! Go to Solution.
This is a fun problem to solve and your friend here is the generate rows tool.
For each row you need to identify the 12 months that you need to find values for. This can be done using as mentioned, the generate rows tool.
You can then perform a join against these 'needed values' and then perform your running total calculation.
Attached is an example using your sample data.
Ben
Perfect solution. Thanks for the quick response.
How would you do this if your Entities change? Say you have multiple groups in one data set and you want to do this all in one workflow?
Sorry for the crossed out font.
This worked perfectly for me too! Thanks Ben Moss - Awesome!