A common technique for calculating distances in Tableau is to use a custom SQL statement to join a dataset to itself. This causes each data row to contain two latitude and longitude values. Then a row-level calculation can use a formula (such as Haversine) to determine the distance. However, many data source connections such as Salesforce and Google Analytics don’t support custom SQL. Furthermore, cross joining large datasets can be quite costly and, even if possible, may not be practical.
Here is an alternative: use a table calculation to calculate the distance using latitude and longitude from different records!
The Alternate Solution
The basic approach is simple and is made up of two parts:
- Create a row level calculated field to set the order in which the geographic points should be evaluated. In the example dashboards above, the origin is set to 1 and all other points are set to 2.
- Use the LOOKUP function to determine latitude and longitude for the origin to compute the distance to every other location.
The first part is relatively easy. In the example dashboards above, a parameter is used to allow the user to select the origin (city or customer). For each record, if the field value matches the parameter value, then the result is 1. Otherwise it is 2. This calculation will serve as the ordering of the table calculation in the second part.
The second part is a little trickier. The initial row-level distance calculation, assuming that the latitude and longitude of both points are contained in the same record, looks like this:
|3959 * ACOS
SIN(RADIANS(Lat)) * SIN(RADIANS(Lat2)) +
COS(RADIANS(Lat)) * COS(RADIANS(Lat2)) *
COS(RADIANS(Lon2) – RADIANS(Long))
Using a couple of table calculations, the calculation becomes:
|3959 * ACOS
SIN(RADIANS(LOOKUP(AVG(Lat), First()))) * SIN(RADIANS(AVG(Lat))) +
COS(RADIANS(LOOKUP(AVG(Lat), First()))) * COS(RADIANS(AVG(Lat))) *
COS(RADIANS(AVG(Lon)) – RADIANS(LOOKUP(AVG(Lon), First())))
You’ll notice that every reference to Lat from the original formula was replaced with LOOKUP(AVG(Lat), First()). The same is true of the original Lon. The lookup of the first values is the origin’s latitude and longitude. The other latitude and longitude references are for the destination. Since this is a table calculation all references are now aggregates: e.g. AVG(Lat).
The last step to a working solution, is to make sure the table calculation is addressed and ordered correctly.
- Right click the field in the view and select “Edit Table Calculation…”
- Select the “Advanced…” option
- Make sure to Address by the fields that determine an individual geographic location (e.g. City and State, or Customer)
- Sort the calculation using the first calculated field that set 1 for the origin and 2 for everything else.
That’s the basic approach. Download the workbook above and take a look to see the details!