topic Re: Pivot table Current and Previous month data for a current month in App Development

Pivot table Current and Previous month data for a current month

Pooja2 — Fri, 15 Nov 2024 21:37:36 GMT

Hi,

I am trying to create a report in qlik in below format.

where for current Month year i have to show 2 measure 1 with current month value and another with previous year same month.

But i am unable to show previous month value as a measure.

is it possible to achieve this.

kindly Help.

Re: Pivot table Current and Previous month data for a current month

udit_k — Tue, 04 Jul 2023 09:15:54 GMT

use below dummy data and expression calculated to get Current Month and Previous year Month:-

Load *, Year ([Start Date]) as Year,
Month([Start Date]) as Month,
Monthname([Start Date]) as Month1;
Load

* INLINE [

ID, Start Date,value
1,01/01/2023,1
1,01/01/2022,10
2,10/01/2023,20
2,10/01/2022,30
3,10/01/2023,40
3,10/01/2022,5
4,03/04/2023,10
4,03/04/2022,10
];

exit Script;

Current month:-

sum({<Month1={'$(=monthname(Max([Start Date])))'}>}value)

Previous Year Month:-

sum({<Month1={'$(=monthname(addmonths(Max([Start Date]),-12)))'}>}value)

Output:-

Re: Pivot table Current and Previous month data for a current month

Pooja2 — Tue, 04 Jul 2023 10:07:15 GMT

this will work when you don't have MOnth_Year dimension in columns of pivot table.

I have 1 dimension in column which is year.

under current year dimension i have to show 2 measures

Re: Pivot table Current and Previous month data for a current month

udit_k — Tue, 04 Jul 2023 10:50:59 GMT

you could nullify month in expression :-

LM:-sum({<Year={'$(=year(addyears(yearstart(max([Start Date])),-1)))'},Month={'>=$(=month(yearstart(max([Start Date]))))<=$(=month(Max([Start Date])))'},Month=>}value)

CM:-

sum({<Year={'$(=year(addyears(yearstart(max([Start Date])),0)))'},Month={'>=$(=month(yearstart(max([Start Date]))))<=$(=month(Max([Start Date])))'},Month=>}value)

Re: Pivot table Current and Previous month data for a current month

Pooja2 — Wed, 05 Jul 2023 07:03:34 GMT

Thanks a lot for the help.

It's working now.