Going the Distance in Tableau: Clustering Based on Distance

Data

Going the Distance in Tableau: Clustering Based on Distance

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.

The final example of using distance calculations in Tableau looks at clustering cities based on their relative distance to a selected set of points. This is based on the table calculation method we looked at in the previous two sections, with the formula I called Distance (Table Calc). Effectively, we will have three total copies of this formula, three copies of the calculated field that indicated the city we selected and three copies of the parameter that tied in our selection of a city to that calculated field.

Here is our distance calculation 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.

Our city indicator calculation:

If [City, State]=[City parameter] then 1 else 2 end

And how we created our parameter:

creating a parameter for city, state in Tableau

When we have three copies of these items, we can compare the distances using one calculated field. We are doing this with three cities; just realize that if you are wanting to do this with more locations, you are going to have a progressively longer calculation to write:

comparing distances in Tableau with a calculated field

Here, we are effectively saying that if the distance attributed to our first city selection is the smallest when compared to the other two, return that city. Then, we reiterate this for our second city selection and pick up the remainders for our third city.

Setting up a Nested Table Calculation

I placed this field on color. To have it run effectively, we need to set up how this table calculation runs as well. This calculation is a nested table calculation, meaning that it references multiple other table calculations. Within the table calculation editing window, we can choose how we would like to run each of these calculations that run within the Nearest Selected City field; we need them to run in the same way these table calculations have run before: on the Specific Dimension of our City, State field, sorted by the minimum of the indicator field, so they are comparing to the chosen city.

Now, when we select three different cities, we should see three colors in our view, showing us which cities are closest to our selections. If this is not working for you, double-check that you have three separate copies of the distance formula and indicator field in your Data pane, three city parameters shown with different cities selected and that the nested table calculations are running in the desired way:

nested table calculations in Tableau

Looking Back at the Distance We’ve Traveled

While Tableau does not allow us to run calculations off of the generated latitude and longitude fields, we can export them from a map view to a CSV file, Excel or Microsoft Access and use a join to bring in the usable data. From there, we can use a distance formula to calculate the distance between the following:

  1. A fixed point and all others, using a calculated field
  2. A point selected from a small parameter and all others, using a calculated field
  3. A point selected from an expansive parameter and all others, using a table calculation
  4. A point selected on the map and all others, using a set action and table calculation
  5. A point selected from an expansive parameter and a list of a few points, using a table calculation
  6. Multiple points selected from multiple expansive parameters, which can be used for distance clustering, using a nested table calculation

I hope you enjoyed the trip!

More About the Author

Grant Eisenmenger

Analytics Consultant
Driving Set Actions with a Fake Filter in Tableau Question: How do I get a filter to also drive a set action in Tableau? Short answer: You can’t. Slightly longer answer: You can if you ...
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 ...

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