Trouble with Canonical Date

[arsallee3]

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

[Anonymous]

Can you Please Attach Sample Data.

[arsallee3]

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

[swuehl]

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]

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

[rubenmarin]

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

Canonical Date

[arsallee3]

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

[#8]

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.