topic Re: RangeSum for Cumulative Rolling Totals in App Development

RangeSum for Cumulative Rolling Totals

mtkst13 — Tue, 19 Mar 2024 23:03:27 GMT

Hello. I have data similar to the below. In my load script, I want to have a three month rolling cumulative by Customer and Dept (or six month or 12 month or etc). So for month end 2/29 for Bob in Dept Food, sum Dollars Spent per Month for 2/29, 1/31, 12/31. For month end 1/31 for Bob in Dept Food, sum Dollars Spent per Month for 1/31, 12/31, 11/30. and etc and etc. I have figured out how to do the Cumulative Spent to Date by Customer and Dept, but I can't seem to get the rolling cumulative to work.

The spreadsheet has what I want the values to be for Dollars Spent 3 Month Rolling Cumulative. How would I do this via the load script? Is it a combination of a group by, order by? RangeSum?

Re: RangeSum for Cumulative Rolling Totals

LorantPataki — Fri, 22 Mar 2024 08:22:41 GMT

Hello, this would be one way to do it:

<<<

Source:

LOAD

"Month End",

"Rolling 3 Min",

Customer,

Dept,

"Dollars Spent per Month"

FROM [lib://DataFiles/CumulativeRollingTotalExample.xlsx]

(ooxml, embedded labels, table is Sheet2);

CumulativeTable:

NoConcatenate Load

Customer,

Dept,

"Month End",

"Dollars Spent per Month",

"Dollars Spent per Month"

+ If(Peek(Customer, -1) = Customer and Peek(Dept, -1) = Dept, Peek("Dollars Spent per Month", -1), 0)

+ If(Peek(Customer, -2) = Customer and Peek(Dept, -2) = Dept, Peek("Dollars Spent per Month", -2), 0) as CumulativeLast3Months

Resident Source

Order By Customer, Dept, [Month End];

Drop Table Source;

>>>

and this is the result:

Re: RangeSum for Cumulative Rolling Totals

LorantPataki — Fri, 22 Mar 2024 08:37:27 GMT

PS. A more flexible option, just replace "3" with the number of months to accumulate:

Re: RangeSum for Cumulative Rolling Totals

mtkst13 — Mon, 25 Mar 2024 14:54:58 GMT

@LorantPataki , thank you! This seems to work. Tweaking a little for my real data. Thank you so much! This is exactly what I was looking for...

Re: RangeSum for Cumulative Rolling Totals

LorantPataki — Mon, 25 Mar 2024 15:54:36 GMT

Glad to hear it helped. Cheers!