topic Re: Rolling values in Load script in QlikView

Rolling values in Load script

sathish_kumar — Sat, 16 Nov 2024 21:39:10 GMT

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

Re: Rolling values in Load script

sunny_talwar — Sun, 06 Jan 2019 15:01:51 GMT

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;

Re: Rolling values in Load script

sathish_kumar — Sun, 06 Jan 2019 15:13:22 GMT

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

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

Re: Rolling values in Load script

sunny_talwar — Sun, 06 Jan 2019 15:45:27 GMT

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

Re: Rolling values in Load script

sathish_kumar — Mon, 07 Jan 2019 01:06:15 GMT

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?

Re: Rolling values in Load script

sunny_talwar — Mon, 07 Jan 2019 01:48:49 GMT

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

Re: Rolling values in Load script

sathish_kumar — Mon, 07 Jan 2019 08:41:49 GMT

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

Re: Rolling values in Load script

uacg0009 — Mon, 07 Jan 2019 08:47:06 GMT

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.

Re: Rolling values in Load script

sunny_talwar — Mon, 07 Jan 2019 12:09:17 GMT

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?

Re: Rolling values in Load script

sathish_kumar — Mon, 07 Jan 2019 13:42:27 GMT

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.

Re: Rolling values in Load script

sathish_kumar — Mon, 07 Jan 2019 13:45:37 GMT

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

Re: Rolling values in Load script

sunny_talwar — Mon, 07 Jan 2019 14:15:33 GMT

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;

Re: Rolling values in Load script

sathish_kumar — Mon, 07 Jan 2019 14:29:03 GMT

Awesome Sunny. Thanks a lot