Syn Tables bad design

[qliklizzy]

Hi

I have an app which gives me future clinic(clinics) and theatre utilisation (fact), I was able to bring two different data sets that will never link to show capacity gaps in both areas using calendars complete separate so I was happy to have a syn table between my fact and clinic tables when using the master calendar.

however I am further depveloping the app, for the use of planning - so I am trying to link in an admissions table, to show what actually happened as well as what's planned 'In month' to produce a forecast which currently resides in a spreadsheet manually done with no drill through. 

But I now get syn 3 tables as I thought I need the admission date to be the same date as op date so I can select a month and show actual and plan for December - if you see what I mean.  I have a spell number field that would connect Theatre(fact) to admissions but how would I get the date to connect to master? - so confusing

I think its my bad design and not understanding the links needed for master calendar ? I've tried to read up as its quite a popular question but I just cannot relate it to what I am doing - if any one can help I would greatly appreciate it!

I have attached my scrambled app

thank you

Liz

[dplr-rn]

Am i right in understanding you have multiple date fields with which you need to filter with a common date filter?
if so you need a canonical date table. See below

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

[qliklizzy]

I think so - this could be it.
I will try to put it into practice today ! thanks .... will let you know

[dplr-rn]

happy to help

[qliklizzy]

Hi

I have created my bridge; thanks! new skill I now have 🙂

however not sure its helping me, I have date for op date, and discharge date for activity date, and when I want to view week commencing 26th November I should just get those that were discharged that week as the date has passed, but if I want to look at December as month - I will have discharges in December and planned op dates in December also to create a complete forecast if you see what I mean?

at the moment if I select December I don't see week commencing 26th November so I miss activity for 1st & 2nd December, and if I select WC 26th November I get discharges in November also. 😐 I'm getting really confused how I should arrange my dates to make this work.

Thank you for helping with the bridge table though its really useful to know when comparing datasets.

[qliklizzy]

Think I'm nearer to it,

but I have noticed a patient in the fact table with 1 spell number across two rows one  cancelled and the other treated so its being brought in twice getting confused, which I didn't realise could happen in that table 😞

what a mess!