Going the Distance in Tableau: Becoming Fully Dynamic with Table Calculations

Data

Going the Distance in Tableau: Becoming Fully Dynamic with Table Calculations

In this series, we will explore different types of calculations involving calculating distance. From a simple iteration to geographic clustering, we will take your data as far as it can go with Tableau.

Let me start off here by laying out the pros and cons of using table calculations when it comes to measuring distance.

  • Pros: Table calcs do all of the work instead of us having to type out every latitude and longitude coordinate in a calculated field.
  • Cons: Table calculations depend on the structure of the temporary table that backs up your viz, so they are very particular about how they are computed.
  • Table Calculation Tip: If you are not seeing the results you anticipate when you are working with a viz, duplicate it as a crosstab to see how Tableau is interpreting the structure of the temporary table. The order of pills in your marks card and the dimensions by which the table calculation are computed can impact how the calculation is being computed.

Here is our basic plan: We are going to be comparing a list of cities to a selected city. This selected city will be presented in the first row of our temporary table, and the distance of every other city will be computed against the latitude and longitude of the city in the first row of temporary table (which, remember, is the city we selected).

Creating the Dynamic Parameter in Tableau

We are first going to create a parameter containing all of the cities in our dataset. We can do this pretty easily by just adding a list of values from a City, State field that we have created; this City, State field ensures that we have unique geographic locations:

edit dynamic parameter in Tableau

Note: We can also use the All option to select all cities in our dataset. This allows us to dynamically update our parameter field with a Data-Driven Parameter extension in our dashboard. This will come in handy if you are adding new locations to the original data source.

To ensure that the city selected from our parameter is actually in the first row of the table, we are going to create a calculated field to indicate whether a city is the selected and use this to sort our cities. That will look something like the calculation below. When the time comes, we will use the MIN aggregation—it is the fastest aggregation in Tableau—and sort ascending, so our city indicated with a 1 is placed above all others that have a 2 assigned to them:

calculated field for distance in Tableau

Building the Distance Calculation

Now we can build out our distance calculation, similar to the previous examples. The difference here is that instead of having the longitude and latitude coming from parameter values, it is now coming from the city in the first row of our data via a LOOKUP function; this is what turns this calculated field into a table calculation:

calculated field for distance in Tableau

To copy and paste:

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([long])) - RADIANS(LOOKUP(AVG([long]), First())))

)

3959 is half of the Earth’s diameter in miles. If you are using kilometers, this needs to be 6371 km.

We’re almost there. We can add in our distance calculation to any view and compute it using City, State with a Custom Sort order of MIN(City indicator), Ascending. If this is not working the way we intend, we can duplicate this view as a crosstab to get an understanding of how the table calculation is running against the temporary table:

distance calculation in Tableau

Beyond Distance Calculations: Two-Tier Sorting

If we want a crosstab view with our cities being sorted by our selected city and then all other cities with their distance from the selected city, we need to create a field incorporating both of these variables, which we can then use to sort.

We cannot simultaneously sort a dimension based on two measures, or multiple dimensions based on multiple measures, but we can create a calculation combining these into one field. We can simply bring this field to rows in front of City, State, and it will sort by the city we have chosen and then the distance. This field can also be hidden by right-clicking the pill and unchecking Show Header for a clean look:

two-tier sorting in Tableau

Stay tuned for the next post in this series where we discuss taking these calculations to the next level with Tableau’s latest game-changer, set actions.

More About the Author

Grant Eisenmenger

Analytics Consultant
Going the Distance in Tableau: Clustering Based on Distance The final example of using distance calculations in Tableau looks at clustering cities based on their relative distance to a selected ...
Going the Distance in Tableau: Dynamically Comparing Different Distribution Centers In this next scenario, we are going to look at which distribution center is closest to a selected city. This is highly similar to the ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072