Master Calander

[HMUTUAL01]

I am new to Qlik and trying to modify the following to only allow users to pick dates which are available in the dataset or only enable business days.

Trace "Starting to Load: MASTER CALENDAR";

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Let varMinDate = Num('2023-02-25');

Let varMaxDate = Num(today());

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

Date(TempDate,'YYYY-MM-DD') AS Report_Date,

Date(TempDate,'YYYY-MM-DD') AS REPORT_DATE_KEY,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Trace "Finished: MASTER CALENDAR";

[PrashantSangle]

How many dates you have in your models?? if you want to design master calendar in such a way that it will show only tables date then create you calendar in that table only.

[vinieme12]

the below script will only create calendar based on dates available in the dataset

replace YOURDATEFIELDNAME  with the date field from your dataset which you want to base the calendar on

MasterCalendar:

Load

Date(TempDate,'YYYY-MM-DD') AS Report_Date,

Date(TempDate,'YYYY-MM-DD') AS REPORT_DATE_KEY,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

 ;
Load
FieldValue('YOURDATEFIELDNAME', recno()) as TempDate
Autogenerate fieldvaluecount('YOURDATEFIELDNAME') ;