Generate your own calendar table in Qlikview?

• We need start date of calendar, in my case I have taken minimum date from fact table.
• We need End Date of calendar, I have taken today's date.

To get min. date from Fact:-

Min:
LOAD min (Date_field) as Min_Date
resident Fact;

let vMin_Date=num(floor(Peek('Min_Date')));
let vMax_Date=num(floor(today()));

To Generate Calendar between min and max date:-

Calendar:
load date(RowNo()+ $(vMin_Date)-1) as Calendar_Date
AutoGenerate ($(vMax_Date)-$(vMin_Date)) ;


This will populate all the dates between min date to max date. Now we can create other date Matrix using Calendar_Date.

Calendar_Table:
load *,
        Month(Calendar_Date) as Month,
  Hour(Calendar_Date) as Hour_Calendar,
  Day(Calendar_Date) as Day,
  Date(floor(Calendar_Date)) as Date,
  Date((floor(Calendar_Date)), 'DD/MM/YYYY') As DateField,
WeekDay(floor(Calendar_Date)) as WeekDay,
Year(date(floor(Calendar_Date))) as Year,
  dual(Month(Calendar_Date)&'-'&Year(Calendar_Date),Year(Calendar_Date)&num(month(Calendar_Date),00)) as YearMonthDis,
  (date(WeekStart(date(Calendar_Date,'DD/MM/YYYY')),'DD/MM') & '-' & date(WeekEnd(date(Calendar_Date,'DD/MM/YYYY')),'DD/MM')) as WeekPeriod,
  MonthStart(floor(Calendar_Date)) as MonthStart,
  Year(Calendar_Date)&num(Month(Calendar_Date),00) as YearMonth,
  Year(Calendar_Date)&num(Week(Calendar_Date),00) as YearWeek,
  'Q'&(FLOOR(NUM(MONTH(Calendar_Date)-1)/3)+1) AS Quarter,
     Date(monthstart(Calendar_Date), 'MMM-YYYY') AS MonthYear,
     Week(Calendar_Date)&'-'&Year(Calendar_Date) AS WeekYear
 
   Resident Calendar;
Drop table Calendar;

Now we have Calendar_Table ready in model to be used.