Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Hi,
Try like this
Sum({<MonthYear={"$(=Date(MonthStart(QuarterEnd(Max(Date), -1)),'MMM-YY'))"}>}Price)
Hope this helps you.
Regards,
Jagan.
Hi,
Try like this
Sum({<MonthYear={"$(=Date(MonthStart(QuarterEnd(Max(Date), -1)),'MMM-YY'))"}>}Price)
Hope this helps you.
Regards,
Jagan.
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.
Hi,
Try like this
For Previous Year (Not Fiscal) :
Sum({<MonthYear={"$(=Date(MonthStart(YearEnd(Max(Date), -1)),'MMM-YY'))"}>}Price)
Regards,
Jagan.
For
End of previous FiscalYear:
Sum({<MonthYear={"$(=Date(MonthStart(YearEnd(Max(Date), -1, 7)),'MMM-YY'))"}>}Price)
Regards,
Jagan.
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!