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

[Milo2009]

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;

NEXT

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

[#2]

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.

[#3]

more Question:

Is ID is a unique key on the source data set ?

[Milo2009]

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.