Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Linking two master calendars?

 

I have two master calendars, one represents the date of events, the other the date of issues. I want to have a sheet displaying all the events (at a particular venue) and all the problems there over one set of dates…without having to select two sets of dates.

How can I link the dates?

I can get something almost useful working using:

=if(IssueDate>(date(Min(Day)&'/'&Min(Month)&'/'&Min(Year),'DD/MM/YYYY')+1) and  
IssueDate<=(date(Max(Day)&'/'&Max(Month)&'/'&Max(Year),'DD/MM/YYYY')+1)
,(
IssueHrs)/24)

 

But it doesn’t quite work. Selecting July and 2017 should total the three in July but doesn’t. September works but I suspect only because the Issues were of the same dates as entries in the Events table? 

Also, this method won’t seem to give me just a list of the issues arising during the period of the events like I’d hoped, but a list of all issues and a figure only in the column (which I can total).

QVW attached – this is sample data and actual data is much more complicated.

I think I’m probably going at this in entirely the wrong direction. Any help would be much appreciated.

 

1 Solution

Accepted Solutions
vkish16161
Creator III
Creator III

You can look into P and E element functions in Set Analysis. These might fit your requirement as they'll save you the trouble of creating a linkage between the 2 tables.

Maybe something like this:

Count(

{<Issue_Date= P(Event_Date)>}

Problems)

fields Issue_Date and Problems should come from the same table to be meaningful.

View solution in original post

5 Replies
effinty2112
Master
Master

Hi Christine,

Maybe concatenate Issues and Events into a single Fact table with one calendar?

1.png

Good luck

Andrew

Anonymous
Not applicable
Author

Thank you Andrew, but the tables in the actual app far more complicated than my sample data and there may be many issues on a date with no events and vice versa.

effinty2112
Master
Master

Hi Christine,

It may be that concatenation into a single fact table is not the answer for you but having many issues per date with no events or vice versa does not rule out concatenation. If two fact tables are of the same granularity then concatenation is often good strategy.

An other option is the use of a canonical calendar:

Canonical Date

Kind regards

Andrew

rafik1983
Contributor II
Contributor II

Hi Christine,

for Multiple Dates With Master Calendar,  You can use only one Isolated Master Calender.Capture.JPG

Best regards

Rafik.

vkish16161
Creator III
Creator III

You can look into P and E element functions in Set Analysis. These might fit your requirement as they'll save you the trouble of creating a linkage between the 2 tables.

Maybe something like this:

Count(

{<Issue_Date= P(Event_Date)>}

Problems)

fields Issue_Date and Problems should come from the same table to be meaningful.