Creating Canonical Date For Contracts Data[Start and End Date] and Active Sites [Site Closed Date]

[Sudarshan]

Hi All, 

Good Morning! I have been working on a project in Qlik Sense and I think i might need Canonical Date to do this, but do let me know if you have other solutions.  So here goes: 

I am working on creating a dashboard to visualize the number of active contracts and active sites on a timeline. Here's the sample of the data : 

Sites Data
Active Site Count: 


Here's the Aggregated Closed Date Dimension expression is: 

And the MIN FCD (M)is: 

So the logic of the Aggregated dimension is that: I want to find the minimum of the "Financially Closed Date" and "Actual Start Date", if it is null then i assign the date '12/31/2016', otherwise use the minimum date. 


Contracts Data:
Active Contracts:

Here the Contract Month Dimension Expression is:

And Valid Contracts Expression is :

Now the difficulties I am facing:

These two table when plotted on separate charts work fine. But I have to calculate a 3rd measure called 'Contract Penetration' which is the active sites/active contracts  to find Penetration (%) and I want to plot this over a timeline(Monthly). 

These two have different dimension and I am not sure how to combine them. I tried reading through Canonical Date but not sure if it is applicable here directly.  Any suggestion would be helpful as I am stuck on this for more than  a week now! 

If you require more clarity on the question, I can gladly help!

Thanks in advance 

Best Regards

Sudarshan.