Calculate the Rolling 2 Months in the Script

[#1]

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.

[sunny_talwar]

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;

[maxgro]

x:

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

];

y:

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;

[sunny_talwar]

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