Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
gayatri7
Creator II
Creator II

How to extract last month's value for every Quarter

Hi All,

I have to convert the below chart based on different quarters. Which is easy and I did it (please ref 2nd chart). As expected, I am getting value for Q1 as Jan+Feb+Mar ; Q2 as Apr+May+Jun etc.

But Goal here is to get the last month's value for every quarter. Eg. if it is Q1 , I should get value as 10,216 (March)and not 30,441 (Jan+Feb+Mar) similarly for Q2 -10,546 (Jun) and not 31,444 (Apr+May+June)

 

Monthly

Monthly.PNG

 

Quarterly

Quarterly.PNG

I tried something with Set expression but I am getting only one Quarter at a time (which is not as expected). I should get all quarters.

Quarterly_1.PNG

 

 

Could anyone let me know how to extract the last month's value of every quarter.

Thanks

1 Solution

Accepted Solutions
Mauritz_SA
Partner - Specialist
Partner - Specialist

Hi @gayatri7 

Maybe you can adapt this to work for you. I assumed that you don't use autoCalendar, otherwise you can obviously just use the Quarter field for your dimension .

LOAD RecNo() AS InvId, Date#(InvDate,'DD/MM/YYYY') AS InvDate, Sales Inline [
InvDate, Sales
28/03/2012, 20
10/12/2012, 25
5/2/2013, 32
31/3/2013, 15
19/5/2013, 19
15/9/2013, 20
11/12/2013, 25
2/3/2014, 10
14/5/2014, 5
13/6/2014, 2
7/7/2014, 10
4/8/2014, 13
];

Dimension:

Dual('Q'&Num(Ceil(Num(Month(InvDate))/3)),Num(Ceil(NUM(Month(InvDate))/3),00))

Measure:

Sum({<InvId = {'=Month(InvDate)=Month(QuarterEnd(InvDate))'}>}Sales)

OR

Sum(If(Month(InvDate)=Month(QuarterEnd(InvDate)),Sales,0))

Hope this helps.

Regards,

Mauritz

View solution in original post

1 Reply
Mauritz_SA
Partner - Specialist
Partner - Specialist

Hi @gayatri7 

Maybe you can adapt this to work for you. I assumed that you don't use autoCalendar, otherwise you can obviously just use the Quarter field for your dimension .

LOAD RecNo() AS InvId, Date#(InvDate,'DD/MM/YYYY') AS InvDate, Sales Inline [
InvDate, Sales
28/03/2012, 20
10/12/2012, 25
5/2/2013, 32
31/3/2013, 15
19/5/2013, 19
15/9/2013, 20
11/12/2013, 25
2/3/2014, 10
14/5/2014, 5
13/6/2014, 2
7/7/2014, 10
4/8/2014, 13
];

Dimension:

Dual('Q'&Num(Ceil(Num(Month(InvDate))/3)),Num(Ceil(NUM(Month(InvDate))/3),00))

Measure:

Sum({<InvId = {'=Month(InvDate)=Month(QuarterEnd(InvDate))'}>}Sales)

OR

Sum(If(Month(InvDate)=Month(QuarterEnd(InvDate)),Sales,0))

Hope this helps.

Regards,

Mauritz