1 Reply Latest reply: Jul 27, 2015 10:23 AM by Simon Hogg RSS

    Group Dates into YTD, YTD-1, YTD -2, etc.

    Simon Hogg

      I have a bunch of measures in rows, and I want to do a YTD comparison for the past X years (in the columns).

       

      I have a master calendar set up so I can ask the user to select months and dates.  Is that the place to also set up a dimension with the members "Current YTD" "YTD-1" "YTD-2" etc.?

       

      I can then ask the user to select the periods she wants to see in a filter.  This will also avoid using the Pivot Table object.

       

      Does this make sense or is there a better way to do this?  (One way would be in a load script but that would test every date and see where it fits so it wouldn't benefit directly from the associative model.)

        • Re: Group Dates into YTD, YTD-1, YTD -2, etc.
          Simon Hogg

          I think I've answered my own question.  In my Master Calendar I added a function to test each date to see if it's in the Current YTD or not. It needs a bit more refinement but I think it answers my needs;

           

          MasterCalendar: 

          Load 

            TempDate AS "Sale Date", 

            week(TempDate) As Week, 

            Year(TempDate) As Year, 

            Month(TempDate) As Month,

            IF(inyeartodate(TempDate, today() ,0),'YTD','notYTD') as YTD,

            Day(TempDate) As Day, 

            ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

            Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

            WeekDay(TempDate) as WeekDay 

          Resident TempCalendar