Mastering Spatial Analysis: 3 Methods for Custom Territories
Optimize Your Insights: Custom Territories with Tableau’s Tools
Method 1: Using Geographic Data for Custom Territories
Method 2: Aggregating Data for Enhanced Analysis
Method 3: Leveraging Spatial Analysis Techniques
Unlocking Tableau’s Potential: Advanced Spatial Analysis
Tableau has a very powerful instant Geo-coding property where it can identify mapping fields and assign geographic roles accordingly (Eg. Country, State, City, Zip Code, Airport, Area Code, Congressional District etc.). In many situations where clients use geographic data as the main feature for reporting, Tableau developers have the need to combine multiple geographic entities into a bucket or a group. With Tableau 10.0 a new feature called Geographic grouping was introduced. It is an extension of the existing group feature by providing an automatic Geographic Role (Groups are manual and static buckets or segments created using existing Dimension members). Now this group acts as a parent and can be used on a Geo map without the support of the child field.
Let us look at a small example. Let us assume the data set given to us has 12 States or 12 Countries or 12 Zip codes etc. Basically, we are looking at a filled map that has 12 polygons. Each of these polygons have population or sales or number of hospitals or number of ATM kiosks etc. Now the end user wants to combine some of the polygons and assign a new territory for those combined ones something like East and West; High Concentration and Low Concentration; North and South; Primary, Secondary and Tertiary areas etc. This feature is now enabled in Tableau 10.
There are multiple ways to achieve this in Tableau. But we also need to focus on an aggregated measure. Tableau allows us to do this using Geographic Groups or we can assign Geographic rule using the child field. In this blog we will be focusing on assigning Geographic role and a method using Table Calculation with Helper function and another method using Level of Detail Calculation (LOD) and Helper function combination.
For this example, we will be using our very own Sample – Superstore dataset with the Orders table.
Method 1: Utilizing Tableau’s Native Tools for Custom Territory Creation
This is a fairly straight forward method. But we have to appreciate Tableau for adding this feature. When we do it for the first time, it almost looks like a magic on the sheet.
1) Starting with a Filled Map for all States in USA with the Auto generated Latitude and Longitude fields. (If the data source has Latitude and Longitude fields, the same concept applies)
(We must observe that for very map, field with a Geographic role must be on the Marks shelf (preferably the Detail card). If the Geo field in not present, it will result in a Null Map or all co-ordinate will converge to the center of the map.
2) Next, we will pick a field that higher than State (Essentially the parent of State field). We have a Country field which has only one entry called USA, but that will not be of any use as all States belong to USA. So we need a field called Region as USA is divided into 4 segments. If the Region field was absent, we could have created our own regional buckets using groups.
3) Use the Region field on the Color card
4) To get better understanding, we will use a measure on the Label card. For this example, we will use Sales as Sum aggregation.
Now the end user has come up with a requirement where all States belonging to a Region must be aggregated to show the total Sales for that region on the map.
Here most of us make a mistake. Assuming that Region will take care of things, we remove State from the view. That is where the map fails. At this point, Tableau is only recognizing Region as a String field. Central East, South and West do not have any geographic property as those are members provided in the data for classification.
5) Next step would be to convert Region into a Geographic field. Here we will direct Tableau to use features from State field (In most situations, child fields inherit parent properties. In this case, it seems like reverse inheritance.)
At this point, Region has become a Geographic entity and formed a Parent-Child Hierarchy with the State field.
6) Now, we can remove State field from Detail and automatically the Sales value will aggregate.
Method 2: Leveraging Spatial Data Blending for Aggregate Values
For this method, we will take support from Table calculation and Helper function to achieve the same result. The initial steps are same
1) Create a Filled Map for State showing Sales as Sum aggregation and colored by Region.
The idea as established in the introduction to get the total value within the Region.
2) We will apply a Running Total table calculation on the map for the Sales field. By default, the Table Calculation will be executed across all States within a Region.
The end goal is to get only that Running total from the last cell. Since this calculation is conducted alphabetically, we can use any of the Helper functions: First() or Last() or a combination of Index() and Size(). A little more explanation is found in one of the previous blogs (http://blog.useready.com/infobytes//top-n-and-bottom-n-in-tableau-4-methods)
For this example, we will use the Last() function.
3) If Last() = 0 then Running_Sum(Sum([Sales])) end. Here Tableau will only pick on the last value. This field will be used on the Label card. This calculation also is executed across all States within a Region.
4) This step will focus on cosmetics – Moving the label to center, Adjusting the Opacity and Removing Borders.
Method 3: Implementing Custom Calculations for Territory Aggregation
For this method, we will use Level of Detail Calculation and Helper function to achieve the same result. The initial steps are same.
1) Create a Filled Map for State colored by Region. Instead of Sales for each State, we need Sales for the entire Region. So we will use a Fixed Calculation.
As we can see, Fixed Calculation will duplicate for every row. So, every State will have the Region’s total value. We need only one of them. Same as the previous method, we can use either First() or Last() or a combination of Index() and Size(). Here we will use First() function.
2) If First() = 0 then Sum([Regional Sales]) end. This calculation will only pick the first value. This field will be used on the Label card. This calculation also is executed across all States within a Region.
3) Like the previous method, this step will focus on cosmetics – Moving the label to center, Adjusting the Opacity and Removing Borders.
Some more examples using the same concept are below:
