Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 🙂
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!
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
@ioannaiogr Yes. Date field in my link table is canonical date. You can use your existing master calendar to link with bridge table.