***UPDATED on 5/11/18 to include the awesomeness of the 2018.1 dual axis mapping improvements!***
** This simplifies the process of combining custom and generated latitude and longitude **
** Also added my demo datasets & 2018.1 TWBX as attached examples **
If you work with maps in Tableau, at some point in time you’ve probably wondered how you can add more than one layer of data. The dual axis capabilities in Tableau can make (some of) your layering dreams come true, though sometimes it is tricky to bend it to your will. In this post I’m going to explain and walk through some of the complexity of dual axis maps.
I’ve put all of the examples below up on Tableau Public as a workbook so that you can download an example set. The workbook is in Tableau 10.2 to take advantage of the awesome spatial file support.
There are likely other pathways to making dual axis maps, other than the methods that I am describing here – feel free to let me know what has worked for you and I’ll add them to this list!
To simplify, there are two types of geographic data that you can work with in Tableau mapping:
Latitude and Longitude
These are dimensions or measures with location coordinates from your data source or from a calculated field; they have a Geographic Role of Latitude or Longitude
Latitude (generated) and Longitude (generated)
These are measures generated by Tableau that tell Tableau how to draw your spatial data. For instance, you see these when you use Tableau geocoding data (e.g., you have a country name and want to use Tableau geographic data to draw the countries), or when you work with spatial file types. When you add a dimension with a geographic role to your worksheet, Latitude(generated) and Longitude(generated) are added to Rows and Columns.
When creating a dual axis map, the most important rule to remember is that you cannot make a map with both generated and non-generated latitude and longitude values together on the Rows and Columns shelves. This just doesn’t work. Even though you can’t make a map with a mix of the two the types of latitude and longitude pills on the Rows and Columns shelves, there are still ways to work around this (you just have to get creative)!
Dual axis maps – many ways - examples:
I’m going to start with the easy examples – dual axis maps where we don’t have to worry about how to combine generated latitude and longitude with non-generated…
Tableau geocoding data only
If you are working with Tableau geocoding data, you just need to drop a second set of Latitude or Longitude on rows (or columns) and change the level of detail on the second marks card. For an example, say you’re working with the Superstore dataset and want a map with sales by state with points showing the cities all of the customers:
Easy peasy since the datasource has both geocoding levels of detail (State and City).
Start by dragging State onto the viz – it will make a map showing centroids for each state.
Then drag Sales onto the color shelf and you’ll see a filled symbol map.
To make the dual axis map, drag a second copy of Latitude(generated) – this is the Tableau-created latitude for the geocoding polygons. It may seem weird to use a second copy of the same field, but since this is the Tableau geocoding data the geography (point or polygon) drawn is defined by whatever you put on the detail on the Marks card…so you can re-use it for different geographies. You should now have two maps that look the same and two Marks cards (technically three – All, and two that are named Latitude(generated) to match the name of the pills on Row).
Now, we can change the second map so that it shows points instead of filled polygons. To do this, open the lower marks card and drop the City dimension on detail. You’ll want to have both State and City for your level of detail because the state disambiguates the city (e.g., there are many Lincolns, but only one Lincoln, Nebraska). Depending on your map type, you might see an error that filled map isn’t an option for point maps, if so…just hit the dropdown at the top of the Marks card and change it to ‘Automatic.’ You can also remove the Sum(Sales) pill from color and symbolize the point markers however you want.
Then, the only magic to making the dual axis is to right click on the second Latitude(generated) pill on Rows and select Dual Axis.
If your points end up underneath your polygons and you want to change which symbols are drawn on top, just change the order of the Latitude pills on Rows. The order of the pills specifies the order of the marks.
And now you’re done!
You can also do the same thing with two polygon geocoding datasets – perhaps you want county-level data with state outlines. Just make your county map, drop another copy of Latitude(generated) on Rows and change the level of detail on the second Marks card to state. Change to dual axis and you’re done!
Tableau geocoding + spatial file
When you add a spatial data source to your workbook, Tableau creates values for Latitude(generated) and Longitude(generated). Because Tableau geocoding also uses generated latitude and longitude values, we can combine them just like we did with the examples above.
Drop the Geometry measure onto the worksheet to draw the shapes from your spatial data source. You’ll see Latitude(generated) and Longitude(generated) in the Rows and Columns. If needed, disaggregate the features on the map by putting a relevant dimension onto detail.
To add a dual axis with Tableau geocoding, start by dropping a second copy of Latitude(generated) onto Rows. In the second Marks card that appears, remove the detail used for your spatial data source (it’ll be something like COLLECT(Geometry) and whatever you put on detail to disaggregate the features), and replace it with the dimension that defines the Tableau geocoding role you want to use. Then you just need to right click on the second Latitude(generated) and select Dual Axis.
Spatial file + spatial file
It’s great that dual axis maps are possible working entirely with Tableau geocoding data, or combination of Tableau geocoding data and a spatial file, but what if it’s two shapefiles that you’re dealing with? That’s a little more complicated, but totally doable. Currently, there isn’t a way to union two spatial files, but you can use an outer join (on non-matching fields) to combine two spatial datasets (or you could extract each file into a TDE and union the TDEs…but I’m not going into that here…).
Whaaatttt? Make a join on fields that don’t match? Yes – because that gives you unique rows for every geometry in your dataset, and since we can’t yet union spatial data files this allows us to get our data in a right-enough format to make the dual axis map. The two ‘layers’ for your dual axis can be drawn independently because the Latitude(generated) and Longitude(generated) measures created by Tableau will only see the geometry for the level of detail that you select on the marks card (and nulls for the other dataset). And then when you add the second Latitude(generated) you’ll select a level of detail for the other dataset and Tableau will see geometry for that second dataset and nulls for the first. Huh? Let’s walk through it to explain…
In this example, we’ll use two shapefiles – USGS_earthquakes.shp (points for earthquake locations) and plate_polygons.shp (polygon boundaries of plates). Individually they are two tables with Tableau-generated geometry columns:
If you are using Tableau 10.2 or later – and if you are trying out this example with shapefiles, I assume that you are using 10.2, you can just create a full outer join between these two (using calculated fields of 0 and 1).
You should get a table with rows for all earthquakes and all plates, but where there is detail for plates there should be null for earthquakes and vice versa:
To make the dual axis viz, add the geometry for plates to a viz and color encode by the Plate dimension so that they polygons are all disaggregated and uniquely colored. Since there are a ton of Null geometries from the earthquake data, they show up in the map legend, but aren’t drawn on the map (because they’re null!). It’s easy to ‘hide’ those in the legend. Don’t ‘remove’ the rows, though, because then you won’t have those rows to draw on the dual axis!
Now you just add a second copy of Latitude(generated) to the Rows, change the level of detail on the lower Marks card to use the geometry from the Earthquake shapefile. Remove the Plate pill on color (it doesn’t do anything for our earthquakes since Plate is null for the earthquakes), and drop Earthquake on detail to disaggregate the earthquake point geometries. Symbolize them up to your liking and then right click on the second Latitude(generated) and select Dual Axis.
Spatial file + CSV
…and now, the more difficult dual axis map…combining a data set with generated latitude and longitude (shapefile) with a dataset with non-generated latitude and longitude (points from a CSV).
UPDATE: If you're working with Tableau 2018.1 or later, there is a great new feature to make it easier to combine custom and generated latitudes and longitudes in a dual axis map.
The 2018.1 and beyond easy dual axis method:
First thing is to get the spatial file and CSV hooked together with a full outer join. I find it easiest to just use a custom calculated field with 0 and 1 to make sure I get all rows of both tables:
On the worksheet, add the geometry from your spatial file - this will put generated latitude and longitude on the rows and columns.
Drop a second copy of Latitude(generated) on Rows to make the second axis. In the second marks card that opens, remove all of the detail information...and then just replace it with the Latitude and Longitude values from your CSV.
Drop on the a dimension to disambiguate the points, right click on the second Latitude(generated) pill and select dual axis. And then you're done!
Note that it also works the other way around...you can add the Latitude and Longitude from the CSV first, and then use a second copy of Latitude on Rows to create the dual axis. Then on your second marks card, just drop the geometry from your spatial file on the viz and you're done!
But, if you're using Tableau 10.5 or earlier, here is the slightly more complex process:
First thing is to get our spatial file and CSV hooked together using a full outer join. Since we can’t union spatial files (yet), we will use the join to give us a data source with a single row for every feature. To avoid problems with filtering (I don’t want to filter my polygons and have some points disappear as well), we’ll join using calculated fields of 0 and 1.
Because the join doesn’t match (0 != 1), we end up with all of the rows from our shapefile input (North Carolina counties in this example) and then all of the rows from our csv file. This gives us a lot of null values. Where I have attributes for the shapefile I have nulls for the attributes in the csv, and vice versa.
Because all of the features in the shapefile have null values for the Latitude and Longitude fields, we’ll need to create a calculated field so that we have a valid value for the Latitude and Longitude geographic roles for every row. It’s easy to set this up using one of my favorite calculations in Tableau: ZN. (Special thanks to Dan Cory for sharing the joys of ZN with me). ZN checks an expression to see if the result is null. If it is, it returns 0; if it isn’t null, it returns the value of the expression.
So, we’ll make new calculated fields for latitude and longitude like these:
Right click on each and set the geographic roles to Latitude and Longitude, respectively.
Set these to geographic role of Latitude and Longitude.
Now we’re set up to make a map!
Drop Latitude (calc) and Longitude (calc) (the new calculated fields) onto the worksheet. Use a unique dimension (primary key) to disambiguate the points. In the example in my workbook, I used the ID field for the points. I only used the ID for my points because the Latitude (calc) and Longitude (calc) values put in as a placeholder for the polygons are all the same (0).
To remove all of the un-necessary points at 0,0 – add ID to color and in the legend, right click on the ‘Null’ legend item and select ‘Hide.’ This doesn’t filter out the rows, it just hides them from the current view. After you do this you can take ID off of color if you want.
Now for the dual axis – drop another copy of Latitude (calc) on Rows. You should see two maps.
On the lower marks card, remove the all of the pills specifying the level of detail and drop the Geometry from the county shapefile on detail instead. If you’re working with polygon data, you may be surprised that NOTHING happened. Try changing the type of mark to ‘Filled Map’ – and then you should have a filled polygon map!
Update the look of the symbols until you’re happy, then right click on the second Latitude (calc) pill on Rows and select dual axis. If your points are under your polygons on the dual axis map, just swap the order of the Latitude (calc) pills and they’ll change drawing order. And now you’re done!
I hope that this has been helpful for explaining the wonders of dual axis mapping using different data sources! Let me know if I have missed combinations of data sources that you need to use in making your dual axis map.