Running Total For Last "N" Days Fixed on Specific Fields

[Trent_Montgomery]

I am trying to calculate a moving sum that is "N" days from the date specified on the row. I also need this calculation to fix itself to both distinct store and sku combination (as shown below). In the scenario below I am creating a fourth column that calculates a moving running total for the last 5 days (including the current date). In other tools such as Tableau I would calculate this as a window sum and fix the calculation at store and sku.

Store	SKU	Date	Sales	5 Day Running Sum
1	1111	1/1/2021	5	5
2	1111	1/1/2021	5	5
1	1111	1/2/2021	6	11
2	1111	1/2/2021	6	11
1	1111	1/3/2021	7	18
2	1111	1/3/2021	8	19
1	1111	1/4/2021	3	21
2	1111	1/4/2021	3	22
1	1111	1/5/2021	4	25
2	1111	1/5/2021	5	27
1	1111	1/6/2021	1	21
2	1111	1/6/2021	1	23
1	1111	1/7/2021	5	20
2	1111	1/7/2021	5	22
1	1111	1/8/2021	2	15
2	1111	1/8/2021	2	16
1	1111	1/9/2021	4	16
2	1111	1/9/2021	4	17
1	1111	1/10/2021	6	18
2	1111	1/10/2021	6	18
1	1111	1/11/2021	8	25
2	1111	1/11/2021	8	25
1	1111	1/12/2021	9	29
2	1111	1/12/2021	5	25
1	1111	1/13/2021	2	29
2	1111	1/13/2021	2	25
1	1111	1/14/2021	6	31
2	1111	1/14/2021	6	27

[Luke_C]

Hi @Trent_Montgomery 

I took a pass at this using the multi-row formula tool. See attached. I grouped by the Store and SKU and was able to get the same results as your sample data 

[Trent_Montgomery]

@Luke_C thanks for the solution, however, I still have one concern: 

This solution works if every store has every calendar date present in the data, but if a date is missing (ie. the store did not sell any units on a particular date) it could include sales that happened more than 5 days ago? Is their a way to complete this calc with a date calc included.

[Luke_C]

Hi @Trent_Montgomery 

Yes, definitely possible. Here's an example of how you could do it:

1. Convert dates to actual valid alteryx dates
2. Identify the max/min date for each store/SKU combo
3. Generate a row for every date in that range
4. Join that up with the original data and union the dropouts from the R anchor (this would represent dates dates not found in the source). I deleted a record from your data to test this
5. Sort the data and run the multi-row formula tool

[apathetichell]

Here's a fairly different way from @Luke_C  - it uses the running total tool. and a macro.

  Since it uses actual dates (and does so as the control) missing dates won't matter.

[apathetichell]

And before you ask - let's say you want a numeric up/down to choose how many days you want your running total? can it be done? why sure...

[Trent_Montgomery]

Alright, I think I am almost there... One more questions question...

In my actual data set I am doing this for the last 91 days. Do I really have to add all 91 rows in the expression (multi row) or is their a simpler way to write that expression (ie. Row +0 through Row -90). Thanks again for all the help

[Luke_C]

@Trent_Montgomery , so instead of doing a rolling 5 days you want to do a running total for a rolling 91 days? In that case amending the filter in @apathetichell is probably the easier option.

Alternatively if you want a straight running total for the data the running total tool grouped by the store/sku would work too

[apathetichell]

Hi @Trent_Montgomery I used a slightly different approach and I avoid the quagmire of the extended multi-row formula. Take a look and see if it works for your needs. First version needs a hardcoded number of days to filter on (in the macro) - second uses a drop down interface in the batch macro to filter on a day # of your choosing (I think I limited it to 100 but you can change that) and even changes the column name to reflect your date selection.