Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
ioannaiogr
Creator II
Creator II

concept of canonical calendar -case inside

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 🙂 

Labels (1)
12 Replies
ioannaiogr
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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.