Sub routine for Master Calender

Halesha_Bandri — Fri, 20 Jun 2025 05:47:04 GMT

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

;

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

Re: Sub routine for Master Calender

marcus_sommer — Fri, 20 Jun 2025 08:39:32 GMT

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

topic Re: Sub routine for Master Calender in App Development

Sub routine for Master Calender

Re: Sub routine for Master Calender