Moving Annual/Quarters Target Calender

Tue, 06 Oct 2015 14:02:50 GMT

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

topic Moving Annual/Quarters Target Calender in QlikView

Moving Annual/Quarters Target Calender