# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for
Did you mean:
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

 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
Labels (3)

• ### rolling

1 Solution

Accepted Solutions
MVP

Try this:

```Table2:
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)
Product, Rolling
A, 2
B, 3
];

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

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

DROP Table Table2;```
12 Replies
MVP

Try this

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

Sales_1:
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;```
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

MVP

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 ;).

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?

MVP

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

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))`

Partner

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.

MVP

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?

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

 Product Rolling A 2 B 3

Table 2:

 Date Product Amount Rolling 01-01-2019 A 5 15 01-02-2019 A 10 25 01-03-2019 A 15 35 01-04-2019 A 20 45 01-05-2019 A 25 55 01-06-2019 A 30 30 01-01-2019 B 1 6 01-02-2019 B 2 9 01-03-2019 B 3 12 01-04-2019 B 4 15 01-05-2019 B 5 11 01-06-2019 B 6 6

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.

Community Browser