Using Set Actions to Drill Down from State to Zip Codes

Welcome back, friends! I was inspired by Bethany Lyons’ (@tablyze) presentation at #TC18, and how she built a 3-level map drilldown for Median Price for Properties sold in 2017-2018.

In her presentation, she didn’t really go into how it was built, but it was really a summary of the different use cases. Luckily, Craig Dewar has a pretty cool video on YouTube, walking you through the steps to recreate this. I give full credit to Craig.

The What?

If you don’t fully understand what we’re building, here’s a working gif:

ezgif.com-resize

In full disclosure, because some zip codes don’t fit into counties nicely, we need to do a little cleanup — AND because of this county boundaries aren’t going to look the same as we traditionally know them.

Getting Started

For this example, we need two files: a file of zip codes, and a state FIPS file.

1.) For the zip codes file, we are going to use the most recent file provided by the HUD USPS ZIP Code Crosswalk. For zips that cross over multiple counties, it has a ratio of residents, businesses, other and total. What I did was sort by tot_ratio descending (so you get the primary zip/county relationship) in Excel and then “Remove Duplicates.” You can either do that or just download the file here.

2.) In the zip/county, we don’t have state name. So we need to bring that in. You can find this file with a simple Google search, but it’s also on data.world here.

Into Tableau

Open up Tableau, and load your zip/county Excel file and your text file into a new datasource. The first two digits of the county FIPS code is the state FIPS code, so we’re going to  connect the files on a custom join calculation:

int(left([County],2)) = int([State Fips])

drilldown_map_9

 

On to sets!

1.) Once you are on your sheet, rename the field “County” to County FIPS (You don’t really have to do this, but it keeps my mind straight).

2.) Create a Hierarchy of State Name, County FIPS, and Zip, and rename it Location Hierarchy.

3.) Create a set of State Name, call it “Selected States” and pick a few.

4.) Next, we need to calculate how many states we have in our entire dataset. We need to put it in a level of detail calc to make sure we are counting over our dataset. Create a calculated field called “Number of States” with the following calculation:

{countd([State Name])}

5.) To work out the logic if we selected all the states or just some of them, create a “Selected State” calculated field:

if [Selected States] then [State Name] end

6.) Create another calculated field where we count the selected states, call it “Number of Selected States”:

{countd([Selected State])}

7.) Create a “Show Counties” calculated field. This will be a boolean field (T/F), telling us when to show county instead of state.

[Number of Selected States]<[Number of States]

8.) Create a “Conditional County” calculated field. This will show the County FIPS, if Show Counties is true:

if [Show Counties] then [County FIPS] end

9.) Drag your Location hierarchy onto the Details mark, and it should populate the map. You can also drag your “Selected States” set onto color so you can see easily which ones are in or out of the set (for testing purposes). The mark type will be Automatic (a circle). That is fine for now.

10.) Create a new Set from of Conditional County called “Selected Conditional Counties”, and select a few (for testing purposes).

11.) Create a new calculated field called “Show Zip”. Essentially, you are making sure it is not null and it’s selected.

{max([Selected Conditional Counties])}
and
{countd([Selected Conditional Counties])}>1

12.) Create a new calculated field called “Filter Set”.

if [Show Zip] then [Selected Conditional Counties]
ELSEIF [Show Counties] then [Selected States]
else TRUE END

13.) Drag Filter Set to filters, and select True. You should see the few counties you selected in your “Selected Conditional Counties” set.

14.) Create a new calculated field called Geographic Mark:

if [Show Zip] then [Zip]
elseif [Show Counties] then [County FIPS]
else [State Name]
end

This is where the magic happens, and why the hierarchy we did in step 2 is really important.

15.) On your Geographic Mark calculated field, click on the Abc field to the left of it,  click Geographic Role > Create From > Zip.

drilldown_map_10

16.) Do the same process for State Name and County FIPS.

17.) Drag Conditional County, and Geographic Mark to the Detail mark type.

18.) Go into your Selected Conditional Counties and Select All. Do the same for Selected States, so you can see the whole country.

19.) Drag “Number of Records” to the Color mark type — and magically all of your circles turn to states!

Creating the set actions

1.) The first set action we will create is on Selected States.

Click on Worksheet > Actions > Add Actions > Change Set Values.

Rename it to Show Counties. Run Action on Select. Select your set, and Add all Values to Set.

drilldown_map_11

(This is the opposite action I usually do, but like anything in Tableau, there are multiple ways to solve the same problem.)

2.) Do the same for Selected Conditional Counties.

Now if you click on any state, you should have the drill down capability!

Here’s a link to the workbook on Tableau Public.

Happy vizzing (and mapping)!

-Paul

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s