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));
