3 Replies Latest reply on Feb 20, 2018 3:50 PM by Manuel Velasquez

    Find Diff between dates and hours in one column with conditions from another column

    ramon.rodriguez.0

      I am struggling with Tableau regarding how to group multiple column values with conditions and find the diff between the days and hours:

      Here is how my data looks like:

       

      id trigger timestamp
      1  started  2017-10-01 14:00:1
      1  ended  2017-10-04 12:00:1
      2  started  2017-10-02 10:00:1
      1  started  2017-10-03 11:00:1
      2  ended  2017-10-04 12:00:1  
      2  started  2017-10-05 15:00:1
      1  ended  2017-10-05 16:00:1
      2  ended  2017-10-05 17:00:1

       

      My goal is to find the difference in day/hour or minutes between the dates grouped by the id.

      My output should look more like this (diff in hrs):

      id trigger  timestamp           trigger      timestamp           diff
      1  started  2017-10-01 14:00:1  ended       2017-10-04 12:00:1   70
      1  started  2017-10-03 11:00:1  ended       2017-10-05 16:00:1   53
      2  started  2017-10-02 10:00:1  ended       2017-10-04 12:00:1   26
      2  started  2017-10-05 15:00:1  ended       2017-10-05 17:00:1   2

       

      I am new to tableau, i heard this could be dome with LOD expressions.I have tried with the following LOD expression, but this is not doing the job:

       

      DATEDIFF('minute',{FIXED [id]: min((IF [trigger] = 'started' THEN [timestamp] END))},{FIXED [id]: max((IF [trigger] = 'ended' THEN [timestamp] END))})

       

       

      Can someone put me on the right track how to achieve this.

       

      Thanks in advanced

        • 1. Re: Find Diff between dates and hours in one column with conditions from another column
          Tushar  More

          Hi Ramon,

           

          With your existing data structure, it looks a bit difficult to get the desired output. But, if you could re-shape your data then it can easily be done.

          I restructured your data.

           

          ~Tushar

          • 2. Re: Find Diff between dates and hours in one column with conditions from another column
            ramon.rodriguez.0

            Hi Tushar,

            Thanks for your reply.

            Unfortunately it is not possible to re-shape the data. I need to find a solution for this calculation as it is.

             

            Ramon

            • 3. Re: Find Diff between dates and hours in one column with conditions from another column
              Manuel Velasquez

              Hi Ramon

               

              You can re-shape your data set within Tableau so it will be easy to calculate the difference in hours.

               

              If you are using a .csv or excel file we could play with the data source filters, calculations, and a Join to prepare the data set

               

              In this case, I'm using a .csv with similar data structure that you provided

               

              1.PNG

              After connecting that file to Tableau I will add a data source filter to keep all the "started" trigger rows

               

              2.PNG

               

              Then we create an Inner JOIN with the same file (drag an drop another instance of the same file to the right of the previous one)

              The Join condition is on Id

              I will rename the trigger field to "Trigger-Started" and "Trigger-Ended" 

               

              3.PNG

               

               

              I will create a calculated field to use it as a filter, the idea here is to eliminate the duplicate rows that this Join condition returned.

               

              This will be a boolean value, we want to keep all the rows that "Trigger-Started" is different to  "Trigger-Ended" .

              4.PNG

               

              Add the new calculated field to the data source filter, keep the True values

               

              5.PNG

               

              Rename Timestamp fields to "Timestamp-Started" and "Timestamp-Ended" respectively

               

              Now it is easy to get the difference in hours

              6.PNG

               

               

              NOTE: If you are connecting to a Database we could create some Custom SQL  to prepare the dataset, of course, that would be different steps.

               

              Attached .twbx file for reference

               

              Let me know if this helps!

               

              Manuel Velasquez