Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
ramyasaiqv
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
techvarun
Specialist II
Specialist II

Hi

Try the attached.

View solution in original post

15 Replies
balar025
Creator III
Creator III

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)

techvarun
Specialist II
Specialist II

Hi,

Please try the attached.

Thanks,

Varun

balar025
Creator III
Creator III

here is the single formula.

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

ramyasaiqv
Creator II
Creator II
Author

Thank you Ravi. Appreciate it.

Regards,

Ramya

ramyasaiqv
Creator II
Creator II
Author

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

ramyasaiqv
Creator II
Creator II
Author

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

ramyasaiqv
Creator II
Creator II
Author

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

techvarun
Specialist II
Specialist II

Try below expression

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

Thanks,

Varun

ramyasaiqv
Creator II
Creator II
Author

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