Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
michael_andrews
Partner - Creator
Partner - Creator

Master Calendar for multiple columns

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.

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

9 Replies
sunny_talwar

MarcoWedel

see also:

Canonical Date

regards

Marco

michael_andrews
Partner - Creator
Partner - Creator
Author

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

michael_andrews
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

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
Creator III
Creator III

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
1Ordered1
1Shipped5
2Ordered6

%Date
Calendar Date
16/1/2018
56/3/2018
65/25/2018
66/1/2018
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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