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. StoreSKUDateSales5 Day Running Sum111111/1/202155211111/1/202155111111/2/2021611211111/2/2021611111111/3/2021718211111/3/2021819111111/4/2021321211111/4/2021322111111/5/2021425211111/5/2021527111111/6/2021121211111/6/2021123111111/7/2021520211111/7/2021522111111/8/2021215211111/8/2021216111111/9/2021416211111/9/2021417111111/10/2021618211111/10/2021618111111/11/2021825211111/11/2021825111111/12/2021929211111/12/2021525111111/13/2021229211111/13/2021225111111/14/2021631211111/14/2021627

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.

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

Developer

Common Use Cases

Best Practices

Accepted answers

All comments

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

New Workflow5.yxmd

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:

Convert dates to actual valid alteryx dates
Identify the max/min date for each store/SKU combo
Generate a row for every date in that range
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
Sort the data and run the multi-row formula tool

New Workflow5.yxmd

apathetichell

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

2021-06-23 (2).png

2021-06-23 (3).png

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

store sku.yxzp

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

store sku.yxzp

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703