Load Statement - Sum in the current month from al previous month records

[#1]

Hi all,

I am new to qlikview.

I have below issues.

I have below data structure in Excel.

Product     Month       Flag

ABC          Jan-2014    1

ABC          Feb-2014    1

ABC          Mar-2014     0

when i load data in qlikview,

i want to add calculated column SUM as below.

Product     Month        Flag     SUM

ABC          Jan-2014      1          0            <---excluding current month, sum from previous months records for this Product

ABC          Feb-2014     1           1            <--excluding curretn month, sum from previous months records for this Product

ABC          Mar-2014     0           2            <--excluding curretn month, sum from previous months records for this Product

any help would appreciated ?

Thanks!.

[maxgro]

if your records are already sorted (by product and year-month) you can use the peek function (see online help); I added Product A just for test

tmp:

load * inline [

Product   ,  Month      , Flag

ABC       ,   Jan-2014   , 1

ABC       ,   Feb-2014   , 1

ABC       ,   Mar-2014   ,  0

A       ,   Jan-2014   , 1

A       ,   Feb-2014   , 1

A       ,   Mar-2014   ,  0

A       ,   Apr-2014   ,  1

A       ,   May-2014   ,  0

A       ,   Jun-2014   ,  1

];

tmp2:

LOAD

  Product,

  Month,

  Flag,

  if(Peek(Product)=Product, Peek(AllCum)+Flag, Flag) as AllCum          // cumulate by product

Resident tmp;

DROP Table tmp;

table:

Load

  *,

  AllCum - Flag as Result               // cumulate by product - current month

Resident tmp2;

DROP Table tmp2;