Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
more Question:
Is ID is a unique key on the source data set ?
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.