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