Master Calander

ddd111rgg23 — Tue, 27 Jan 2026 10:45:26 GMT

hello

i have master calander and i try to represent only the last 5 years when i filter,

what should i modify :

QuartersMap:
MAPPING LOAD
RowNo() as Month,
'Q' & Ceil(RowNo()/3) as Quarter
AUTOGENERATE 12;

Temp:
LOAD
Min(CREATION_DATE_1) as minDate,
Max(CREATION_DATE_1) as maxDate
RESIDENT AEC_NEW_EMPLOYEE_RECR_STATUS_QUERY;

LET varMinDate = Num(Peek('minDate', 0, 'Temp'));
LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
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
TempDate AS CREATION_DATE_1,
Week(TempDate) AS Week,
Year(TempDate) AS Cal_Year,
IF(Year(TempDate) <= Year(Today()) AND Year(TempDate) >= Year(Today())-5, Year(TempDate)) AS last_5_Years,
IF(Year(TempDate) <= Year(Today()) AND Year(TempDate) >= Year(Today())-9, Year(TempDate)) AS last_10_Years,
Month(TempDate) AS Cal_Month,
Num(Month(TempDate)) AS Month_Num,
Day(TempDate) AS Day,
ApplyMap('QuartersMap', Month(TempDate), Null()) AS Cal_Quarter,
Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) AS WeekYear,
WeekDay(TempDate) AS WeekDay
RESIDENT TempCalendar
WHERE Year(TempDate) <= Year(Today())
ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

let vSD = Date(AddYears(YearStart(Today()),-4));
let vED = Date(Today());

// Calendar:
// Load
// Date('$(vSD)'+IterNo()-1) as Cal_Date,
// // Date('$(vSD)'+IterNo()-1) as CREATION_DATE_1,
// Year(Date('$(vSD)'+IterNo()-1)) as Cal_Year,
// Month(Date('$(vSD)'+IterNo()-1)) as Cal_Month,
// 'Q'&Ceil(Month(Date('$(vSD)'+IterNo()-1))/3) as Cal_Quarter
// AutoGenerate 1 While Date('$(vSD)'+IterNo()-1) <= '$(vED)';
LET vCurrentYear = Year(Today());

Re: Master Calander

FedericoDellAcqua — Thu, 11 Sep 2025 12:17:27 GMT

you can modify this where condition "WHERE Year(TempDate) <= Year(Today())" adding with AND Year(TempDate) >= Year(Today())-5

Re: Master Calander

ddd111rgg23 — Sun, 14 Sep 2025 06:18:07 GMT

thank you , it works now

topic Re: Master Calander in Move to Qlik Cloud Analytics

Master Calander

Re: Master Calander

Re: Master Calander