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)
1 Solution

Accepted Solutions
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

12 Replies
sunny_talwar

Try this

Sales:
LOAD Product, 
     Period, 
     Amount, 
     Output
FROM
Cumalative.xlsx
(ooxml, embedded labels);

Sales_1:
LOAD Product,
	 Period,
	 Amount,
	 RangeSum(If(Product = Previous(Previous(Product)), Previous(Previous(Amount))),
If(Product = Previous(Product), Previous(Amount)),
Amount) as Rolling Resident Sales Order by Product, Period desc; DROP Table Sales;
sathish_kumar
Contributor II
Contributor II
Author

Thanks Sunny. 
Is there any other alternate way of doing this?

It will be bit tedious to calculate rolling for 5 months or 7months

sunny_talwar

In that case, I would say you can use The As Of Table... this can give you a partial script solution... or you can left join the As Of Table into your main table and then use Group by to do what you want. Regardless of what you do... it might all be a little tedious.... I guess you are in the wrong profession if you are afraid of writing a little tedious script ;).

sathish_kumar
Contributor II
Contributor II
Author

Hello Sunny,

I wanted to make it dynamic. For instance, if user wants 3months rolling then i will calculate for 3 months, if user wants 5months then i will use for 5months.

in the front end ,i can use this formula. So here 3 can be made as variable and by changing this variable,i can calculate accordingly. 

RangeSum (Above(FieldName,0,3))

But how do we achieve the same in load script? 

sunny_talwar

Before I can answer, I would like to know how are you planning to pass the user input to the script?

sathish_kumar
Contributor II
Contributor II
Author

Hello Sunny,

In the front end, i am able to achieve by defining "vRolling" as variable and use the same in the chart expression.

Need to achieve the same in load script.

RangeSum (Above(Amount,0,vRolling))

1.JPG

uacg0009
Partner - Specialist
Partner - Specialist

Hi Kumar,

Firstly I want to say that it's better to solve it in front-end like what you do, create a variable and input box.

Because even achieve this in back-end, the variable will be useless, because even user change it, you have calculate it on back-end, the value won't be change. So I don't know why you want to do it in back-end.

But anyway, it really takes me a lot of hours to try to solve that. I created a variable in the back-end, if you want to change to 5/7 month, you need to change the variable, but user can not do that I think.

Aiolos Zhao

Or what you want is even user change the variable in front-end, the value will be change? I think it's not possible.

Rolling values in Load script.PNG

sunny_talwar

So, once the variable is changed, your user will trigger a reload? Is this app sitting on QMC or is this for a user locally? I am just trying to understand the reason behind making this difficult by doing it in the script and asking the user to reload if this can be done with RangeSum(Above()) or using The As Of Table. Have you read about The As Of Table yet? May be if you have not, this might be the best solution for you?

sathish_kumar
Contributor II
Contributor II
Author

Hello Sunny,

It will be done locally.Yes i did read about 'as of' table. It will solve my purpose partly. 

How about the one below. I load two tables Table 1 and Table 2. 

Table 2 will have column called ROLLING, which will calculate based on the values defined in Table 1?

Is this possible?

 

Table 1

ProductRolling
A2
B3

 

Table 2:

DateProductAmountRolling
01-01-2019A515
01-02-2019A1025
01-03-2019A1535
01-04-2019A2045
01-05-2019A2555
01-06-2019A3030
01-01-2019B16
01-02-2019B29
01-03-2019B312
01-04-2019B415
01-05-2019B511
01-06-2019B66

 

I am not doing this in the front end because this is not the end state. Based on the above table , in the back end, I will be using calculated field for other calculations and then storing the table in csv using Store command.