How to add previous month data with current month

[tahsin12]

Hey there,

I'm new to qlikview.  I need help regarding a requirements. I'll give the data I have working on and then I'll explain what are the requirements are.

Category	Cust_no	Year	FiscalMonth	Brand	Segment	Volume
TYR	23033	2021	1	APOLLO	PC	25
TYR	23033	2021	1	APOLLO	TT	2
TYR	23033	2021	1	DUNLOP	PC	44
TYR	23033	2021	2	APOLLO	TB	12
TYR	23033	2021	3	DUNLOP	PC	10

I have to calculate the incentive. The table will be like this

Category	CustNo	Year	FiscalMonth	Brand	Seg	Volume	IncQty
TYR	23033	2021	1	APOLLO	PC	25	25
TYR	23033	2021	1	APOLLO	TT	2	2
TYR	23033	2021	1	DUNLOP	PC	44	44
TYR	23033	2021	2	APOLLO	PC	25	25
TYR	23033	2021	2	APOLLO	TT	2	2
TYR	23033	2021	2	DUNLOP	PC	44	44
TYR	23033	2021	2	APOLLO	TB	12	12
TYR	23033	2021	3	APOLLO	PC	25	25
TYR	23033	2021	3	APOLLO	TT	2	2
TYR	23033	2021	3	DUNLOP	PC	44	44
TYR	23033	2021	3	APOLLO	TB	12	12
TYR	23033	2021	3	DUNLOP	PC	10	54

The previous month volume will be added to the current month incentive calculation in qlikview script. Total calculation will be in qlikview script. I tried but couldn't find any solution. Please help me to solve this problem. Thanks.

Regards

tahsin12

@Kush @Henric_Cronström @johnw 

[Taoufiq_Zarra · Accepted solution]

@tahsin12  try below

Data:

LOAD *,Brand&Segment as SortTmp INLINE [
    Category, Cust_no, Year, FiscalMonth, Brand, Segment, Volume
    TYR, 23033, 2021, 1, APOLLO, PC, 25
    TYR, 23033, 2021, 1, APOLLO, TT, 2
    TYR, 23033, 2021, 1, DUNLOP, PC, 44
    TYR, 23033, 2021, 2, APOLLO, TB, 12
    TYR, 23033, 2021, 3, DUNLOP, PC, 10
];


Input:
noconcatenate

load *,if(peek(SortTmp)=SortTmp and peek(Year)=Year,peek(IncQty)+Volume,Volume) as IncQty;
load * resident Data order by SortTmp,Year,FiscalMonth;

drop table Data;


Final:
load *,1 as Tmpfield resident Input;

Tmp:
load distinct FiscalMonth&'_'&Year as FIELD resident Input;

FOR Each a in FieldValueList('FIELD')

join(Final)

load Brand&Segment as SortTmp,Category, Cust_no, Year, subfield('$(a)','_',1) as FiscalMonth, Brand, Segment, Volume resident Input where FiscalMonth<=subfield('$(a)','_',1) and Year=subfield('$(a)','_',2);

NEXT a 

drop table Tmp,Input;
drop fields Tmpfield;


output:
noconcatenate

load  Category,SortTmp, Cust_no, Year, FiscalMonth, Brand, Segment, Volume,if(len(IncQty)=0,Volume,IncQty) as IncQty where Flag=0;
load *,if(peek(SortTmp)=SortTmp and peek(FiscalMonth)=FiscalMonth and len(IncQty)=0,1,0) as Flag;
load Category,SortTmp, Cust_no, Year, FiscalMonth, Brand, Segment, Volume,IncQty resident Final order by Year,FiscalMonth,SortTmp,IncQty DESC;

drop table Final

output:

[tahsin12]

Thank you so much @Taoufiq_Zarra  for the solution.😊

[Kushal_Chawda]

@tahsin12  although you got the solution, I would recommend  not to use for loops as it will affect the performance of the app. So I would suggest to use below

Data:
LOAD Category, 
     Cust_no, 
     Year, 
     FiscalMonth as FiscalMonth1, 
     Brand, 
     Segment, 
     Volume
FROM Source

Join(Data)
LOAD num(FieldValue('FiscalMonth1',RecNo())) as FiscalMonth
AutoGenerate FieldValueCount('FiscalMonth1');

T1:
NoConcatenate
LOAD *
Resident Data
where (FiscalMonth1=1 and FiscalMonth=1) or (FiscalMonth1<=FiscalMonth);

DROP Table Data;DROP Field FiscalMonth1;

Final:
LOAD *
where Flag=1;
LOAD *,
     if(rowno()=1 or FiscalMonth<>Previous(FiscalMonth),1,0) as Flag,
     if(RowNo()=1 or Segment<>Previous(Segment),Volume, 
     if(FiscalMonth<>Previous(FiscalMonth) and Volume<>Previous(Volume),rangesum(Peek(Volume),Volume),Volume)) as IncQty
Resident T1
Order by Year,Category, Cust_no,Brand,Segment,FiscalMonth,Volume;

DROP Table T1;

[tahsin12]

Thank you @Kushal_Chawda  I will  keep this in my mind definitely. Thanks for all the help 😊