This post is a complement to the publication “Drill Down/Up | Basic technique in a Multi-Level Hierarchy“, where a detailed explanation of the basics of how this technique works is given.
In this exercise I use a table of data from a long time ago on COVID data published by Johns Hopkins University.
In this data set, most of the countries present the information at the global level, but for some countries the data is disaggregated at the state level and even for the United States, detailed information is presented at the county level.
These data give us the opportunity to:
- On the one hand, to show how this Drill Down technique can be combined with the new functionality of multiple Mark Layer of Tableau 2020.4, so that with the definition of SINGLE MAP, show the information to different definitions of geographic roles (country, state or county) according to the Drill Down level selected.
- On the other hand, show a new example where different levels of disaggregation of the data are presented, where it is required to define the maximum level to be deepened at the level of the first element of the hierarchy, that is, at the country level.
- And also, it allows to show an example where two main charts are used for the Drill Down: a bar chart and a map chart, where either of the two charts can be used to advance in the Drill Down.
Below I share the steps to follow:
STEP 1. CREATE PARAMETERS.
| PARAMETER | TYPE | CURRENT VALUE |
| Level | Integer | 0 |
| Path | String | World |
STEP 2. DEFINE LEVELS BY COUNTRY.
Its definition can be as simple as replacing the constant of the maximum number of possible Drill Down levels, by a calculation using the CASE function, where knowing the data in advance, the maximum level per country is assigned.
| Maximum Level | CASE [Country Region] WHEN “US” THEN 3 WHEN “Australia” THEN 2 WHEN “Canada” THEN 2 WHEN “China” THEN 2 ELSE 1 END – 1 |
Or it can be sought that its definition is automatic based on the data, for example:
- When a country has more than one value in “Admin2”, this means that in that country the data reaches level 3 of detail.
- Otherwise, when a country has more than one value for “Province State” it means that in that country the data reaches level 2.
- Otherwise, the data in that country are global, that is, they are at level 1.
| Maximum Level | IIF({FIXED [Country Region] : COUNTD(IFNULL([Admin2], “”))} <> 1, 3, IIF({FIXED [Country Region] : COUNTD([Province State])} <> 1, 2, 1)) – 1 |
Since Drill Down is used immersed in the main charts, we will adjust the level by minus one, as explained in the exercise “Basic Techique using the main chart for the Drill Down“.
STEP 3. CALCULATE THE DIMENSIONS THAT WILL SUPPORT US WITH THE DRILL DOWN.
| DD Level | IIF([Level] = [Maximum Level], 0, [Level] + 1) |
| DD Label | CASE [Level] WHEN 0 THEN [Country Region] WHEN 1 THEN [Province State] WHEN 2 THEN [Admin2] END |
| DD Path | IIF([Level] = [Maximum Level], “World”, [Path] + “»” + [DD Label]) |
| DD Filter | STARTSWITH(“World»” + [Country Region] + “»” + [Province State] + “»” + IFNULL([Admin2], “”), [Path]) |
A new dimension is created whose objective is to include a “+” sign at the beginning of the description of the Drill Down options for which a next level of detail is possible. In such a way that the user can quickly identify the options where they can drill down to more detail than is not possible.
| DD Label Display | CASE [Level] WHEN 0 THEN IIF([Maximum Level] > 0, “+ “, ” “) + [Country Region] WHEN 1 THEN IIF([Maximum Level] > 1, “+ “, ” “) + [Province State] WHEN 2 THEN [Admin2] END |
STEP 4. CALCULATE THE DIMENSIONS FOR THE DRILL UP MENU.
| DU Level | ASCII([Country Region]) % ([Level] + 1) |
| DU Path | LEFT([Path], FINDNTH([Path] + “»”, “»”, [DU Level] + 1) – 1) |
| DU Label | SPLIT([DU Path], “»”, -1) |
| DU Color | [DU Level] = [Level] |
STEP 5. CALCULATE THE DIMENSIONS FOR THE MARKS LAYER.
The new Marks Layer functionality allows you to add multiple layers of geographic data to a map. Where the mark layers act as overlays and each layer works independently and can have its own mark type, subtitles and color.
This example is a variant of the basic use of the marks layer, since instead of making overlays about the geographical layers of country, state and county, what we want is to be able to visualize only the layer that corresponds to the current level of Drill Down, since be it country or state or county.
The key to achieving this is to create a new dimension for each level of the geographical hierarchy, seeking:
- On the one hand, that only one of the three layers is visible at a time, and
- On the other hand, that within that layer, only the elements that comply with the Drill Down filter are visible.
| Country | Marks Layer | IIF([Level] = 0, [DD Label], NULL) |
| State | Marks Layer | IIF([Level] = 1 AND [DD Filter], [DD Label], NULL) |
| County | Marks Layer | IIF([Level] = 2 AND [DD Filter], [DD Label], NULL) |
By assigning the value of null to the unselected layers and / or elements, they will not be shown on the map.
The new dimensions created by default do not have a geographic role assigned, therefore, they must be selected and with the right click choose the geographic roles option to assign the corresponding role: Country, State or County.
STEP 6. CALCULATE DIMENSIONS FOR COLOR.
From the second level of the hierarchy we will use LOD calculations to determine the number of cases to determine the color.
| Color by State | {FIXED [Country Region], [Province State] : SUM([Cases])} |
| Color by County | {FIXED [Country Region], [Province State], [Admin2] : SUM([Cases])} |
This calculation is to avoid that the layers that are kept hidden affect the minimum and maximum values within the color scale.
STEP 7. CREATION OF THE MAP CHART WITH MARKS LAYERS FOR THE DRILL DOWN.
Adding a markup layer is as simple as:
- Build a first map in the view that will be the base layer.
- Drag a new geographic field into view. The Add a Marks Layer control is available in the upper left corner of the view.
- Drop the geographic field onto the Add a Marks Layer control. A new marks layer is added to the Marks card and the layer is displayed in view.
In this example the base layer will be by country geographic role and we will use the new dimension created for this role: “Country | Marks Layer”.
For color we will directly use the sum of the cases.
The second layer will be by geographic role of State and we will use:
- The dimension of “Country Region”, plus
- The new dimension created for this role of: “State | Marks Layer”
For color we will use the Color by State dimension.
The third layer will be by County geographic role and we will use:
- The dimension of “Country Region”, plus
- The dimension of “Province State”, plus
- The new dimension created for this role of: “County | Marks Layer”
For color we will use the Color by County dimension.
In each of the layers, the Drill Down fields must be included: DD Level and DD Path.
STEP 8. CREATION OF THE DRILL DOWN BAR CHART.
Using the fields created for the Drill Down.
STEP 9. CREATION OF THE DRILL UP MENU CHART.
Using the fields created for the Drill Up.
STEP 10. DEFINITION OF PARAMETER ACTIONS.
La técnica utiliza acciones de parámetro para actualizar el valor de los parámetros cuando una de las opciones de Drill Down o de Drill Up es seleccionado.
Create two Parameter Actions for the Drill Down whose source is the two main charts: the bar chart and the map chart.
| PARAMETER ACTION | SOURCE | TARGET PARAMETER | FIELD OR VALUE | AGGREGATION |
| DD Level | Drill Down1 & Drill Down2 | Level | DD Level | None |
| DD Path | Drill Down1 & Drill Down2 | Path | DD Path | None |
Create two Parameter Actions for the Drill Up menu.
| PARAMETER ACTION | SOURCE | TARGET PARAMETER | FIELD OR VALUE | AGGREGATION |
| DU Level | Drill Up | Level | DU Level | None |
| DU Path | Drill Up | Path | DU Path | None |
Ready!
The interactive version without using SPLIT and FINDTH can be found here.
Thank you very much and if you have any questions about the post, feel free to contact me on Twitter (@rosariogaunag).
RosarioViz 