Date variable projecting forward to 2020

[Ken_Hardwick]

Hello,

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!

[Mauritz_SA]

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

[Ken_Hardwick]

That is helpful Mauritz, thank you.

[Gopi_E]

remove Num() before  variable creation

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
DROP Table Temp; 

then try ones