Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
Hi,
Please try the attached.
Thanks,
Varun
here is the single formula.
sum(TSI)+sum({<MonthName={$(=(Q-1)*3)},Date>}TSI)+sum({<MonthName={$(=(Q-2)*3)},Date>}TSI)
Thank you Ravi. Appreciate it.
Regards,
Ramya
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
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
Please can someone help me with this issue? i really appreciate your time and efforts. Thank you,
Try below expression
=sum({<Month={"$(=MaxString(Month))"}>}Data)
Thanks,
Varun
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