3 Replies Latest reply: Oct 17, 2015 7:18 AM by Sebastian Müller

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

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:

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)

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

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

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?

more Question:

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

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

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.