3 Replies Latest reply on Oct 15, 2018 9:13 AM by Don Wise

    Calculate Date diff based on previous datetime (first moved)

    AJ.Yusuf

      Hi All ,

       

      I would like to add a "Exit Time" Coloumn , this basically the next first moved time for the paticular VehicleID, I have done it in excel to give you an exam. I tired using the lookup but it wont let me do a LOD with a Lookup. The purpose of this is to get an average time for the paticular descriptions. regardless of Vehicle ID.

       

       

       

       

       

      Excel Example : Exit time is basically the "First moved time of the next movement for the paticular id.  Also the last  movment is zero as there is no movement more to get a end time. Objective would be to caluclate the average time for each description

       

      Any ideas

       

       

       

       

      Regards

        • 1. Re: Calculate Date diff based on previous datetime (first moved)
          Don Wise

          Hello,

          Please see attached workbook (2018.2) and below screenshots which gets you what you're looking for:

           

          This calculation computes the Exit calculation, a LOOKUP function which looks not at row 0 or the row preceding but the next row:

          Screen Shot 2018-10-12 at 10.10.35 AM.png

          This calculation computes the number of seconds between First Moved and the new Exit calculation; and if it is the last row in the partition to make it '0'.

          Screen Shot 2018-10-12 at 10.10.20 AM.png

          This is a copy of Jonathan Drummey's calculation uses the SUM of Exit Seconds to determine total duration or difference between First Moved and Exit. Because you have 'days' in between movement, this calc is needed and a simple DateDiff will not suffice nor format correctly.

          Screen Shot 2018-10-12 at 10.10.59 AM.png

          This is the related Table Calculation needed for Exit Seconds to set the order of displaying the results correctly.

          Screen Shot 2018-10-12 at 10.11.10 AM.png

          This is the related Table Calculation needed for the Exit Calculation to display the last time value as Null:

          Screen Shot 2018-10-12 at 10.11.22 AM.png

          If this answers your question, please mark this response as correct. Hope it helps! Thx, Don

          • 2. Re: Calculate Date diff based on previous datetime (first moved)
            AJ.Yusuf

            Thanks Don WISE ,

             

            This is really good and the numbers correct. How do I talk these numbers and and get a average for the description removing first moved, vehicle id from the rows section and just having Description and Average Differecne. To get a average time at those descriptions, Is this possible in Tableau?. .... Like a average if function just for those description

            • 3. Re: Calculate Date diff based on previous datetime (first moved)
              Don Wise

              Hello A.J.,

               

              The downside of being able to get you correct numbers is that I'm using Table Calculations to specifically get them; which in turn, doesn't allow Tableau to further aggregate (use an Average) on a calculation that's already aggregated. 

               

              The issue is the [Exit] time/date calc that you needed. That's a table calculation using the LOOKUP function; so thereafter, any type of further aggregation efforts will error out.  If your [Last Moved] timestamp was actually the correct data (date/timestamp), instead of creating it using the [Exit] calculation to mimic it, then we could potentially get you to an average using a slightly similar methodology but involving an AVG LOD calc. 

               

              In looking at your [Last Moved] data, I completely understand why you needed a new [Exit] timestamp...If you can get a column of data which is better for [Last Moved], then I can go forward...Thx, Don