Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sathish_kumar
Contributor II
Contributor II

Rolling values in Load script

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

ProductPeriodAmountOutput
A01-01-20191033
A01-02-20191136
A01-03-20191239
A01-04-20191342
A01-05-20191445
A01-06-20191548
A01-07-20191651
A01-08-20191754
A01-09-20191857
A01-10-20191960
A01-11-20192041
A01-12-20192121
B01-01-20192269
B01-02-20192372
B01-03-20192475
B01-04-20192578
B01-05-20192681
B01-06-20192784
B01-07-20192887
B01-08-20192990
B01-09-20193093
B01-10-20193196
B01-11-20193265
B01-12-20193333
Labels (3)
12 Replies
sathish_kumar
Contributor II
Contributor II
Author

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

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
Contributor II
Contributor II
Author

Awesome Sunny. Thanks a lot