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

# Location of vehicle on specific date

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 ID Location StatusDateTime 1234 A 7/1/2017 9:17am 1234 B 7/1/2017 12:00pm 1235 B 7/1/2017 3:35pm 1236 A 7/1/2017 4:45pm 1234 B 7/8/17 8:43 AM 1235 A 7/10/2017 9:00am 1236 B 7/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:

 Date A B 7/1/2017 2 1 7/2/2017 2 1 7/3/2017 2 1 7/4/2017 2 1 7/5/2017 2 1 7/6/2017 2 1 7/7/2017 2 1 7/8/2017 1 2 7/9/2017 1 2 7/10/2017 2 1 7/11/2017 2 1 7/12/2017 1 2

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!

• ###### 1. Re: Location of vehicle on specific date

HI Claudia

First of all, Could you tell me your logic to calculate bottom table?

I understand 7/1 number if A =1 and B =2, but cannot understand why A=2 and B=1...

Thanks,

Shin

• ###### 2. Re: Location of vehicle on specific date

Hi Shin,

Thanks for your response! You're right, I had a typo in there. The sample

data should actually look like this:

 Vehicle ID Location StatusDateTime 1234 B 7/1/2017 9:17am 1234 A 7/1/2017 12:00pm 1235 B 7/1/2017 3:35pm 1236 A 7/1/2017 4:45pm 1234 B 7/8/17 8:43 AM 1235 A 7/10/2017 9:00am 1236 B 7/12/2017 7:52pm

Leading to the counts in the bottom table. Thanks for catching that!

On Tue, Aug 8, 2017 at 10:38 AM, Shinichiro Murakami <

• ###### 3. Re: Location of vehicle on specific date

Claudia,

Sorry for late reply, and one more question.

Which version of Tableau you are using?

Shin

• ###### 4. Re: Location of vehicle on specific date

I'm using 10.3.

On Tue, Aug 8, 2017 at 7:39 PM, Shinichiro Murakami <

• ###### 5. Re: Location of vehicle on specific date

Hi Claudia,

Looks pretty challenging..

You are using 10.3, then join itself is relatively easy.

Specify the day end location by vehicle.

Then, Count "A" and "B" separately.

Because "Location" directly impact to table calc, I could not find a way to set Location as dimension.

Same table calc needs to be set for "B"

Then filter the view with index..

Conclusion :

Table calc is VERY complicated, becaue of missing value..

If you could create this table as data set, the approach can be straight forward.

Thanks,