Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!.
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;