I used to think you couldn’t hot-swap geographic levels of detail in a Tableau visualization. And, before Tableau 10, you couldn’t. I mean, of course in Desktop or web authoring mode you can drag and drop fields, but for the end-user of a dashboard that wasn’t an option. You might have thought to use some kind of sheet swapping. And you could swap a couple of things with a dual axis.
But how could you allow an end-user to switch between the following in a single view?
And could you add additional levels of detail, like countries or area codes or custom sales regions?
The answer, it turns out, is to use a few of new Tableau features in combination (and by the way, there’s a whole other blog post to be written about how a single new feature is more than just a single new feature). Here, I turn to:
- Spatial File Connector (first appeared in Tableau 10.2)
- Cross Database joins (first appeared in Tableau 10.0)
- Join Calculations (first appeared in Tableau 10.2)
And, in practice, you could do it with just a single spatial file, assuming it had all the levels of detail you wanted. And join calculations might not be needed if everything matches exactly. But neither of those is often not the case and so we’ll take a look at an example that makes use of a couple of spatial files.
An Example of Swapping Geographic Levels of Detail in a Single Tableau Viz
You can imagine all kinds of practical real-world examples such as looking at voting and switching between zip code and congressional district or swapping between city and county to see how many movie theaters are showing the next Star Wars film.
Here, I’m selling the ever so seasonally popular pumpkin spice latte and I want to track sales geographically by state and also by zip code. And I want to allow the end-user to change the geographic level of detail on the fly. And I’ve got my data in Excel (but it could be almost any database or file type) and I’ve also downloaded some spatial files:
From there, I simply join the data together, using a join calculation, INT(Zcta5Ce10), to get Zip code to match in type between my sales data and the spatial file data:
My data source now has everything I need:
Specifically, I really need the Geometry fields that will tell Tableau how to draw the polygons for State and Zip along with the identifiers [State] Name, and Zcta5Ce10 (zip code). I do have State and Zip Code from my Excel data and Tableau recognizes them as geographic fields. But, I can only use one at a time. If I create a calculation based on both of the fields, I still can only assign one geographic role to that field.
But the Geometry fields don’t have that limitation. Tableau doesn’t care if they are states or zip codes or congressional districts or rivers or some combination of all kinds of geographic detail. Tableau only cares that they define polygons or lines.
And that allows me to use a parameter and a couple of calculated fields to switch between geographic levels of detail in a single viz.
There’s only a bit of setup required.
First, the parameter:
Geographic Level of Detail: a list of string values
- State
- Zip Code
Next, based on the selection of the parameter value, I can get the value of either of the Geometry fields:
[Selected Geometry]
IF [Geographic Level of Detail] = “State”
THEN [Geometry (tl 2017 us state.shp)]
ELSE [Geometry]
END
And finally, one more calculation to assign a value that identifies each individual shape (so Tableau will treat it as a separate mark rather than draw all states or all zip codes as one mark):
[Selected Geometry ID]
IF [Geographic Level of Detail] = “State”
THEN [Name]
ELSE [Zcta5Ce10]
END
And now in a single view, you can switch the geographic level of detail. And I didn’t have to stop at just two levels!
And that will keep your users warm and happy!
