Sub routine for Master Calender

[Halesha_Bandri]

In all Qlik Applications we need to have Master Calendar for Date Analysis.

The below Script will help to create master calendar with minimum steps.

The  below Master Calendar script can be store it as QVS File.

In Transform Section, we can just  load that QVS File Using Must_Include statement.

Ex: $(Must_Include=$(vScriptPath)/SubRoutine_MasterCalender.qvs);

After loading single fact or if multiple facts then after link table , we can call master calendar subroutine as like below.

CALL Create_Calender ('MC_Date','MasterCalender',1,1,4);

// =========================================================

// Script:1 ==> Date Field Creation

// =========================================================

Let vStartDate = Num(MakeDate(2022,01,01));

Let vEndDate   = Num(MakeDate(2025,12,31));

Trace vStartDate : $(vStartDate);

Trace vEndDate   : $(vEndDate);

DateTable:

Load Date($(vStartDate) + IterNo()-1)  AS [MC Date],

$(vStartDate) + IterNo()-1    AS sequence

AutoGenerate 1 

While $(vStartDate) + IterNo()-1 <= $(vEndDate)

;

// ==> Drop Variables

Let vStartDate = Null();

Let vEndDate   = Null();

// =========================================================

// Script:2 ==>  Subroutine

// =========================================================

Sub Create_Calender (DateField,CalenderName,FullCalender,FiscalCalender,FiscalStartMonth)

 Let vDateField = '$(DateField)';

 Trace vDateField is $(vDateField);

 Let vCalenderName = IF(Len('$(CalenderName)') =0,'MasterCalender','$(CalenderName)');

 Trace vCalenderName is $(vCalenderName);

 Let vFullCalender = IF(Len('$(FullCalender)')=0,0,'$(FullCalender)');

 Trace vFullCalender is $(vFullCalender);

 Let vFiscalCalender = IF(Len('$(FiscalCalender)')=0,0,'$(FiscalCalender)'); // 1- fiscal calender, 0-Normal Calender

 Trace vFiscalCalender is $(vFiscalCalender);

 Let vFiscalStartMonth = IF(Len('$(FiscalStartMonth)')=0,1,'$(FiscalStartMonth)'); // 1- Jan,2-Feb,3-Mar,4-Apr .......

 Trace vFiscalMonth is $(vFiscalStartMonth);

// =====================================================================================================

// Part-1 : TempMasterCalender

// =====================================================================================================

//==> Generate a temp table of dates

Temp_$(vCalenderName):

Load 1 as Dummy AutoGenerate 0;

IF vFullCalender = 1 THEN 

Trace Genearte ==> Full Calender ==> Source unique Calender Dates + Source Unique Missing Dates; 

        Concatenate(Temp_$(vCalenderName))

Load MaxDate,

Date(NumMinDate + IterNo()-1) as Temp_date

while  NumMinDate + IterNo()-1 <=NumMaxDate;

//=== Get min/max dates from Field ===/

LOAD

Date(min(FieldValue('$(vDateField)', recno()))) as MinDate,

Date(max(FieldValue('$(vDateField)', recno()))) as MaxDate,

Num (min(FieldValue('$(vDateField)', recno()))) as NumMinDate,

Num (max(FieldValue('$(vDateField)', recno()))) as NumMaxDate

AUTOGENERATE FieldValueCount('$(vDateField)');

ELSE

Trace Genearte ==>  Source Calender ==> Source unique Calender Dates  ; 

        Concatenate(Temp_$(vCalenderName))

Load 

Date(FieldValue('$(vDateField)',RecNo())) as Temp_date

Autogenerate FieldvalueCount('$(vDateField)');

END IF

// ==> Drop field 

Drop Field Dummy From Temp_$(vCalenderName);

// =====================================================================================================

// Part-2 : MasterCalender

// =====================================================================================================

IF   vFiscalCalender=1 THEN

        Trace Generate ==> Fiscal Calender;

NoConcatenate

$(vCalenderName):

Load *,

        IF(Date(MonthStart(Date))=LastCompletedMonth,1,0) AS LastCompletedMonthFlag,

        IF(Date(QuarterStart(Date,0,4))=LastCompletedQuarter,1,0) AS LastCompletedQuarterFlag,

        IF(Date(YearStart(Date,0,4))=LastCompletedYear,1,0) AS LastCompletedYearFlag        

        ;

Load *,

Hash128(MC_Date) AS %Date_Key,

Dual(MC_Date,MC_Date) AS Date,

Dual('FY '& Chr(39) & NumFiscalYear,NumFiscalYear) AS Year,

Dual(Date(MonthStart(MC_Date),'MMM'&chr(39)&'YY'),  MonthStart(MC_Date)) AS Month,

Dual('Q' & NumFiscalQuarter & Chr(39) &'FY'& Right(NumFiscalYear,2), QuarterStart(MC_Date)) AS Quarter,

Dual(('WK of ' & Date(WeekStart(MC_Date,0),'DD'&' '&'MMM'&CHR(39)&'YY')),WeekStart(MC_Date)) AS Week,

Day(MC_Date) AS Day,

WeekDay(MC_Date) AS WeekDay,

        Date (MonthStart(max_date))  AS LastCompletedMonth,

        Date (QuarterStart(max_date,-1,4))  AS LastCompletedQuarter,

        Date (YearStart(max_date,-1,4))  AS LastCompletedYear,

;

LOAD *,

NumYear + If(NumMonth<$(vFiscalStartMonth),-1, 0) AS NumFiscalYear,   // Numeric fiscal year (Jan to Mar ==>Year-1, Apr to Dec ==>Year) 

Mod(NumMonth-$(vFiscalStartMonth),12)+1 AS NumFiscalMonth,  // Jan-10,Feb-11,Mar-12,Apr-1,May-2,Jun-3,Jul-4,Aug-5,Sep-6,Oct-7,Nov-8,Dec-9

Num(Ceil((Mod(NumMonth-$(vFiscalStartMonth),12)+1)/3)) AS NumFiscalQuarter // (Jan,Feb,Mar)-4,(Apr,May,Jun)-1,(Jul,Aug,Sep)-2,(Oct,Nov,Dec)-3

;

LOAD

        MaxDate AS max_date,

Temp_date AS MC_Date,

Num(Year(Temp_date)) AS NumYear,   

Num(Month(Temp_date)) AS NumMonth,  // Jan-1,Feb-2,Mar-3,Apr-4,May-5,Jun-6,Jul-7,Aug-8,Sep-9,Oct-10,Nov-11,Dec-12

Num(Ceil(Month(Temp_date)/3)) AS NumQuarter // (Jan,Feb,Mar)-1,(Apr,May,Jun)-2,(Jul,Aug,Sep)-3,(Oct,Nov,Dec)-4 

        ;

        // ==> We are generating date fields in preceding load and not progressing Temp_date further due to ( Temp_date field_value will not hold sorting according to requirement but Date is sorted according to the requirement)  

        Load *

        Resident Temp_$(vCalenderName)

        Order By Temp_date ASC

;

        Drop Table Temp_$(vCalenderName);

        // ==> Drop Un-neccessary fields

        DROP FIELDS

        MC_Date,

        NumYear,   

        NumMonth,

        NumQuarter,

        NumFiscalYear,   

        NumFiscalMonth,  

        NumFiscalQuarter

        FROM $(vCalenderName);

ELSE

Trace Generate ==> Normal Calender;

NoConcatenate

$(vCalenderName):

        LOAD 

        Hash128(Temp_date) AS %Date_Key,

        Temp_date AS Date,

        Year(Temp_date) AS Year,

        Dual(Date(MonthStart(Temp_date),'MMM'&chr(39)&'YY'), MonthStart(Temp_date)) AS Month,

        Dual('Q' & Ceil(Month(Temp_date)/3) & Chr(39) & Year(Temp_date), QuarterStart(Temp_date)) AS Quarter,

        Dual(('WK of ' & Date(WeekStart(Temp_date,0),'DD'&' '&'MMM'&CHR(39)&'YY')),WeekStart(Temp_date)) AS Week,

        Day(Temp_date) AS Day,

        WeekDay(Temp_date) AS WeekDay

;

        // ==> We are generating date fields in preceding load and not progressing Temp_date further due to ( Temp_date field_value will not hold sorting according to requirement but Date is sorted according to the requirement)  

        Load *

        Resident Temp_$(vCalenderName)

        Order By Temp_date ASC

;

        Drop Table Temp_$(vCalenderName);

END IF

// ==> Drop Variables

Let vDateField   = Null();

Let vCalenderName     = Null(); 

Let vFullCalender     = Null(); 

Let vFiscalCalender   = Null();

Let vFiscalStartMonth = Null();

End Sub

// =========================================================

// Script:1 ==>  Call Subroutine

// =========================================================

// ================================================================================

/*

Sub Create_Calender (DateField,CalenderName,FullCalender,FiscalCalender,FiscalStartMonth)

    #). DateField   ==> Need to provide Date field Name with single quotes { 'created_date' OR 'created date' }

    #). CalenderName   ==>  Need to provide Master Calender Name with single quotes { 'Master_Calender' OR 'Master Calender' }

    #). FullCalender      ==> Values should be eithe 1 OR 0 (1- means generates unique  missing dates along with nique transaction dates, 0- means generates unique transaction dates)

    #). FiscalCalender   ==> Values should be eithe 1 OR 0 ( 1- Fiscal Calender (defined by FiscalStartMonth), 0- Normal Calender 1-Jan,2-Feb,3-Mar...)

    #). FiscalStartMonth  ==> Values should be 1 to 12  (1-Jan,2-Feb,3-Mar,4-Apr,5-May...12-Dec ==> Normally for astellas value is 4-Apr)==> month starts from Aprill

*/

// ================================================================================

// Call Create_Calender('MC Date', 'Master_Calender2',1,0,0) ;

Call Create_Calender('MC Date', 'Master_Calender2',1,1,4) ;

[marcus_sommer]

Wouldn't it be much simpler to create a global master-calendar and each application picked their needed fields + periods?