Calendar in Qlik

jjustingkm — Thu, 24 Apr 2025 18:57:35 GMT

Is this the most efficient way to create a calendar in Qlik?

SET vFiscalYearStartMonth = 10;

Temp:
Load
Min(MasterDate) as minDate,
Max(MasterDate) as maxDate
Resident [APPS];

//Resident [OTHER];
Let varMinDate = Floor(Num(Peek('minDate', 0, 'Temp')));
Let varMaxDate = Floor(Num(Peek('maxDate', 0, 'Temp')));
Let MonthEnd =floor(monthend(Today(),-1));
DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load*,
Dual('Q' & Ceil(FMonth/3), Ceil(FMonth/3)) AS FQuarter,
If(FiscalFlag=1,right(FYear,4)) as FYearTD,
If(PFiscalFlag=1,right(FYear,4)) as MonthFYearTD;
Load
// TempDate AS Unified_Date,
TempDate AS MasterDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Year(TempDate) As CY,
Month(TempDate) As Month,
Day(TempDate) As Day,
// ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
// Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay,
YearName(TempDate, 0, $(vFiscalYearStartMonth)) AS FYear,
Dual(Month(TempDate), Mod(Month(TempDate)-$(vFiscalYearStartMonth), 12)+1) as FMonth,
if(TempDate-if(Month(TempDate)>=10,MakeDate(Year(TempDate),10,1),MakeDate(Year(TempDate)-1,10,1)) <=
(Today()-if(Month(Today())>=10,MakeDate(Year(Today()),10,1),MakeDate(Year(Today())-1,10,1))),1,0) as FiscalFlag,
if(TempDate-if(Month(TempDate)>=10,MakeDate(Year(TempDate),10,1),MakeDate(Year(TempDate)-1,10,1)) <=
( Date($(MonthEnd), 'MM/DD/YYYY')-if(Month($(MonthEnd))>=10,MakeDate(Year($(MonthEnd)),10,1),MakeDate(Year($(MonthEnd))-1,10,1))),1,0) as PFiscalFlag

// 1 as FiscalFlag
// =Min( {1} MasterDate)

Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

FYEAR:
LOAD text(max(FYearTD)) as maxfyear Resident MasterCalendar;

--{<[=Right(FYear,4)]={"=Right(FYear,4)=maxfyear"}>}

Re: Calendar in Qlik

rwunderlich — Thu, 24 Apr 2025 21:40:01 GMT

No, this is a very old way of doing it. It's more efficient to get the mon/max dates from FieldValues() and pipe the data through a preceding load instead of using temp tables.

See https://qlikviewcookbook.com/2015/05/better-calendar-scripts/

-Rob

topic Calendar in Qlik in App Development

Calendar in Qlik

Re: Calendar in Qlik