Discussion Board for collaboration on QlikView Scripting.
I have the following data:
and so on.
I have weekly target sales data.
For each week, I have to calculate the average of next three weeks target sales in script level.
for Dim A and week 05-10-2015, I have to take average of next three months sales value for Dim A i.e
average of these 3 values: 1500,2500,1700.
Likewise I have to calculate for all the weeks.
Is there any way to do this?
Thanks in advance
Maybe something like this:
Set DateFormat ='DD-MM-YYYY';
(html, codepage is 1252, embedded labels, table is @1);
NOCONCATENATE LOAD *,
AutoNumber(Week, Dim) as WeekCounter
ORDER BY Dim, Week desc;
if(WeekCounter >3, rangesum(peek('Target Sales',-3),peek('Target Sales',-2),peek('Target Sales',-1))/3 )as AvgForecastTarget
Drop Table INPUT, TMP;
Try like Below: In this Apprach the Top 2 weeks avg of 2 and 1 weeks.
Week, Dim , Target_Sales
From TargteFile ;
LOAD Distinct Week as TempWeek Resident InPut;
Left Join (InPut)
TempWeek AS RollingWeek ,
Date(TempWeek + iterNo()*7) AS Week
Resident Temp While IterNo() <=3;
RollingWeek AS Week,
AVG(Target_Sales) AS 3WkAvgSales
Group by RollingWeek, Dim
Drop table Input;
Thank you swuehl
Thanks for your time,
but thhis will calculate rolling 3 weeks average not future 3 weeks average.