

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you Please Attach Sample Data.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Added the data files, sorry thought it was included with qvf file.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I did forget to convert that in the sample data however in my actual I did convert to just dates.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Andy, this document explains how the create a canonical date:


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
