Canonical Calendar with multiple keys and hierarchy

[Quentinn]

Hi everyone,

my ultimate goal is to create a canonical calendar which still interacts with the data as a (that is, making selection etc)

my data is structured like this:

Sample:
- sampleid, registrationdate, validationdate and validationenddate

Subsample:

- subsampleid, sampleid, createddate

Slide:

- slideid, subsampleid, cutdate, scanneddate

Antibodyexecution:

- antibodyexecutiondate

structure is like this: a sample is devised in multiple sub samples, and a subsample is devised in multiple slide (clear hierarchical relationship). The antibodyexecutiontable is a bit special: some slides have a certain antibodyexecution

could someone please help me in creating a suitable canonical calendar for this? It’ s something I haven’t been able to find out. It would help tremendously. I was successful creating a canonical calendar without including the id references, so that calendar works, but doesn’t link to the data as it should

[rubenmarin]

Hi, did you follow this example?: https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

Note that ther is a DateType field, and this one need to be used in expression to chosee the date it has to applied.

You can use a variable so the user can selct the date in some charts, and use an expression like:  Sum({$<DateType={'$(vDateType)'}>} Amount)

[Quentinn]

Hi there,

Yes i understand the logica behind canonical calendar. However, the post uses An example with a 2 level hierarchy (orders have multiple orderlines), i have a case with 3 levels (sample have multiple subsamples and on its turn multiple slides).

Any insight on how to do this?

[rubenmarin]

Hi, if an slide belongs to a single subsample, and that subsaple belongs to a single sample, the 'DateBridge' table would relate with the slideID with each date, this Id will tell wich subsample and wich sample it's related.

Maybe a capture with the relationship model can help me to see the difference or the complications to apply the canonical.

[Quentinn]

[Quentinn]

This is a very simplified version of the model, however, i don't know how to apply canonical date to this relationship. It would be very benificial for me to be a able to build a canonical master calendar ( i have gotten this far) and also integrate the keys/ relationship into this, so i'm able to make selection

[Quentinn]

Does anyone have an answer/tips? It would help LOADS

[vikasmahajan]

Hello,

Refer Link Table solution in below link :

https://community.qlik.com/t5/QlikView-App-Dev/Advantages-of-Link-Table/td-p/573849

Thanks

Vikas