Rolling values in Load script

[sathish_kumar]

Hello Users,

I am trying to calculate the rolling values for n period.

I am using below code to calculate rolling but is there any way to do only rolling 3 or 4 or 5 months?

if(Product=previous(Product), rangesum(Amount, peek(Rolling)),Amount) as Rolling

Attached excel sheet is the expected output

Product	Period	Amount	Output
A	01-01-2019	10	33
A	01-02-2019	11	36
A	01-03-2019	12	39
A	01-04-2019	13	42
A	01-05-2019	14	45
A	01-06-2019	15	48
A	01-07-2019	16	51
A	01-08-2019	17	54
A	01-09-2019	18	57
A	01-10-2019	19	60
A	01-11-2019	20	41
A	01-12-2019	21	21
B	01-01-2019	22	69
B	01-02-2019	23	72
B	01-03-2019	24	75
B	01-04-2019	25	78
B	01-05-2019	26	81
B	01-06-2019	27	84
B	01-07-2019	28	87
B	01-08-2019	29	90
B	01-09-2019	30	93
B	01-10-2019	31	96
B	01-11-2019	32	65
B	01-12-2019	33	33

[sathish_kumar]

Hey Thanks for your solution.
I am not doing this in the front end because this is not the end state. Once I solve the above issue in the back end, I will be using that particular table for other calculations and then storing the table in csv using Store command

[sunny_talwar · Accepted solution]

Try this:

Table2:
LOAD * INLINE [
    Date, Product, Amount
    01-01-2019, A, 5
    01-02-2019, A, 10
    01-03-2019, A, 15
    01-04-2019, A, 20
    01-05-2019, A, 25
    01-06-2019, A, 30
    01-01-2019, B, 1
    01-02-2019, B, 2
    01-03-2019, B, 3
    01-04-2019, B, 4
    01-05-2019, B, 5
    01-06-2019, B, 6
];

Left Join (Table2)
LOAD * INLINE [
    Product, Rolling
    A, 2
    B, 3
];

Left Join (Table2)
LOAD Date as AsOfDate,
	 Date(Date + IterNo() - 1) as Date,
	 Product,
	 IterNo() - 1 as Count
Resident Table2
While IterNo() <= Rolling;

FinalTable:
LOAD AsOfDate as Date,
	 Product,
	 Only(If(AsOfDate = Date, Amount)) as Amount,
	 Sum(Amount) as RollingAmount
Resident Table2
Group By AsOfDate, Product;

DROP Table Table2;

[sathish_kumar]

Awesome Sunny. Thanks a lot