Script level Calculation

[Vinothishere]

Hi All,

      I have the following data:

Week	Dim	Target Sales
05-10-2015	A	1000
05-10-2015	B	1500
12-10-2015	A	1500
12-10-2015	B	1200
19-10-2015	A	2500
19-10-2015	B	2000
26-10-2015	A	1700
26-10-2015	B	1100
02-11-2015	A	3500
02-11-2015	B	3100
09-11-2015	A	3000
09-11-2015	B	3900

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.

Example:

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

Regards,

VR

[swuehl]

Maybe something like this:

Set DateFormat ='DD-MM-YYYY';

INPUT:

LOAD Week,

     Dim,

     [Target Sales]

FROM

[https://community.qlik.com/thread/169851]

(html, codepage is 1252, embedded labels, table is @1);

TMP:

NOCONCATENATE LOAD *,

   AutoNumber(Week, Dim) as WeekCounter

RESIDENT INPUT

ORDER BY Dim, Week desc;

RESULT:

LOAD *,

  if(WeekCounter >3,  rangesum(peek('Target Sales',-3),peek('Target Sales',-2),peek('Target Sales',-1))/3 )as AvgForecastTarget

Resident TMP;

Drop Table INPUT, TMP;

[#3]

Try like Below: In this Apprach the Top 2 weeks avg of 2 and 1 weeks.

InPut:

LOAD

     Week, Dim , Target_Sales

From TargteFile ;

Temp:

LOAD Distinct Week as TempWeek Resident InPut;

Left Join (InPut)

Load

     TempWeek AS RollingWeek ,

     Date(TempWeek + iterNo()*7) AS Week

Resident Temp While IterNo() <=3;

Final:

Load

     RollingWeek AS Week,

     Dim,

     AVG(Target_Sales) AS 3WkAvgSales

Resident InPut

Group by RollingWeek, Dim

;

Drop table Input;

[Vinothishere]

Thank you swuehl

[Vinothishere]

Thanks for your time,

but thhis will calculate rolling 3 weeks average not future 3 weeks average.