topic Re: Master Calendar for multiple columns in QlikView

Master Calendar for multiple columns

michael_andrews — Mon, 26 Jan 2026 16:26:21 GMT

I followed your master calendar tutorial to get all dates to show. My question is, I have more than one date column I'd like to add this on. Am I going to have to copy and paste the code for each column? Here's what I have now

QuartersMap:  
MAPPING LOAD   
rowno() as Month,  
'Q' & Ceil (rowno()/3) as Quarter  
AUTOGENERATE (12);  
      
Temp:  
Load  
min(exam_date) as minDate,  
max(exam_date) as maxDate  
Resident exam;  
      
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  
  TempDate AS exam_date,  
  week(TempDate) As Week,  
  Year(TempDate) As Year,  
  Month(TempDate) As Month,  
  Day(TempDate) As Day,  
  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;

But I also have a cert_date column, as well as a date_updated column. Is there a better way to do that on multiple columns?

Also, none of the date picker extensions out there work for filtering, do you have any suggestions on a better date filter? Honestly, your guys' date filter is pretty lacking.

Re: Master Calendar for multiple columns

sunny_talwar — Thu, 29 Sep 2016 21:17:11 GMT

See if this helps:

Better Calendar Scripts | Qlikview Cookbook

Re: Master Calendar for multiple columns

MarcoWedel — Thu, 29 Sep 2016 21:22:00 GMT

Re: Master Calendar for multiple columns

michael_andrews — Thu, 29 Sep 2016 21:25:15 GMT

Maybe I tagged this wrong, we have qliksense. Those look great, but they look like they're all for qlikview

Re: Master Calendar for multiple columns

michael_andrews — Thu, 29 Sep 2016 21:32:02 GMT

Let me run this by you quick if I'm thinking about this correctly. So say I have an exam table with exam_date, exam_expire, and exam_active, that I want to have the date thing.

I would create a master calendar with all possible dates, then create another table that has exam_id (the pk for table exam) that links to my exam table, and a date field that links to the master calendar?

If that's the case, I'm not following how I would still map that to the three date columns?

Re: Master Calendar for multiple columns

sunny_talwar — Thu, 29 Sep 2016 21:32:45 GMT

You can use it in Qlik Sense as well

Re: Master Calendar for multiple columns

michael_andrews — Thu, 29 Sep 2016 21:37:07 GMT

How do I do that? Whenever I try to open qvw files, they're just unreadable text. I'm not sure how you install those on qlik sense because doesn't it require js files and the qext file and what not?

Re: Master Calendar for multiple columns

MarcoWedel — Thu, 29 Sep 2016 22:39:09 GMT

please post some sample data and your expected result to demonstrate.

thanks

regards

Marco

Re: Master Calendar for multiple columns

markp201 — Thu, 28 Jun 2018 14:28:57 GMT

If the dates reference the same grain (e.g. order processing has order_date and shipped_date)

you can create a role-playing table which connects (bridges) your facts to the master calendar

%Fact	Date Type	%Date
1	Ordered	1
1	Shipped	5
2	Ordered	6

%Date	Calendar Date
1	6/1/2018
5	6/3/2018
6	5/25/2018
6	6/1/2018

Re: Master Calendar for multiple columns

rwunderlich — Fri, 29 Jun 2018 04:54:38 GMT

See this tutorial and download the Qlik Sense example file:

Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

-Rob

http://masterssummit.com

http://qlikviewcookbook.com