# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

#### Breathe easy -- you now have more time to plan your next steps with Qlik! QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

New Contributor III

## 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?

Regards,

VR

Tags (2)
4 Replies
MVP

## Re: Script level Calculation

Maybe something like this:

Set DateFormat ='DD-MM-YYYY';

INPUT:

Dim,

[Target Sales]

FROM

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

TMP:

AutoNumber(Week, Dim) as WeekCounter

RESIDENT INPUT

ORDER BY Dim, Week desc;

RESULT:

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;

Not applicable

## Re: Script level Calculation

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

InPut:

Week, Dim , Target_Sales

From TargteFile ;

Temp:

LOAD Distinct Week as TempWeek Resident InPut;

Left Join (InPut)

TempWeek AS RollingWeek ,

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

Resident Temp While IterNo() <=3;

Final:

RollingWeek AS Week,

Dim,

AVG(Target_Sales) AS 3WkAvgSales

Resident InPut

Group by RollingWeek, Dim

;

Drop table Input;

New Contributor III

## Re: Script level Calculation

Thank you swuehl

New Contributor III