I am trying to find an error in some script that was developed for us by a third party. We have a report that has stopped working due to the knock on effect of our date calendar now projecting to 2020. I can see in the variables that a variable called varMaxDate is calculating a value 43887 (in Excel this is showing me it is 26/02/2020 which is the date in Qlik too).
I have used the find function to look through all of the sections within the script and the only mention of the variable varMaxDate is in the below script.
//CalDate field exists elsewhere in script and is the link. Example: date(InvoiceDate, 'DD/MM/YYYY') as [CalDate],
QuartersMap: MAPPING LOAD rowno() as Month, 'Q' & Ceil (rowno()/3) as Quarter AUTOGENERATE (12);
Temp: Load min(CalDate) as minDate, max(CalDate) as maxDate Resident Fact;
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 MonthStart(date([TempDate],'YYYYMM')) as Period1,
Date(monthstart(TempDate), 'YYYYMM') as CalYearMonth,
12*(Year(Today())-Year(TempDate)) + Month(Today()) - Month(TempDate) as MonthsAgo,
// MonthStart(date([TempDate],'YYYYMM')) as Period1, TempDate AS CalDate, Date(monthstart(TempDate), 'MMM-YY') as CalMonthYear, week(TempDate) As CalWeek, Year(TempDate) As CalYear, Month(TempDate) As CalMonth, Day(TempDate) As CalDay, ApplyMap('QuartersMap', month(TempDate), Null()) as CalQuarter, Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as CalWeekYear, weekstart(date(TempDate, 'DD/MM/YYYY')) as [CalWeekCommencing], WeekDay(TempDate) as CalWeekDay Resident TempCalendar Order By TempDate ASC; Drop Table TempCalendar;
As I'm still new to Qlik I cannot figure out why this script is generating 26/02/2020 for the calendar. Any help would be great!
Hi there Somehere in your load script there is a table called Fact. In that table there is a field/column called CalDate and your calendar is generated up to the maximum date in that field. Hope that helps. Regards, Mauritz