0 Replies Latest reply: Oct 6, 2015 10:02 AM by Dan McGregor RSS

    Moving Annual/Quarters Target Calender

    Dan McGregor

      Hi, below is a our current calendar, which works fine typically for calendar quarters. However what I need it to be able to do is have Moving Quarters (current moving quarter (CMQ) and previous moving quarter (PMQ))

      By moving I mean if we’re in July then the months which makeup the quarter are:

      CMQ = Apr, May Jun

      PMQ = Jan, Feb, Mar

       

      Similarly if we’re in March then the months would be:

      CMQ = Dec, Jan, Feb

      PMQ = Sept, Oct, Nov

       

      And so on

      Any help on this greatly appreciated.

      itsdanny



      //------------------------------------------------------------------------------------------------------------------------------------------------------------

      // Load table of unique values of date field

      //------------------------------------------------------------------------------------------------------------------------------------------------------------

       

       

      CalendarTemp:

      LOAD

      Max(AllCalendarDates) AS DateMax,

      Min(AllCalendarDates) AS DateMin

      ;

      LOAD

      FieldValue('CalendarDate',IterNo()) as AllCalendarDates

      AutoGenerate(1)

      While not IsNull(FieldValue('CalendarDate',IterNo()));

       

       

      //------------------------------------------------------------------------------------------------------------------------------------------------------------

      // Create variabels to be used in calendar creation

      //------------------------------------------------------------------------------------------------------------------------------------------------------------

       

       

      LET vMaxDate = FieldValue('DateMax', 1);

      LET vMinDate = FieldValue('DateMin', 1) -1; // Needed to control the minum date autogenerated in Sales table is included in the MasterCalendar

       

       

      DROP TABLE CalendarTemp;

       

       

      //------------------------------------------------------------------------------------------------------------------------------------------------------------

      // Create master calendar using above variables

      //------------------------------------------------------------------------------------------------------------------------------------------------------------

       

       

      MasterCalendar:

      LOAD

      Date(IterNo() + Date($(vMinDate))) as CalendarDate,

      Week(Date(IterNo() + Date($(vMinDate)))) as CalendarWeek,

      Month(Date(IterNo() + Date($(vMinDate)))) as CalendarMonth,

      Ceil(Month(Date(IterNo() + Date($(vMinDate))))/3) as CalendarQuarter,

      Ceil(Month(Date(IterNo() + Date($(vMinDate))))/6) as CalendarHalf,

      Year(Date(IterNo() + Date($(vMinDate)))) as CalendarYear,

      Year(Date(IterNo() + Date($(vMinDate))))*100+Month(Date(IterNo() + Date($(vMinDate)))) as CalendarYearMonth,

      Year(Date(IterNo() + Date($(vMinDate))))*100+Ceil(Month(Date(IterNo() + Date($(vMinDate))))/3) as CalendarYearQuarter,

      Year(Date(IterNo() + Date($(vMinDate))))*100+Ceil(Month(Date(IterNo() + Date($(vMinDate))))/6) as CalendarYearHalf,

      AutoNumber(Year(Date(IterNo() + Date($(vMinDate))))*100+Month(Date(IterNo() + Date($(vMinDate)))),'_CalendarYearMonth') as _CalendarYearMonthID,

      AutoNumber(Year(Date(IterNo() + Date($(vMinDate))))*100+Ceil(Month(Date(IterNo() + Date($(vMinDate))))/3),'_CalendarYearQuarter') as _CalendarYearQuarterID,

      AutoNumber(Year(Date(IterNo() + Date($(vMinDate))))*100+Ceil(Month(Date(IterNo() + Date($(vMinDate))))/6),'_CalendarYearHalf') as _CalendarYearHalfID,

      InYearToDate(Date(IterNo() + Date($(vMinDate))), Date($(vMaxDate)), 0) as YTDFlag,

      InYearToDate(Date(IterNo() + Date($(vMinDate))), Date($(vMaxDate)), -1) as LYFlag,

      InQuarterToDate(Date(IterNo() + Date($(vMinDate))), Date($(vMaxDate)), 0) as QTDTYFlag,

      InQuarterToDate(Date(IterNo() + Date($(vMinDate))), Date($(vMaxDate)), -1) as QTDLYFlag,

      InMonthToDate(Date(IterNo() + Date($(vMinDate))), Date($(vMaxDate)), 0) as MTDTYFlag,

      InMonthToDate(Date(IterNo() + Date($(vMinDate))), Date($(vMaxDate)), -1) as MTDLYFlag

      AutoGenerate 1 While Date(IterNo() + Date($(vMinDate))) <= Date($(vMaxDate));