Setting MIN and MAX dates on Master Calendar

[SI_NY02]

I created a master calendar with a field called "date".

I picked up this load logic to create MINDATE and MAXDATE which works, but it ultimately gives me dates starting in 1966.  I'd prefer for the MINDATE to be 1/1/2016.  However, when I tried changing to

Date ('1/1/2016') as MinDate,

it then messed up the calculation for YearMonth so that it shows weeks instead of the months.  There is probably something else I need to adjust. I'm fairly new to load logic, can someone direct me here?

MinMaxTemp:
Load
Date(Min(FieldValue('Date', Recno()))) as MinDate,
Date(Max(FieldValue('Date', Recno()))) as MaxDate

AUTOGENERATE FieldValueCount('Date');

Let vToday = NUM(PEEK('MaxDate',0,'MinDate'));
Let vRealToday = NUM(Today());

//**** Create the AddedDate field ****
MasterCalendar_Temp:
Load
Date(MinDate + IterNo() - 1) as Date // Create the AddedDate field
Resident MinMaxTemp
WHILE MinDate + IterNo() - 1 <= MaxDate; //-1: An additional iteration is needed to reach the max date

Drop Table MinMaxTemp;

//*** Create the Master Calendar ****
MasterCalendar:
Load
Date,
Week(Date) as Week,
Year(Date) as Year,
Month(Date) as Month,
Day(Date) as Day,
Weekday(Date) as WeekDay,
Dual('Q' & Ceil(Month(Date) / 3), Ceil(Month(Date) / 3)) as Quarter, // new for better sorting
Date(MonthStart (Date), 'YYYY-MMM') as MonthYear,
Dual(Year(Date)&'-'&Month(Date), monthstart(Date)) AS [YearMonth],
Week(Date) & '-'& if(Week(Date)=1 AND Month(Date)=12, Year(Date)+1, If (Match(Week(Date), 52, 53) AND
Month(Date)=1, Year(Date)-1, Year(Date))) as WeekYear,
InYearToDate(Date,$(vToday), 0) * -1 as CYTDFlag,
InYearToDate(Date,$(vToday), -1) * -1 as LYTDFlag,
If(DayNumberOfYear(Date) <= DayNumberOfYear($(vToday)), 1, 0) as IsInYTD,
If(DayNumberOfQuarter(Date) <= DayNumberOfQuarter($(vToday)), 1, 0) as IsInQTD,
If(Day(Date) <= Day($(vToday)), 1, 0) as IsInMTD,
If(Month(Date) <= Month($(vToday)), 1, 0) as IsCurrentMonth,
If(Month(AddMonths(Date,1)) = Month($(vToday)), 1, 0) as IsLastMonth

[anat]

you can try other approach by taking min and max date from your fact table.

https://community.qlik.com/t5/QlikView-App-Dev/Creating-A-Master-Calendar/td-p/341286

[akmalquamri]

Hi There,

I tried to encounter the error however I couldn't. 

I am writing a master calendar script do the required changes as per your column name and copy paste to check whether this is working or not?

Min_max:
Load 
NUM(Min("Date")) as Min_date,
NUM(Max("Date")) AS Max_date
Resident table;

Let vMin_date= Peek('Min_date',0,'Min_max');
let vMax_date= peek ('Max_date',0,'Min_max');
drop Table Min_max;

Temp_Calendar:
load 
Date($(vMin_date) + IterNo() -1) as Date
AutoGenerate 1 While ($(vMin_date) + IterNo() -1<= $(vMax_date));

Master_Calendar:
Load 
Date as "NewDate",
Week(Date) as week,
WeekDay(Date) as Dayname,
MonthName(Date) as "Month_start",
Resident Temp_Calendar;

Drop Table Temp_Calendar;

[anat]

Resident table

here table means u need to define your actual table name from where you are getting Date field

[SI_NY02]

Hi Anat - I'm getting an error - see in RED

Min_max:
Load
NUM(Min("CanonicalDate")) as Min_date,
NUM(Max("CanonicalDate")) AS Max_date
Resident MasterCalendar;

Let vMin_date= Peek('Min_date',0,'Min_max');
let vMax_date= peek ('Max_date',0,'Min_max');
drop Table Min_max;

Temp_Calendar:
load
Date($(vMin_date) + IterNo() -1) as Date
AutoGenerate 1 While ($(vMin_date) + IterNo() -1<= $(vMax_date));

Master_Calendar:
Load
Date as "NewDate",
Week(Date) as week,
WeekDay(Date) as Dayname,
MonthName(Date) as "Month_start",
Resident Temp_Calendar;

Drop Table Temp_Calendar;

[anat]

can u share the error message