4 Replies Latest reply: Jun 25, 2015 11:37 AM by vinoth govindarajan

# Script level Calculation

Hi All,

I have the following data:

Week

DimTarget Sales

05-10-2015

A1000
05-10-2015B1500
12-10-2015A1500
12-10-2015B1200
19-10-2015A2500
19-10-2015B2000
26-10-2015A1700
26-10-2015B1100
02-11-2015A3500
02-11-2015B3100
09-11-2015A3000
09-11-2015B3900

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

• ###### Re: Script level Calculation

Maybe something like this:

Set DateFormat ='DD-MM-YYYY';

INPUT:

LOAD Week,

Dim,

[Target Sales]

FROM

(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;

• ###### Re: Script level Calculation

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;

• ###### Re: Script level Calculation

Thanks for your time,

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