3 Replies Latest reply on Aug 14, 2012 1:10 PM by Mark Holtz

    How do I identify and label records based on time?

    William Weaver

      I need to categorize a list of trip records based on the time difference between a trip's scheduled dropoff time and it's actual dropoff time.

       

      First, here are the categories:

       

      Early = Actual Dropoff Time is 10:01 minutes BEFORE the Scheduled Dropoff Time

       

      On Time = Actual Dropoff Time is less 10:01 minutes BEFORE the Scheduled Dropoff Time AND less LESS than 10:01 minutes AFTER the Scheduled Dropoff Time

       

      Late = Actual Dropoff Time is 10:01 minutes AFTER the Scheduled Dropoff Time

       

      Next, here is my very crude attempt at creating a calculated field to generate the desired results.

       

      IF [ActualEndDateTime - ScheduledDropoffDateTime] >= 00:10:01 THEN 'Late' ELSEIF [ActualEndDateTime - ScheduledDropoffDateTime] <= 00:10:00 AND <= -00:10:01 THEN 'On Time' ELSE 'Early' END.

       

      Obviously, I know the formula is very flawed but I wanted to do my best to show where my thinking was for solving this problem. Any helpful suggestions would be most appreciated.

       

      I have attached a packaged workbook of my efforts if anyone wants to give this challenge a try.

       

      Thanks - William

        • 1. Re: How do I identify and label records based on time?
          Mark Holtz

          Hi William,

           

          A calculated field with this logic should get you what you're after.

          10 minutes = 600 seconds.

           

          IF DATEDIFF('second',[ScheduledDropoffDateTime],[ActualEndDateTime]) <-600

          THEN 'Early'

          ELSEIF DATEDIFF('second',[ScheduledDropoffDateTime],[ActualEndDateTime]) <600

          THEN 'On Time'

          ELSE 'Late'

          END

           

          I went ahead and added a calculated field showing the result of the datediff function too in the attached workbook.

          • 2. Re: How do I identify and label records based on time?
            William Weaver

            Perfect! Thank you Mark. This is exactly what I was trying to accomplish. And my very crude formula looked nothing like the formula I actually needed.

             

            If I am not pushing my luck, could you tell me why I cannot re-sort the trip data by scheduled pickup time in both my original worksheet and your modified worksheet? I just wanted to show the trip times from earliest to latest.

             

            Thanks --- William

            • 3. Re: How do I identify and label records based on time?
              Mark Holtz

              Glad it worked.

               

              Regarding sorting the table again, Tableau sorts by order of the dimensions (blue "pills") on your shelf.

              So if you are trying to sort by a dimension not in the "1st position", you would have to move its position on your shelf.

               

              If you want the sorted data to "appear" in a column other than the first one in your table, you could create a duplicate of the dimension and then place the COPY on the (Rows) shelf in position 1 and sort it in ascending order.  Finally, to prevent the data from being displayed twice, you uncheck the "Show Header" from the menu for that pill.

               

              dup and re-sort.png