Working with multiple date fields

[nicouek]

Hello all, 

I have a problem I need some assistance with, Thank you

I have an [EMPLOYEE] table with [EFF_FROM_DATE] and [EFF_TO_DATE], currently to count [EMP_ID] successfully, I leverage a MasterCalendar and IntervalMatch(MASTER.CALENDAR_DT). I now have to add another table EMP_ACTION: to my model on [DATABASE_KEY]. 

EMP_ACTION table has [PROMOTION_CODE] and [EFFECTIVE_DATE] after associating the tables on [DATABASE_KEY], my requirement is to count the number of total employees and corresponding number of employees that have gotten promotions in the same visualization. My issue is, employee count uses IntervalMatch(CALENDAR_DT), and PROMO count uses [EFFECTIVE_DATE], individually each calculation returns the correct number of record, however together the number are off. Is there a solution to have one calendar return the correct counts for each aggregation?

[Vegar]

Have you seen this blog post by HIC? Canonical Date 

If you treat your   "CALENDAR_DT" and the "EFFECTIVE_DATE" as different datetypes in a date bridge you could be able to solve your issue.

[nicouek]

Hi, Vegar

I have been working on a canonical date through the weekend with  "Business intelligence with Qlik Sense" book; I  gave a try to the model described in the link you provided... and there is still no success.

I do really appreciate your input.

Sincerely,