5 Replies Latest reply on Aug 9, 2017 8:02 AM by Shinichiro Murakami

    Location of vehicle on specific date

    Claudia Setubal

      Hi folks. I'm fairly new to Tableau and trying to figure out a problem.

       

      I have a dataset that gives me dates of status change every time a vehicle is moved, which will sometimes be multiple times per day, and sometimes not for a week at a time, like so:

       

       

      Vehicle IDLocationStatusDateTime
      1234A7/1/2017 9:17am
      1234B7/1/2017 12:00pm
      1235B7/1/2017 3:35pm
      1236A7/1/2017 4:45pm
      1234B7/8/17 8:43 AM
      1235A7/10/2017 9:00am
      1236B7/12/2017 7:52pm

       

      I need to create a table that gives me a total count of vehicles in each location at the end of each day. This involves finding the max location by day. The ideal result would look like this:

       

       

      DateAB
      7/1/201721
      7/2/201721
      7/3/201721
      7/4/201721
      7/5/201721
      7/6/201721
      7/7/201721
      7/8/201712
      7/9/201712
      7/10/201721
      7/11/201721
      7/12/201712

       

      I've figured out how to do a Max LOD fixed on the vehicle ID, but I can't figure out how to pin that to a specific date, particularly dates that do not already appear in my data set. Any advice would be greatly appreciated. Thanks!