Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Last month of previous quarter?

Hey guys,

I'm trying to select data for the month of the previous quarter and am having some trouble doing so. The months are like so.

Q1 - Jul, Aug, Sept

Q2 - Oct, Nov, Dec

Q3 - Jan, Feb, Mar

Q4 - Apr, May, Jun

And my MonthYear is as so:

Jan-14

Feb-14

Apr-14

etc. up to Jan-15

And my load statement for dates is as so:

  

    SET vFiscalOffset =6;

  

    Date(Date) as "Date",

    Floor(Date) as DateNum,

    Week(Date) as "Week",

    Month(Date) as "Month",

    'Q' & Ceil(Month (Date)/3) as Quarter,

    Year(Date) as "Year",

    Date(MonthStart(Date), 'MMM-YY') as MonthYear,

    num(year(addmonths(Date, $(vFiscalOffset)))) AS FiscalYear,

    'Q' & ceil(month(monthstart(Date, $(vFiscalOffset))) / 3) AS FiscalQtr,

    num(year(addmonths(Date, $(vFiscalOffset)))) & ' ' & 'Q' & ceil(month(monthstart(Date, $(vFiscalOffset))) / 3) AS FiscalYearQtr,

If a MonthYear such as Nov-14 is selected, I want to only have data showing for the last month of the previous Qtr, which would be Sept-14.


Currently I can do this for the previous month easily enough with this:

Sum({<MonthYear={"$(=Date(MonthEnd(Max(MonthYear),-1),'MMM-YY'))"}>}Price)

I've tried using the methods shown here:YTQ, QTD, MTD and WTD and the one for previous, but I still can't seem to get it to work.

If anyone is able to help that would be greatly appreciated.

(I'm not able to post the app sorry)

1 Solution

Accepted Solutions
MVP
MVP

Re: Last month of previous quarter?

Hi,

Try like this

Sum({<MonthYear={"$(=Date(MonthStart(QuarterEnd(Max(Date), -1)),'MMM-YY'))"}>}Price)


Hope this helps you.

Regards,
Jagan.

5 Replies
MVP
MVP

Re: Last month of previous quarter?

Hi,

Try like this

Sum({<MonthYear={"$(=Date(MonthStart(QuarterEnd(Max(Date), -1)),'MMM-YY'))"}>}Price)


Hope this helps you.

Regards,
Jagan.

Not applicable

Re: Last month of previous quarter?

That's working like a charm thanks so much! I'm still learning when it comes to this type of syntax so it's great you guys are so helpful!

I haven't answered your answer as correct yet because I stil have a couple of things similar I'm trying to solve.

What would the equation be to select the end of the previous FiscalYear, and the end of the previous Year (not fiscal)?

End of previous FiscalYear being Jun-14 and end of previous year being Dec-14 in this case.

I can't seem to work it out.

MVP
MVP

Re: Last month of previous quarter?

Hi,


Try like this


For Previous Year (Not Fiscal) :

Sum({<MonthYear={"$(=Date(MonthStart(YearEnd(Max(Date), -1)),'MMM-YY'))"}>}Price)


Regards,

Jagan.



MVP
MVP

Re: Last month of previous quarter?

For

End of previous FiscalYear:

Sum({<MonthYear={"$(=Date(MonthStart(YearEnd(Max(Date), -1, 7)),'MMM-YY'))"}>}Price)


Regards,

Jagan.

Not applicable

Re: Last month of previous quarter?

That's awesome man it's all working perfectly thanks so much! I actually worked out the year one just before you posted it, but had no clue about the fiscal year. Thanks for that!