Creating an moving running total

[NicholasL]

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.

[BenMoss · Accepted 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

[NicholasL]

Perfect solution. Thanks for the quick response.

[Ye_Olde_Baller]

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?

[Ye_Olde_Baller]

Sorry for the crossed out font.

[martinme8]

This worked perfectly for me too! Thanks Ben Moss - Awesome!