Rolling sum of future forecast quantity over n number of rows

[ritikat]

Hello Team,

I have a situation where I need to do a sum of forecast quantity over "n" number of rows. 

n = coverage days

The N will vary based on article and store combination. 

Requirement column is the output column where the sum has to be stored.

Looking forward for your responses. 

Article	Store	N (Coverage days)	Date	Forecast qty	Requirement
A1	S1	2	2020-09-02	10	10+20=30
A1	S1	2	2020-09-03	20	20+5 = 25
A1	S1	2	2020-09-04	5	15+5 = 20
A1	S1	2	2020-09-05	15	15
A2	S1	4	2020-09-02	3	3+2+1 + 5=11
A2	S1	4	2020-09-03	2	2+1+5+6=14
A2	S1	4	2020-09-04	1	1+5+6=12
A2	S1	4	2020-09-05	5
A2	S1	4	2020-09-06	6

[ggruccio]

Hi @ritikat,

Based on the data you provided, you could build out a simple if-then statement within a multi-row formula tool.  However if it gets too complex then you may want to choose another route...this would depend on the number of "N Coverage Days"  you have. 

Not sure if I have the logic exactly correct , as shown below, in item A2, I believe your formula pulls values from A3, where I've grouped on the field [Article] and won't allow it to pull from A3.  

You may also want to "Group-by" [Store] and [Article] and check my formula...where I've only added 2 rows below the current row for the N-Coverage days =4 to match your requirement you may want to go 3 rows below (the current row + 3 more =4).

Hopefully this is enough to get you started!

[ritikat]

Hi @ggrucio,

Thanks so much for the reply. 

1 issue that I am facing is - The expression needs to be dynamic based on the coverage days value. This is going to be a huge data set and every article store combination will have a different coverage days, so we can't  possibly right all IF statements and expression to sum the Row+N rows. 

Hoping to find a way where the expression changes dynamically. 

Regards,

Ritika

[RolandSchubert · Accepted solution]

Hi @ritikat ,

maybe this approach solves your problem:

- generate a list of "days to add" using the Generate Rows  tool (number of rows to generate is determined by the number of days to include)

- join this list to the input data

- summarize

I've separated the number of days from the forecast data, the "number of days" table needs your input for each combination Article/Store.

I've attached a sample workflow. Let me know if it works for you.

Best,

Roland

[ritikat · Accepted solution]

Thank you So much @RolandSchubert

It's a perfect solution! 🙂

[MarqueeCrew]

@ritikat ,

I was reviewing this post and saw a fixed-solution that works by @ggruccio and wanted you to be aware of a CReW macro (http://www.chaosreignswithin.com/p/macros.html) that is a "Moving Summarize" tool.  It allows you to generate not only the sum, but also moving averages and virtually all summarize numeric functions.

Macros Download Link 

Cheers,

Mark

[akshaydalvicia]

The Moving summarize tool only go back 100 rows. It is not looking back > 100 rows

[Mahadeva]

@MarqueeCrew It doesn't have group by option to perform moving calculations. Any way we can achieve this along with the features available in this macro. ?

[Mahadeva]

@RolandSchubert Thanks for the awesome solution.