topic Re: How to loop through a dataset and use previously calculated values? in QlikView

How to loop through a dataset and use previously calculated values?

Milo2009 — Fri, 16 Oct 2015 18:08:42 GMT

Hey there,

although I'm getting familar with QV and scripting I now have a question regarding Loops and Calculations in the script.

I want to perform a XYZ-Analysys. Therefore I have to compute some values....

Until now I do this steps in my script:

SOURCE:

LOAD ARTNR,

WEEK,

YEAR,

WEEK &'_'& YEAR AS WEEK_YEAR,

DAN &'_'& WEEK &'_'& YEAR AS ID,

FORECAST,

SALES

FROM

[..\SOURCEDATA\XYZANALSYSE\SALES.xlsx]

(ooxml, embedded labels, table is SalesJan2014)

WHERE YEAR = 2014;

LET $(SQ) = 0;

LET $(SF) = 2

For each ID in SOURCE

LEFT JOIN (SOURCE)

LOAD

ID,

0.5*($(SQ)+($(SF)*FABS(1-(SALES/FORECAST)))) AS COEFFICIENT

RESIDENT SOURCE;

So what I want to calculate is the following:

SQt = 0.5*($(SQt-1)+($(SF)*FABS(1-(SALES/FORECAST))))

In the first period (calendarweek 1) SQt-1 vanishes the value 0 as set above the loop. As a result of the formula I get a value for SQ1 for the first calendarweek. Now I want to calculate SQ2 for the calendarweek 2. Now my SQt-1 should vanish the result of the formula from the previous period (calendarweek 1). This I want to do for every week in a year. I always use the result from the previous period in the calculation for the actual period.

But I do not know, how to do this in a Script:/

Thanks in advance for any ideas

Kind regards,

Sebastian

Re: How to loop through a dataset and use previously calculated values?

Fri, 16 Oct 2015 20:21:42 GMT

It is a necessary to pre calculate on the script level ? If not please do these calculation on UI.

Also, Please share some sample data.

Re: How to loop through a dataset and use previously calculated values?

Fri, 16 Oct 2015 20:25:42 GMT

Re: How to loop through a dataset and use previously calculated values?

Milo2009 — Sat, 17 Oct 2015 11:18:06 GMT

Hey,

thanks for your reply. Its not necessary to do the calculation on the

script level. Well, for no reason I tend to do a lot of things in the script...

I create the ID from the articlenumber and the calendarweek, so that it is

a unique one.