How to generate a complex Master Calendar (complex fields) ?

[Andi_G]

Hello,

I am quite new to Qlik Sense scripting, and generally scripting in any form (except Excel). 

I am developing an app that has multiple fact tables and dimensions, and have used the Link Table approach.

I need to generate the Master Calendar, but the scripts I find online are usually simple and don't include more complex fields.

I have currently generated it using the work-around below. I basically derived the Date field, and then copied the AutoCalendar script (generated via the Data Manager from another app), and adapted it for my manual Data Load script for this app. I have no idea how the AutoCalendar is calculating all those fields.

I would like however to create normal fields in my Master Calendar table, instead of the autogenerated ones. 

Where can I find a ready script that generates all possible Master Calendar fields?

//-------------------------------------------------
// Generating Master Calendar and additional calendar fields using AutoCalendar script
//-------------------------------------------------

StartAndEndDates:
Load
MIN([Date]) AS FirstDate,
MAX([Date]) AS LastDate
Resident LinkTable;

LET vFirstDate = NUM(PEEK('FirstDate', 0, 'StartAndEndDates'));
LET vLastDate = NUM(PEEK('LastDate', 0, 'StartAndEndDates'));

TempCal:
LOAD
DATE($(vFirstDate) + ROWNO () -1 ) AS TempDate
AUTOGENERATE
$(vLastDate) - $(vFirstDate) + 1;

MasterCalendar:
Load
TempDate AS Date
Resident TempCal;

[AutoCal]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [Date] USING [AutoCal];

DROP TABLES StartAndEndDates, TempCal;

[Kushal_Chawda · Accepted solution]

@Andi_G  you can copy paste your autocal script in your master calendar load and modify it little bit like below.

1) Remove Tag statements  from fields

2) Replace $1 with TempDate

3) Once copied AutoCal fields , remove entire AutoCal load statement

Your final script would look line below. Make sure to replace $1.

StartAndEndDates:
Load
MIN([Date]) AS FirstDate,
MAX([Date]) AS LastDate
Resident LinkTable;

LET vFirstDate = NUM(PEEK('FirstDate', 0, 'StartAndEndDates'));
LET vLastDate = NUM(PEEK('LastDate', 0, 'StartAndEndDates'));

TempCal:
LOAD
DATE($(vFirstDate) + ROWNO () -1 ) AS TempDate
AUTOGENERATE
$(vLastDate) - $(vFirstDate) + 1;

MasterCalendar:
Load TempDate AS Date,
Dual(Year($1), YearStart($1)) AS [Year] ,
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter],
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] ,
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter],
Month($1) AS [Month],
....
Resident TempCal;

DROP TABLES StartAndEndDates, TempCal;

[Andi_G]

@Kushal_Chawda  thank you for your help. The solution you suggested worked very well.

I will paste here the full script I used, in case someone else needs to use it in the future.

//-------------------------------
// Generating Master Calendar dimension and additional calendar fields
//-------------------------------

StartAndEndDates:
Load
	MIN([Date]) AS FirstDate,
    MAX([Date]) AS LastDate
Resident LinkTable;


LET vFirstDate = NUM(PEEK('FirstDate', 0, 'StartAndEndDates'));
LET vLastDate = NUM(PEEK('LastDate', 0, 'StartAndEndDates'));

TempCal:
LOAD
	DATE($(vFirstDate) + ROWNO () -1 ) AS TempDate
AUTOGENERATE
	$(vLastDate) - $(vFirstDate) + 1;


MasterCalendar:
Load
	//TempDate AS Date,
	Date(Floor(TempDate)) AS [Date],
	Date(Floor(TempDate), 'D') AS [Day],
	Dual(Year(TempDate), YearStart(TempDate)) AS [Year],
	Dual('Q'&Num(Ceil(Num(Month(TempDate))/3)),Num(Ceil(NUM(Month(TempDate))/3),00)) AS [Quarter],
	Dual(Year(TempDate)&'-Q'&Num(Ceil(Num(Month(TempDate))/3)),QuarterStart(TempDate)) AS [YearQuarter],
	Dual('Q'&Num(Ceil(Num(Month(TempDate))/3)),QuarterStart(TempDate)) AS [_YearQuarter],
	Month(TempDate) AS [Month],
	Dual(Year(TempDate)&'-'&Month(TempDate), monthstart(TempDate)) AS [YearMonth],
	Dual(Month(TempDate), monthstart(TempDate)) AS [_YearMonth],
	Dual('W'&Num(Week(TempDate),00), Num(Week(TempDate),00)) AS [Week],
	If (DayNumberOfYear(TempDate) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
	Year(Today())-Year(TempDate) AS [YearsAgo] ,
	If (DayNumberOfQuarter(TempDate) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
	4*Year(Today())+Ceil(Month(Today())/3)-4*Year(TempDate)-Ceil(Month(TempDate)/3) AS [QuartersAgo] ,
	Ceil(Month(Today())/3)-Ceil(Month(TempDate)/3) AS [QuarterRelNo] ,
	If(Day(TempDate)<=Day(Today()),1,0) AS [InMTD] ,
	12*Year(Today())+Month(Today())-12*Year(TempDate)-Month(TempDate) AS [MonthsAgo] ,
	Month(Today())-Month(TempDate) AS [MonthRelNo] ,
	If(WeekDay(TempDate)<=WeekDay(Today()),1,0) AS [InWTD] ,
	(WeekStart(Today())-WeekStart(TempDate))/7 AS [WeeksAgo] ,
	Week(Today())-Week(TempDate) AS [WeekRelNo] 
Resident TempCal;

DROP TABLES StartAndEndDates, TempCal;

[Gaël]

Hi @Andi_G and @Kushal_Chawda,

Thanks for nicely summarizing the Master Calendar creation. I got so far on my own as well, maybe not defining all the same fields as dual, but one follow-up question remains: How to I tag the fields of the single calendars so that the x-axis of my graphs zoom as nicely as with autoCalendar? I use this to create single calendars:

SUB createCalendar(tablename, key, prefix)
  $(tablename):
  LOAD
	Date(Floor(Date#(Date, 'DD/MM/YYYY')), 'DD/MM/YYYY')  AS [$(key)],
    [Date]					AS [$(prefix) Date],
    [Year] 					AS [$(prefix) Year],
    [Quarter]				AS [$(prefix) Quarter],
    [YearQuarter] 			AS [$(prefix) YearQuarter],
		...
  RESIDENT MASTER_CALENDAR;

  // post-hoc tagging for Qlik Sense magic to work
  TAG FIELD [$(prefix) Date]			WITH '$axis',	'$date',        '$qualified';
  TAG FIELD [$(prefix) Year]			WITH '$axis',	'$year';
  TAG FIELD [$(prefix) YearQuarter]		WITH '$axis',	'$yearquarter', '$qualified';
  TAG FIELD [_$(prefix) YearQuarter]	WITH '$hidden',	'$yearquarter', '$simplified';
	...

end sub;

call createCalendar('ReceptionCalendar', 'ReceptionDate', 'Reception');

Here's a visual comparison of autoCalendar and tagged copy of Master Calendar: