topic Re: Script level Calculation in QlikView

Script level Calculation

Vinothishere — Wed, 24 Jun 2015 16:35:28 GMT

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,

Re: Script level Calculation

swuehl — Wed, 24 Jun 2015 16:56:40 GMT

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;

Re: Script level Calculation

Wed, 24 Jun 2015 19:19:13 GMT

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

Vinothishere — Thu, 25 Jun 2015 15:36:20 GMT

Thank you swuehl

Re: Script level Calculation

Vinothishere — Thu, 25 Jun 2015 15:37:31 GMT

Thanks for your time,

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