topic Working with multiple date fields in App Development

Working with multiple date fields

nicouek — Mon, 29 Jan 2024 16:05:41 GMT

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?

Re: Working with multiple date fields

Vegar — Mon, 29 Jan 2024 16:31:46 GMT

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.

Re: Working with multiple date fields

nicouek — Mon, 29 Jan 2024 18:32:42 GMT

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,