Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
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;

View solution in original post

sathish_kumar
Contributor II
Contributor II
Author

Awesome Sunny. Thanks a lot