7 Replies Latest reply: Jul 27, 2017 9:06 PM by Srikanth P RSS

    Trouble with Canonical Date

    ANDY SALLEE

      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

        • Re: Trouble with Canonical Date
          Mouna Chandra Konidhina

          Can you Please Attach Sample Data.

          • Re: Trouble with Canonical Date
            Ruben Marin

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

            Canonical Date

              • Re: Trouble with Canonical Date
                ANDY SALLEE

                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