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 basic approach is simple and is made up of two parts:
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.
That’s the basic approach. Download the workbook above and take a look to see the details!