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

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

    Sebastian Müller

      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