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
Options for Row-Level Security in Tableau Now that you know you need Row-Level Security (RLS) and you are familiar with the basic concepts and building blocks, we can take a ...
Row-Level Security Basics in Tableau In this blog, we will run through fundamental concepts and building blocks for Row-Level Security (RLS) solutions. This will include: ...

See more from this author →

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

×

Love our blog? You should see our emails. Sign up for our newsletter!