Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Moving Annual/Quarters Target Calender

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

Community Browser