Previous month of current quarter as the current quarter

[Anonymous]

Hi,

I’m totally new to QlikView, trying to build a chart and need help. I did some research in the community and found a similar issue but mine is little different. I was able to make it to work to certain extent by using the post on community and need some help with the 2nd part of the issue.

The requirement is I need to plot a chart which displays last month of every quarter for example for Q1-2017 it should be Mar-2017, Q2-Jun 2017 and for current quarter it should be the previous month. Basically there will be data only till to the previous month of current quarter (for Q3-2017 data is there only till July-2017). Ideally need to display in the current quarter till to the max date data is available.

Last month of every quarter for example for Q1-2017 it should be Mar-2017, Q2-Jun 2017-This part is working and need help with current quarter data.

In the current quarter as in the attached excel and qvw there is data for July-2017 this is displayed correctly on the monthly chart but not on the quarterly chart. Basically july-2017 data should be displayed as Q3-2017.  Please help me in solving this issue.

Thanks and Regards,

Harini

[tresesco]

May be like:

Sum(If(MonthNum=Max(total {<Data={"=Len(trim(Data))>0"}>} MonthNum), Data,

If(mod(MonthNum,3)=0 , Data)))

[Kushal_Chawda]

Please see the attached file, Instead of using complex expression on front end create the Flag in script. this will make the expression simple and optimized

[Anonymous]

Hi Tresesco,

Thank you very much for the response unfortunately this is working only for 2017 data, when i add 2016 data it's not working. I have attached the excel with 2016 & 2017 data. I'm sorry initially i didn't add 2016 data in the excel. when you get a chance please can you look into it.

Thank you,

Harini

[Anonymous]

Hi Kushal,

Thank you very much for the response unfortunately this is working only for 2017 data, when i add 2016 data it's not working ! !. I have attached the excel with 2016 & 2017 data. I'm sorry initially i didn't add 2016 data in the excel. when you get a chance please can you look into it.

Thank you,

Harini

[tresesco · Accepted solution]

This?

[Kushal_Chawda]

So for 2016 latest quarter , you want previous month data?

[Anonymous]

Kushal,

For all completed year's(Quarter's it should be the last month of the Quarter (Q1-March, Q2-June, Q3-Sep, Q4-Dec). Only for current quarter of the current year it should be previous month because data will available till previous month only. Thank you,

Harini

[Anonymous]

Does it mean it should be like this:

2016:

Q1: March

Q2: June

Q3-Sep,

Q4-Dec

2017:

Q1: March

Q2: June

Q3: Jul ??

Q4: ??

[Anonymous]

Hi,

2016:

Q1: March

Q2: June

Q3-Sep,

Q4-Dec

2017:

Q1: March

Q2: June

Q3: Jul ??   (yes, this is the previous month of Current quarter, there is no data for August), once we are in Sep it should display August because there will be no data for Sep, then in October it should be Sep, in November it should be Sep, in Dec also Sep (as Q3-completed)

Q4: ?? at the moment there is no data for Q4 but once in Q4 – October should display Sep data, November should display October Data, Dec should display November Data – This previous month data logic applies to only to current year

For past (completed) years: It’s always

Q1- March

Q2-June

Q3-Sep

Q4-Dec