concept of canonical calendar -case inside

[ioannaiogr]

Hi all.

I have a very specific issue which I have yet to solve. I have another open thread but I would like to make the problem more specific.

Table-wise speaking:

Facts_Applications:

ApplicationID, ApplicantID, DateIssued, DateCancelled etc ;

Dimension_Applicants:

ApplicantID, DateInserted;

Relationship between Facts and Dim on ApplicantID is 1-1.

1st question)

a) For a canonical calendar I need to do one master calendar for DateIssued, one for DateCancelled  and one for DateInserted, then a date bridge where dates are as CanonicalDate and finally a Canonical Calendar?

b) If yes though, having studied this by HIC https://community.qlik.com/t5/Design/Canonical-Date/ba-p/1463578 I see that both tables he works with are about orderID. For me, some dates are for Applications and I have one date for an applicant. This must be a problem right? How do I handle this?

Many thanks in advance. I believe someone can help me 🙂 

[ioannaiogr]

Hello @Kushal_Chawda I will try it. The Date in your link table is the canonicaldate field in my case right?

And I have got a fine grain date dimension table can i use this instead of your master calendar approach and link them through Date fields? 

Thank you in advance!

[ioannaiogr]

Hey @jwjackso i found out what's happening and i feel that maybe the solution is close!

To start with, my date bridge is with key applicationid not applicantid. With applicantid my results get messed up. BUT! I found out that there are applicantids in each academic year that don't have applicationids. Those don't get counted with only selection of academic year AND datetype=inserted or submitted or cancelled or any time of date i have. On the other hand if i do the selection manually at each date field >=01/09/20xx<=31/08/20xx+1 they get counted and that is the correct result.

How do i get it by the first selections?

Ps. I have done outer join on appicantid for fact and dimension table. 

cc @Kushal_Chawda 🙂 any help is appreciated 

[Kushal_Chawda]

@ioannaiogr  Yes. Date field in my link table is canonical date. You can use your existing master calendar to link with bridge table.