topic Re: Calculate the Rolling 2 Months in the Script in QlikView

Calculate the Rolling 2 Months in the Script

Mon, 04 May 2015 12:40:16 GMT

The source table is as follows:

I want to add a new field which will calculate the sales of last two months based on the dimensins.

So the new table will look like below.

Here I want the calculation to be done in the Qlikview Script only.

Re: Calculate the Rolling 2 Months in the Script

sunny_talwar — Mon, 04 May 2015 13:21:16 GMT

Use the following script:

Table:

LOAD *,

DIM1&DIM2 as Combo;

LOAD * INLINE [

DIM1, DIM2, Month, Sales

A, xx, 20140901, 10

A, yy, 20140901, 20

A, xx, 20141001, 30

A, yy, 20141001, 40

A, xx, 20141101, 50

A, yy, 20141101, 60

A, xx, 20141201, 70

A, yy, 20141201, 80

A, xx, 20150101, 90

A, yy, 20150101, 10

A, xx, 20150102, 20

A, yy, 20150102, 30

];

Table1:

LOAD Combo,

DIM1,

DIM2,

Month,

Sales,

RangeSum(Alt(Peek('Sales'), 0)) + RangeSum(Alt(Peek('Sales', -2), 0)) as [Last 2 Months]

Resident Table

Where Combo = 'Axx';

Table2:

NoConcatenate

LOAD Combo,

DIM1,

DIM2,

Month,

Sales,

RangeSum(Alt(Peek('Sales'), 0)) + RangeSum(Alt(Peek('Sales', -2), 0)) as [Last 2 Months]

Resident Table

Where Combo = 'Ayy';

DROP Table Table;

Table:

NoConcatenate

LOAD *

Resident Table1;

Concatenate(Table)

LOAD *

Resident Table2;

DROP Table Table1, Table2;

Re: Calculate the Rolling 2 Months in the Script

maxgro — Thu, 09 Jul 2015 17:31:32 GMT

LOAD * INLINE [

DIM1, DIM2, Month, Sales

A, xx, 20140901, 10

A, yy, 20140901, 20

A, xx, 20141001, 30

A, yy, 20141001, 40

A, xx, 20141101, 50

A, yy, 20141101, 60

A, xx, 20141201, 70

A, yy, 20141201, 80

A, xx, 20150101, 90

A, yy, 20150101, 10

A, xx, 20150102, 20

A, yy, 20150102, 30

];

load

if(DIM1=peek(DIM1) and DIM2=peek(DIM2), RangeSum(Alt(Peek('Sales'), 0)),0)

if(DIM1=peek(DIM1,-2) and DIM2=peek(DIM2,-2), RangeSum(Alt(Peek('Sales', -2), 0)),

0) as [Last 2 Months]

Resident x

order by DIM1, DIM2, Month;

DROP Table x;

Re: Calculate the Rolling 2 Months in the Script

sunny_talwar — Thu, 09 Jul 2015 17:34:52 GMT

Massimo Grossi‌ this is a old post from May 5th, not sure why old posts are coming up randomly.