Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

arsallee3
New Contributor III

Trouble with Canonical Date

Good day Qlik Community!

Having trouble with what I think should be Canonical Date Calendar. Not able to get both selections without selecting both dates individually.

They are 2 different sources and wish to compare Actual to scheduled by employee, and have Single date selection.Calendar choice.

Actual Hours

EmpID, InDateTime, OutDateTime, TotalHours

Schedule Hours

EmpId, ScheduledInDate, ScheduledOutDate, ScheduledHours

Would like end result to be achieved with single date selection such as yesterday's date or last Monday's date.

EmpId     Scheduled     Actual     Difference

1               8                    10               2

2               0                      4               4

3               10                    8               -2

4               8                      0               -8

Additionally would still like to select tomorrows date and see who is scheduled.

Maybe I am overthinking this?

Sorry thought data came with the qvf file. Message was edited by: ANDY SALLEE

7 Replies
mounachandra1
Contributor

Re: Trouble with Canonical Date

Can you Please Attach Sample Data.

arsallee3
New Contributor III

Re: Trouble with Canonical Date

Added the data files, sorry thought it was included with qvf file.

MVP
MVP

Re: Trouble with Canonical Date

Without opening your QVF, it seems like your looking at timestamps for actual records and dates for scheduled records. Take care to  e.g. floor the the timestamps, so that actual and current values do match.

arsallee3
New Contributor III

Re: Trouble with Canonical Date

I did forget to convert that in the sample data however in my actual I did convert to just dates.

Re: Trouble with Canonical Date

Hi Andy, this document explains how the create a canonical date:

Canonical Date

arsallee3
New Contributor III

Re: Trouble with Canonical Date

Yes that is a great document and I have not been able to get the correct results.

I think the problem is I need to match an ID and a date both across both tables. Working to create another field that is a combination of ID&Date and then use that as the data link point. I believe this will achieve the result I am looking for.

I probably did not lay out example well enough.

Scheduled table

EmpId     Scheduled     Date

1               8                    07/25/2017 

1               8                    07/26/2017

1               8                    07/27/2017

2               0                    07/25/2017      

2               10                  07/26/2017           

3               8                    07/25/2017


Actual Worked Table

EmpId     Actual     Date

1               7     07/25/2017

1               9     07/26/2017

2               8     07/25/2017

3               9     07/25/2017

Results desired If no date selection chosen

EmpId     Scheduled     Actual     Difference

1               24                   16              -8

2               10                    8               -2

3               8                      9               1

Results desired if date of 7/25 is selected

EmpId     Scheduled     Actual     Difference

1               8                    7               -1

2               0                    8               8

3               8                    9               1

Results desired if date of 7/26 is selected (ID 3 would not be shown because zero for all values on this date range.)

EmpId     Scheduled     Actual     Difference

1               8                      9              1

2               10                    0               -10

Not applicable

Re: Trouble with Canonical Date

Andy, Simply concatenate your two facts into one single Fact table and use data source flags to calculate correctly.

Association key EmpID&Date should work as well. Or you can join directly on these fields if you have common dimensions.

Community Browser