How to use the DISTANCE function in Tableau | #WorkoutWednesday | 2019 | 43

2019 has been the year of the map at Tableau. They’ve been working hard to close the gap in doing quick visual analytics on maps. In the last four releases they added:

  • Vector maps
    • Having Mapbox maps available out of the box is really slick
  • MAKEPOINT() function
    • This allows the user to essentially concatenate latitude and longitude points, from your data source, into a geographic role
  • MAKELINE() function
    • This allows users to draw a line between two geographic points
  • DISTANCE() function
    • And most recently, the distance calculation will allow users to calculation the distance any two points

These new capabilities are very exciting for Tableau users because they more easily allow users to more fluidly analyze geographical elements in their flow of analysis.

Lorna Eden’s week 43 #WorkoutWednesday challenge is a great introduction to several of these new features. Let’s check it out and learn

_WorkoutWednesday2019_Week_43_-_Lorna_Eden___Tableau_Public.png

Cool! This will be fun because it’s getting us in the mood for #Data19 which is just a few weeks away at the time of this post. Let’s take a look at the requirements

Week_43__Show_me_all_the_casinos_within_X_miles_of_X_casino_by_metres_–_Workout_Wednesday.png

Ok, simple enough. The first thing we need to do is get familiar with the data. Here is a sample

Tableau_-_Places_of_interest_within_a_user_selected_radius.png

So first things first is users will select a casino and then Tableau will show all casinos within a user-selected radius in miles.

Let’s tackle the first part. Allowing users to select a casino. Immediately, I think of a parameter when I hear that kind of requirement. And we just learned about parameter actions recently. So we should be able to quickly set up a parameter and parameter action to allow for users to click and change a selection

Fullscreen_10_23_19__12_34_PM.png

I’m defaulting my casino of choice to Mandalay Bay because that’s where #Data19 will be held so it makes the most sense. Before we set up our map, let’s start with a simple crosstab so that we can see everything

Fullscreen_10_23_19__12_40_PM.png

Now let’s set up a parameter check

## Casino Check ##
[Casino] = [Name]

Let’s put this as the highest level on rows and change the sort so that “TRUE” is on top

Fullscreen_10_23_19__12_44_PM.png

Now in order to use the DISTANCE() function, the syntax is

DISTANCE( start, end, units)

The start and end inputs must be geographic points which means…we need to…MAKE them. See what I did there? 🙂

So what we’re going to do is we need to MAKE two new point fields; one for our selected parameter casino and one for every casino in our dataset.

## Each Casino Point ##
MAKEPOINT( [Latitude] , [Longitude] )

That’s pretty straightforward, right. take these two points per row and concatenate them into a new geographic field. Now, these field types do not get materialized in the traditional sense. They can only be placed on the detail shelf of the marks card, so you’ll just have to trust that Tableau knows what it’s doing. Now for the selected casino points, we need to have their coordinates on each row so that Tableau can do the row level distance calculation. The easiest way to have values duplicated for each row is to use a level of detail expression. And only return the appropriate value for the selected casino

## Parameter Casino Point ##
MAKEPOINT(
{ MIN( IF [Casino Check] THEN [Latitude] END ) }
, { MIN( IF [Casino Check] THEN [Longitude] END ) } )

Now that we have our two points we’re ready to calculate our distance in miles. It looks like Lorna wants to filter by miles and color by meters (or metres depending on your preferred spelling)

## Distance from Parameter Casino_miles ##
DISTANCE( [Parameter Casino Point], [Each Casino Point], ‘miles‘ )

Let’s put that out there

Fullscreen_10_23_19__1_27_PM.png

Now let’s duplicate our distance field and replace our units

Fullscreen_10_23_19__1_29_PM.png

Looking good. Let’s create the ability to filter by radius in miles. I set up an simple integer parameter with a default value of 2

Edit_Parameter__How_many_miles___and_Tableau_-_Places_of_interest_within_a_user_selected_radius.png

The filter calculation is pretty straightforward from here

## Distance Filter ##
[Distance from Parameter Casino_miles] <= [How many miles?]

Put that on the filter shelf set to “TRUE” and you’ll see several rows filter out.

Now that we’ve got our foundation, we’re ready to start putting together our map.

Double click on the Lat/Lon fields from the data source, not generated ones. Then [Name] goes on the detail shelf of the marks card. Then apply your [Distance Filter] field. I also applied formatting to the map layers. You can get to this menu by navigating to “Map” in your menu bar and selecting “Map Layers…”

Fullscreen_10_23_19__1_43_PM.png

Cool, so if you look at Lorna’s version, she’s got a custom shape for the selected casino. And circles for the rest…colored circles…based on a continuous measure. So we’re mixing shapes with a separately colored circles. Don’t fret, it just means we’re making a dual axis map.

Let’s deal with the custom shape piece first. I’m not going to go out and get a custom shape for this challenge. Instead I’ll opt for a filled star icon to denote our selected casino

Tableau_-_Places_of_interest_within_a_user_selected_radius.png

I can see what some of may be thinking, well since the others are circles can you simply put your [Distance from Parameter Casino_meters] measure on color and call it good? Unfortunately, no because by doing that it will color all the shapes including our star which we want to make a different distinct color. But…what if we hide the marks that are not the star?

Fullscreen_10_23_19__3_28_PM.png

The “Hide” feature is one of those features that not many people know about but (or remember) but can get you out of quite a few jams when you need it

Tableau_-_Places_of_interest_within_a_user_selected_radius.png

Well, that’s not exactly what I had in mind but it’s working as designed. Now let’s drag Latitude out on rows to duplicate this axis. Now we can remove the [Casino Check] field from the mark and we’re back to circles. But in doing so, we make our selected casino a circle as well. Drat! That’s not what we want. We’re gonna have to figure out a way to fake removing that selected mark. The way I approached this was to use the size shelf. And I need to duplicate the [Casino Check] field so that I again utilize both TRUE and FALSE rows. I’ll put the newly duplicate field on the size shelf and then edit the sizing appropriately, making sure that I set the TRUE size to as small as Tableau allows. It’s still slightly visible but we’re just going to have to deal with it. We can also now add our measure to color and reverse the scale. Then make this a dual axis and…

Fullscreen_10_23_19__3_44_PM.png

Voila!

We’ve only got two more elements to work out 1) the title and 2) the tooltip.

For the title, we need to aggregate the number of casinos within the radius but not count the selected casino. It should be fairly straightforward since each record is a casino, should just be able to use [Number of Records] and simply subtract 1. But in order to use [Number of Records] you must pre-aggregate it somehow first. I chose to go the LoD route

## Casinos within radius ##
{ SUM( [Number of Records] ) } -1

NOTE: we just introduced a Fixed LoD in our view and any time you do so, you need to think about the order of operations. Fixed LoDs happen BEFORE dimension filters. In our view, our Distance Filter field is a dimension and in order for our title calculation to work, we need to add this filter to context.

Tableau_-_Places_of_interest_within_a_user_selected_radius__from_PUBLIC_TABLEAU_COM__DEFAULT__.png

ROCK ‘N ROLL!

Now the last thing is the tooltip. It’s really straightforward except that Lorna’s language says “approximately…metres” and she’s round the number of meters to the nearest 10. Are you familiar with the ROUND() function in Tableau? You’re not? Well, let me enlighten you. The syntax for this function is:

ROUND( expression, number)

The expression is the aggregation that you want to round and the number is the number of places (or 10s) you want to round. The great thing about this function is that you can round on either side of the decimal. Where 1 is rounding to the nearest tenth place, -1 rounds to the nearest ten

And since this is the only place we’re using this field, we can simply edit our existing meters calculation and wrap what we have in ROUND( ###,-1). Pretty cool, right!?

Alright, let’s get this thing on a dashboard and set up our parameter action and…

2019-10-23 11-30-45.2019-10-23 11_36_34.gif

Go Forth and VIZ

2 comments on “How to use the DISTANCE function in Tableau | #WorkoutWednesday | 2019 | 43”

  1. Pingback: Best of the Tableau Web: A look back at TC, the Community, and a fond farewell | Tableau Software

  2. Johny Prem Talreja Reply

    Very nice explanation can i get the data and package to practice the same

Leave A Reply

Your email address will not be published. Required fields are marked *