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

[ramyasaiqv]

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.

[balar025]

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]

Hi,

Please try the attached.

Thanks,

Varun

[balar025]

here is the single formula.

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

[ramyasaiqv]

Thank you Ravi. Appreciate it.

Regards,

Ramya

[ramyasaiqv]

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]

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]

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

[techvarun]

Try below expression

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

Thanks,

Varun

[ramyasaiqv]

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