Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Need Help with calculation of Current month, Last month of previous quarters

Hello Qlik Community,

I'm trying to calculate the value for current month of current quarter ( July-2017), last month of 2nd quarter(Jun-2017), last month value of 1st quarter (Mar-2017) and had no success. So please help me with this calculation.

I have attached the sample test data and expected result set data (excels). Please help me with this req. really appreciate your help. Thank you,

Ramya

Message was edited by: Ramya Sai I'm sorry the requirement has changed and i didn't explain my requirement clearly. Here is the exact requirement, this time i have attached sample excel data and qvw also. Please help me with this issue. I really appreciate your time and efforts. Thank you, My data model has month level data so, I calculated year from month, also calculated quarter using match function for ex: if month= Jan, Feb, Mar then Q1, Apr, May, June-Q2 etc. The requirement is I have a bar chart with all the quarters data displayed (2016-Q1, 2016-Q2, 2016-Q3, 2016-Q4, 2017-Q1, 2017-Q2). And here is the issue that I need help with, here (2016-Q1 means last month of 2016 Q1 i.e. March, 2016-Q2 means last month of Q2 i.e. June, Q3- Sep, Q4-Dec) and in current quarter it should be current month i.e. 2017-Q3 is August. As shown in attached. qvw the bars are showing year quarter values but that’s sum of all the moths in a quarter. The requirement is 2016 Q1 should display the value of March 2016 2016Q2 should be- June 2016 value 2016 Q3 should be -Sep 2016 Q4 should be -Dec and Current quarter 2017-Q3 should be August 2017 (i.e. current month). Thank you and I really appreciate your help.

1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

Re: Need Help with calculation of Current month, Last month of previous quarters

Hi

Try the attached.

View solution in original post

15 Replies
Highlighted
Creator III
Creator III

Re: Need Help with calculation of Current month, Last month of previous quarters

You can try it in script side by creating Quarter,MonthNo field.

And check it set analysis of expression like

sum({<Quarter={only(Quarter)-1},MonthNo={3*(only(Quarter)-1)}>} Value)

sum({<Quarter={only(Quarter)-2},MonthNo={3*(only(Quarter)-2)}>} Value)

Highlighted
Specialist II
Specialist II

Re: Need Help with calculation of Current month, Last month of previous quarters

Hi,

Please try the attached.

Thanks,

Varun

Highlighted
Creator III
Creator III

Re: Need Help with calculation of Current month, Last month of previous quarters

here is the single formula.

sum(TSI)+sum({<MonthName={$(=(Q-1)*3)},Date>}TSI)+sum({<MonthName={$(=(Q-2)*3)},Date>}TSI)

Highlighted
Creator II
Creator II

Re: Need Help with calculation of Current month, Last month of previous quarters

Thank you Ravi. Appreciate it.

Regards,

Ramya

Highlighted
Creator II
Creator II

Re: Need Help with calculation of Current month, Last month of previous quarters

Hi Ravi,

The requirement has been changed, i have updated and attached sample excels and qvw. if you get a chance please help me with this issue

Ramya

Highlighted
Creator II
Creator II

Re: Need Help with calculation of Current month, Last month of previous quarters

Hi Varun,

The requirement has been changed, i have updated and attached sample excels and qvw. if you get a chance please help me with this issue

Highlighted
Creator II
Creator II

Re: Need Help with calculation of Current month, Last month of previous quarters

Please can someone help me with this issue? i really appreciate your time and efforts. Thank you,

Highlighted
Specialist II
Specialist II

Re: Need Help with calculation of Current month, Last month of previous quarters

Try below expression

=sum({<Month={"$(=MaxString(Month))"}>}Data)

Thanks,

Varun

Highlighted
Creator II
Creator II

Re: Need Help with calculation of Current month, Last month of previous quarters

Varun,

i tried that it doesn't work, the problem is with the Current Quarter as it needs to display current month instead of max month of this quarter (sep.)

Thanks,

Ramya