topic Re: Linking two master calendars? in QlikView

Linking two master calendars?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Linking two master calendars?

effinty2112 — Thu, 12 Oct 2017 13:24:02 GMT

Hi Christine,

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

Good luck

Andrew

Re: Linking two master calendars?

Anonymous — Thu, 12 Oct 2017 13:32:23 GMT

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.

Re: Linking two master calendars?

effinty2112 — Thu, 12 Oct 2017 13:45:47 GMT

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

Re: Linking two master calendars?

rafik1983 — Thu, 12 Oct 2017 14:17:54 GMT

Hi Christine,

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

Best regards

Rafik.

Re: Linking two master calendars?

vkish16161 — Thu, 12 Oct 2017 20:18:00 GMT

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.