4 Replies Latest reply on Aug 30, 2018 2:36 PM by Jamil Deen

    Tracking Late Check In's for 1st Scheduled Load of the Day

    Jamil Deen

      The data I am dealing with is loads that drivers have completed (multiple loads per day). The specific information I am looking at is the times they are scheduled and when they checked in. I created a column that determines if the check in time is >30 min after the scheduled date and time. What I am trying to do is to be able to separate that information even further, group the loads by day and driver, set a minimum value where it is able to determine the first load of the day, and if only the first load of the day checked in later than 30 mins.

       

      How should I go about doing this? I am not sure what functions and steps I would need to do in order to group the information the way I mentioned and focus only on the first load (minimum value for the day)? Any help is much appreciated!

        • 1. Re: Tracking Late Check In's for 1st Scheduled Load of the Day
          Joe Oppelt

          Do you have separate rows for each load for a driver?

           

          If so, you can have a FIXED LOD that sets either 1 or 0 if that load is late.  You probably have a unique Load ID, so you can do that calc at the level of Load ID.  That calc can be a dimension.  You can handle that calc like any other dimension, so you can put it on your sheet and filter for all the late ones, or sort your sheet by late loads at the top, etc.

          • 2. Re: Tracking Late Check In's for 1st Scheduled Load of the Day
            Jamil Deen

            Each load has a separate row and Load ID. I already made the column that determines if the load is late. What I am trying to do is not just see which loads are late, but only the first load of the day for each driver that is late if that makes sense.

            • 3. Re: Tracking Late Check In's for 1st Scheduled Load of the Day
              Joe Oppelt

              { FIXED [Driver], DATE([date-time field]) : MIN( if [late flag] = 1 then [date-time field] END) }

               

              I'm assuming there is a date-time field.  And that's how you tell the time of the load.  Maybe there is another date field where you just record the date and not the time...  If so, then you would use that before the colon in the FIXED LOD.

               

              Basically that calc is saying "For each driver, for each day, give me the min date-time of all the rows that are marked as late."  And that will give you the first late delivery for a given driver's day.  (It will give you the date-time of that delivery.  If you wanted the LoadID of that delivery you would take another step to say

               

              { FIXED [Driver], DATE([date-time field]) : MIN( IF [that date calc] = [date-time field] then [LoadID] END ) }

               

              If you just wanted to check if the first load of the day was late then

               

              {  FIXED [Driver] , DATE([date-time field]) : MIN( [Date-time field] ) }

               

              That gives you the first load.

               

              { FIXED [Driver], [DATE([date-time field]) : MAX(IF [date-time field] = [min-date calc] and [late flag] = 1 then 1 END ) }

               

              You'll get a value of 1 for that driver on that day if his first load was late.


              I typed all this off the top of my head, of course.  Some debugging may be necessary.