RangeSum for Cumulative Rolling Totals

[mtkst13]

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?

[LorantPataki]

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:

[LorantPataki · Accepted solution]

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

[mtkst13]

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

[LorantPataki]

Glad to hear it helped. Cheers!