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 🙂 

[jwjackso]

You don't have to have the 3 master calendars.  Those depend on how you want to filter the data.  Look at the script below, this is how we used HIC's canonical calendar.

Dimension_Applicants:
load ApplicantID
,DateInserted
from Somewhere
;

Fact_Applications:
Load ApplicationID
,ApplicantID
,DateIssued
,DateCancelled
from Somewhere
;

DateBridge:
Load ApplicantID
,DateInserted as CanonicalDate
,'ApplicantDateInserted' as DateType
Resident Dimension_Applicants
;

Concatenate

Load ApplicantID
,DateIssued as CanonicalDate
,'ApplicationDateIssued' as DateType
Resident Fact_Applications
;

Concatenate

Load ApplicantID
,DateCancelled as CanonicalDate
,'ApplicationDateCancelled' as DateType
Resident Fact_Applications
;

Temp:
Load min(CanonicalDate) as minDate
,max(CanonicalDate) as maxDate
resident DateBridge
;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate)
;

MasterCalendar:
Load
TempDate as CanonicalDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
/* add date field as needed */
Resident TempCalendar
Order By TempDate ASC;

Drop Table TempCalendar;

[ioannaiogr]

@jwjackso hello! This works perfectly for my applicants, but messes up all my counts for applications 😥 It's like it only works for ApplicantDateInserted now and shows bizarre results for applications. Eg when I pick AcademicYear let's say 21-22 

my measure for applications for Date='Submitted' this year shows submissiondates  from all academic years (not only in 21-22), but ApplicantDateInserted shows correct results that belong in that academic year.

I'm going crazy with this one 😂 😥

[jwjackso]

Can you show an example of your measures?  For count of applications by date issued:  Count({<DateType={'ApplicationDateIssued'}>}ApplicationID)

[ioannaiogr]

Yes this but with distinct Count({<DateType={'ApplicationDateIssued'}>} distinct ApplicationID)

Shows about a 1000 more than it should, also the dates are not in the year they should be. For an academic year all date bridge dates should be in that year. For date bridge for applicantid as key, now i get dates from other years too. I pick datesubmitted in and year 21-22 and the date itself belongs to whatever year 😵😵😵

[jwjackso]

Are your dates a timestamp?  If they are, use 

Load ApplicantID

        ,Floor(DateIssued) as CanonicalDate

      ,'ApplicationDateIssued' as DateType

[ioannaiogr]

I have already turned them into date(floor(date)'DD/MM/YYYY').

How do you explain the fact that before having to  to handle the applicants in the date bridge my results for applications where correct and now not 😩 

In fact my date bridge before was 

Load ApplicationD
,DateIssued as CanonicalDate
,'ApplicationDateIssued' as DateType
Resident Fact_Applications
;

Concatenate

Load ApplicationID
,DateCancelled as CanonicalDate
,'ApplicationDateCancelled' as DateType
Resident Fact_Applications;

I tried joining with a left join, but date inserted didn't work then.

I thought i'm doing something wrong with the concept of date bridge/canonical date.

I don't get it.

[jwjackso]

Without knowing your data, it is hard to tell what is happening.  I added some dummy data

Dimension_Applicants:
load * inline [
ApplicantID,DateInserted
1,12/31/2020
2,01/01/2022
3,01/01/2023
4,01/02/2021
]
;

Fact_Applications:
Load * inline [
ApplicationID,ApplicantID,DateIssued,DateCancelled
100,1,01/01/2021,12/31/2024
200,2,01/02/2022,01/01/2023
300,3,01/02/2023,12/31/2024
400,4,01/02/2021,12/31/2024
]
;

The counts appear to work

[ioannaiogr]

Your example is with the above date bridge?

also: The relation between applicantid and applicationid is 1 to many in my data. Could this be it? 🤷🏼‍♀

In any case, I appreciate that you're trying to help, really! 

[Kushal_Chawda]

@ioannaiogr  see my approach here