5 Replies Latest reply: May 4, 2015 1:39 AM by James Kerrigan RSS

    Last month of previous quarter?

    James Kerrigan

      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)