Master Calendar for multiple columns

[michael_andrews]

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.

[sunny_talwar]

See if this helps:

Better Calendar Scripts | Qlikview Cookbook

[MarcoWedel]

see also:

Canonical Date

regards

Marco

[michael_andrews]

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

[michael_andrews]

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?

[sunny_talwar]

You can use it in Qlik Sense as well

[michael_andrews]

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?

[MarcoWedel]

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

thanks

regards

Marco

[markp201]

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

[rwunderlich · Accepted solution]

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