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