4 Replies Latest reply on Oct 6, 2016 7:31 PM by Ivan Young

    Time Diff between two rows

    Christopher McKinnish

      I'm trying to calculate the hours and standard minutes between two rows.  I cannot understand for the life of me what Tableau is doing here.

       

      1. Line 2 showing a diff of 3:66 is 366 minutes (the colon was done with custom format).  Ok...366 minutes is the correct answer, but I would like to show this as Hours and Minutes.  The resulting calculation shows this to be a number.  The Convert syntax does not convert correctly.
      2. The last line: shows a value of 15:37.  The actual result is 25 hours and 37 minutes.  Why is this returning 15:37

       

      Can anyone see what I obvious appear to be missing?

       

        • 1. Re: Time Diff between two rows
          Wesley Magee

          Christopher,

          I think with a couple small changes you can get what you're looking for.

           

          1. First, change your calc to show seconds instead of minutes. Second, you'll need to convert the seconds into proper time (hh:mm:ss), which you can find in this article Show Seconds as hh:mm:ss

           

          2. The last line is showing 15:37 because 25 hours and 37 minutes converts to 1,537 minutes. Your calculation is showing everything in minutes.

           

          Let me know if you have any other questions or run into problems.

          -Wesley

          • 2. Re: Time Diff between two rows
            Shinichiro Murakami

            Datetime calc is tricky.

             

            See below table and attached workbook.

             

            To convert minutes to Date format,

            Need to convert Days grain. (minutes x 60 x 24)

            Then add some date.

             

             

            [Difference (Minutes)]

            float(datediff('minute',lookup(min([Dispatch time]),-1),lookup(min([Dispatch time]),0)))

             

            [Difference (Days)]

            (datediff('minute',lookup(min([Dispatch time]),-1),lookup(min([Dispatch time]),0)))/(60*24)

             

            [Date format]

            date("2016/1/1")+(datediff('minute',lookup(min([Dispatch time]),-1),lookup(min([Dispatch time]),0)))/(60*24)

            [HHMM format]  //  same as Date Format

            date("2016/1/1")+(datediff('minute',lookup(min([Dispatch time]),-1),lookup(min([Dispatch time]),0)))/(60*24)

             

             

            Thanks,

            Shin

            • 3. Re: Time Diff between two rows
              Christopher McKinnish

              Thank you!!!   That got me much closer.    Now notice the last line below, the result should be 25:37. 

               

               

               

               

              If i set the custom format to D hh:mm:SS  I'm running into an epoch date issue

               

               

              Any suggestions to this?

               

              Again...thank you...those were simple and quick solutions to get me this close!!

              • 4. Re: Time Diff between two rows
                Ivan Young

                Hi Christopher,

                I don't think the hour will go above 23 using that custom format.  The only way I know of to get the format you want is to create a calculated text or numeric field.  You can't really perform any aggregation on this field it is just for display.  Below are the steps to create it.

                 

                1.  Create date diff by hour DATEDIFF('hour',LOOKUP(MIN([Timestamp]),-1), LOOKUP(MIN([Timestamp]),0))

                 

                2.  Create date diff by minute stg: LOOKUP(DATEPART('minute',MIN([Timestamp])),-1)-LOOKUP(DATEPART('minute',MIN([Timestamp])),0) - This won't return the correct values

                 

                3.  Create date diff by minute: IF [Date Diff Minutes Stg] <= 0 THEN -1*[Date Diff Minutes Stg] ELSE 60-[Date Diff Minutes Stg] END

                 

                4. Create date diff hours and minutes: [Date Diff Hour]*100+[Date Diff Minutes]

                 

                5.  Set custom number format for date diff hours and minutes ##:##

                 

                6.  Set compute settings to Timestamp.

                 

                Let me know if you have any questions.

                 

                Regards,

                Ivan

                 

                1 of 1 people found this helpful