Qlik Community

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.

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

Thanks in advance

Regards,

VR

4 Replies
MVP
MVP

Re: Script level Calculation

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;

Not applicable

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;

vinoth25593
New Contributor III

Re: Script level Calculation

Thank you swuehl

vinoth25593
New Contributor III

Re: Script level Calculation

Thanks for your time,

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