7 Replies Latest reply on Aug 14, 2018 8:12 AM by Jim Dehner

# Tricky Calculation in Tableau

I have a requirement in tableau and it is as follows:

The purpose of the dashboard is to look for illogical cab rides:

Rides where, drop off(meter off event where the driver turns the meter off after completing the trip) in a specific area say NYC is doing another ride which is > (greater than) 7 miles away from the drop off location of the previous ride within a 0 to 30 minutes time frame for the same user_id.

• ###### 1. Re: Tricky Calculation in Tableau

Good morning I assume you want to know how to calculate the distance between points - the time difference is straight forward

the formula for distance between lon/lat points is (note those are not street miles - they are direct point to point)

`3959 * ACOS `

`( SIN(RADIANS([Lat])) * SIN(RADIANS([Lat2])) + COS(RADIANS([Lat])) * COS(RADIANS([Lat2])) * COS(RADIANS([Long2]) - RADIANS([Long])) )`

see the link for how to use the formula  Calculate the Distance Between Points on a Map | Tableau Software

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 2. Re: Tricky Calculation in Tableau

I have different levels at which I want to partition the data.
Userid is the first partition inside which there will be pickup time say a few rides are being booked within the time frame of 30 minutes difference from each other they should be grouped together.
Now I want to take the timeoff lat long from the previous ride and find the distance to the next pickup point using the meter on of the next ride which is in the next row.

• ###### 3. Re: Tricky Calculation in Tableau

you will need to post your twbx workbook with the data attached

Thanks

JIm

• ###### 4. Re: Tricky Calculation in Tableau

Here's the workbook

• ###### 5. Re: Tricky Calculation in Tableau

here is an approach

the time diff is

the distance diff is

you have lots of nulls in the data set that you will need to deal with somehow

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 6. Re: Tricky Calculation in Tableau

I'm trying to fix these calculations at the level of detail rider_phone, Date(Pickup_time).
But getting an error : Any suggestions ?

• ###### 7. Re: Tricky Calculation in Tableau

you will not be able to use LOD's the calculations are using a table calculation (lookup) to get the values from one record and use it in another

if you think you really need to use lod's you will need to completely restructure the data

and you should have mentioned that in the first place

if you don't share all the details on a question we can''t be very specific with an answer

JIm