topic Re: How to add previous month data with current month in QlikView

How to add previous month data with current month

tahsin12 — Tue, 20 Oct 2020 09:23:53 GMT

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

Re: How to add previous month data with current month

Doralina — Tue, 20 Oct 2020 09:54:41 GMT

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

Re: How to add previous month data with current month

Taoufiq_Zarra — Tue, 20 Oct 2020 10:50:16 GMT

@tahsin12 how you can get 54 in this line ?

TYR

23033

2021

DUNLOP

Re: How to add previous month data with current month

tahsin12 — Tue, 20 Oct 2020 10:58:36 GMT

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

TYR

23033

2021

DUNLOP

TYR

23033

2021

DUNLOP

Re: How to add previous month data with current month

Taoufiq_Zarra — Tue, 20 Oct 2020 11:24:37 GMT

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

ategory	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

Re: How to add previous month data with current month

tahsin12 — Tue, 20 Oct 2020 12:54:03 GMT

The source data is :

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

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.

Re: How to add previous month data with current month

Taoufiq_Zarra — Tue, 20 Oct 2020 13:27:29 GMT

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

Re: How to add previous month data with current month

Kushal_Chawda — Tue, 20 Oct 2020 15:00:30 GMT

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

Re: How to add previous month data with current month

tahsin12 — Wed, 21 Oct 2020 04:43:45 GMT

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

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.

Re: How to add previous month data with current month

tahsin12 — Wed, 21 Oct 2020 04:48:19 GMT

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

Re: How to add previous month data with current month

Taoufiq_Zarra — Wed, 21 Oct 2020 09:30:29 GMT

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

Re: How to add previous month data with current month

tahsin12 — Wed, 21 Oct 2020 10:12:08 GMT

Thank you so much @Taoufiq_Zarra for the solution.😊

Re: How to add previous month data with current month

Kushal_Chawda — Wed, 21 Oct 2020 15:47:41 GMT

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

Re: How to add previous month data with current month

tahsin12 — Thu, 22 Oct 2020 03:51:55 GMT

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