Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tahsin12
Contributor II
Contributor II

How to add previous month data with current month

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.

CategoryCust_noYearFiscalMonthBrandSegmentVolume
TYR2303320211APOLLOPC25
TYR2303320211APOLLOTT2
TYR2303320211DUNLOPPC44
TYR2303320212APOLLOTB12
TYR2303320213DUNLOPPC10

 

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

CategoryCustNoYearFiscalMonthBrandSegVolumeIncQty
TYR2303320211APOLLOPC2525
TYR2303320211APOLLOTT22
TYR2303320211DUNLOPPC4444
TYR2303320212APOLLOPC2525
TYR2303320212APOLLOTT22
TYR2303320212DUNLOPPC4444
TYR2303320212APOLLOTB1212
TYR2303320213APOLLOPC2525
TYR2303320213APOLLOTT22
TYR2303320213DUNLOPPC4444
TYR2303320213APOLLOTB1212
TYR2303320213DUNLOPPC1054

 

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 

1 Solution

Accepted Solutions
Taoufiq_Zarra

@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:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

13 Replies
Doralina
Contributor
Contributor

Hello, I have a query used to retrieve Monthly data which is scheduled to run every 1st of the month for the previous month.

I’m passing the date format as follows:

where
year_id = to_char(sysdate,’yyyy’)
and month_id =to_char(sysdate,’mm’) – 1)

This works great, until I need to retrieve December data in January, because of the year change
Can anyone help me with this?
Thanks in advance

Taoufiq_Zarra

@tahsin12  how you can get 54 in this line ?

TYR2303320213DUNLOPPC1054
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
tahsin12
Contributor II
Contributor II
Author

As previously month added up with current month data.So in current data  incentive will be 44+10=54.

TYR2303320212DUNLOPPC4444
TYR2303320213DUNLOPPC1054
Taoufiq_Zarra

@tahsin12  and why for other row Volume and IncQty is the same ?

ategoryCustNoYearFiscalMonthBrandSegVolumeIncQty
TYR2303320211APOLLOPC2525
TYR2303320211APOLLOTT22
TYR2303320211DUNLOPPC4444
TYR2303320212APOLLOPC2525
TYR2303320212APOLLOTT22
TYR2303320212DUNLOPPC4444
TYR2303320212APOLLOTB1212
TYR2303320213APOLLOPC2525
TYR2303320213APOLLOTT22
TYR2303320213DUNLOPPC4444
TYR2303320213APOLLOTB1212
TYR2303320213DUNLOPPC1054
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
tahsin12
Contributor II
Contributor II
Author

The source data is :

CategoryCust_noYearFiscalMonthBrandSegmentVolume
TYR2303320211APOLLOPC25
TYR2303320211APOLLOTT2
TYR2303320211DUNLOPPC44
TYR2303320212APOLLOTB12
TYR2303320213DUNLOPPC10

 

we sum up incentive segment and  Brand wise. If Previous month segment and brand is same with current month then it will sum up. In this 1st month Segment and brand is same in 3rd month so its adding in the incqty.

Taoufiq_Zarra

@tahsin12  One Option:

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,SortTmp;


output:
noconcatenate

load Category, Cust_no, Year, FiscalMonth, Brand, Segment, Volume,if(len(IncQty)=0,Volume,IncQty) as IncQty resident Final;

drop table Final

 

output:

 

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Kushal_Chawda

@tahsin12  try 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:
NoConcatenate
LOAD *,
     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;

 

 

Screenshot 2020-10-20 155813.png

tahsin12
Contributor II
Contributor II
Author

Hey @Taoufiq_Zarra  thank you for the solution. There is a problem I've faced in your solution. Please  take a look on this.

Inc.PNG

when we adding up previous month data with current data it will only show the current months sumed up data. In this example Dunlop PC IncQty 54 will be shown. The last  row where incQty is 44 will not shown in this table. How can I just do this? Please let me know. Thanks in advanced.

tahsin12
Contributor II
Contributor II
Author

Hey @Kushal_Chawda Thank you for your solution. But I've face the same problem in your solution also. Please take a look at this

Inc.PNG

when we adding up previous month data with current data it will only show the current months sumed up data. In this example Dunlop PC IncQty 54 will be shown. The last  row where incQty is 44 will not shown in this table. How can I just do this? Please let me know. Thanks in advanced.