In previous blogs we had already worked with Drill Down and Drill Up using Parameter Actions. In those exercises, the definition of the menu for Drill Up was achieved using several sheets for the menu, one for each Drill Up option required.

In this blog I will share a new technique to present Drill Up options using a single sheet.

What do we use Drill Down and Drill Up for?

The Drill Down is a very useful tool for queries since it allows the user to go from the general to the specific. When we use a Drill Down menu, we navigate in a hierarchy of dimensions or levels from the upper to the lower dimension, by selecting one of the values ​​of the presented dimension.

As for Drill Up, implies going from the specific to the general: When using a Drill Up Menu, we navigate to higher dimensions of a hierarchy with the difference that the jumps in the levels can be abrupt; we can select any dimension of the deployed.

Drill Down and Drill Up has multiple uses, for example with accounting, financial, inventory, portfolio, or other data, since it allows, from a visualized information, to “immerse” in that information in order to know the detail of the data that has been used to create it.

In my case, where I have worked with financial information for quite some time, I have seen that when a figure goes out of its normal range, or on the one hand, there is a need to understand what caused this deviation; or on the other hand, the person distrusts the numbers, therefore, being able to use a tool that helps in traceability, to know where the information comes from, gives us greater understanding, credibility and confidence when analyzing and submit information.

Requirements:

  • Use Orders from Tableau superstore dataset 2019.4.
  • The dimensions Region, State, City and Postal Code will be used for the navigation of Drill Down and Drill Up.
  • On the left side, you will have a Drill Down menu in a single sheet, where the values of the next level of the hierarchy will be displayed, from which one of the options can be selected.
  • At the top, there will be a single sheet menu, where one to five levels of Drill Up will be displayed. This menu will help us: 1) To indicate the values of the current hierarchy as a heading; and 2) To allow the user to select any of the options in the upper levels of the hierarchy, to return the query directly to the desired level.
  • In the central part, the main graph(s) will be located.

Feb-21-2020 17-53-44

Next, I share the steps to be followed to meet these requirements.

STEP 1. CREATE PARAMETERS.

For this exercise it needs only two parameters. These parameters are shared and updated by both menus.

PARAMETER TYPE CURRENT VALUE ALLOWABLE VALUES
Level Integer 0 All
Path String Overall All

For the initial value of the “Path” I used the description “Overall” to indicate the starting point in the Drill Down of the hierarchy, however, any other description can be used, such as: “Home”, “Total “, or “United States”.

In case you decide to use another description, you only have to replace the “Overall” string in the formulas, with the new description.

DEFINITIONS FOR DRILL DOWN

STEP 2. CALCULATE THE DIMENSIONS THAT WILL SUPPORT US WITH THE DRILL DOWN.

Five levels of Drill Down will be handled: Region, State, City, Zip Code and Return to Overall. Thus, a level for each dimension has to be included in the Drill Down, plus an additional level to present the option to close the current drill down cycle and reset with a new one.

The Postal Code field by default is considered as numerical, so it’s required to change its data type to a string and thus match the type of data handled in the rest of the hierarchy values.

The “DD Level” and “DD Path” dimensions will help us to update the parameters defined in step 1 and the “DD Label” dimension will have the value of the visible legend of the menu for the user to identify the Drill Down option desired.

DD Level IIF([Level] = 4, 0, [Level] + 1)
DD Label CASE [Level]
WHEN 0 THEN [Region]
WHEN 1 THEN [State]
WHEN 2 THEN [City]
WHEN 3 THEN [Postal Code]
WHEN 4 THEN “Overall”
END
DD Path IIF([Level] = 4, “Overall”, [Path] + “/” + [DD Label])

Convert the “DD Level” field to dimension with the right click or just drag it to the dimensions.

I used for the last level of “DD Label”, the description of “Overall”, however, it could be replaced by the value of the last field in the hierarchy, depending on which one best suits what you want to show.

The following definition is to select or filter from Superstore the records that meet the Drill Down user selections set to the Path value.

Update: 06/16/2020

Compact formula:

DD Filter CONTAINS(“Overall/” + [Region] + “/” + [State] + “/” + [City] + “/” + [Postal Code], [Path])

Original formula:

DD Filter ([Region] = SPLIT([Path], “/”, 2) OR SPLIT([Path], “/”, 2) = “”) AND
([State] = SPLIT([Path], “/”, 3) OR SPLIT([Path], “/”, 3) = “”) AND
([City] = SPLIT([Path], “/”, 4) OR SPLIT([Path], “/”, 4) = “”) AND
([Postal Code] = SPLIT([Path], “/”, 5) OR SPLIT([Path], “/”, 5) = “”)

End of update

Finally, we will calculate the legend of the title and subtitle for the heading of the Drill Down menu.

DD Title IIF([Level] < 4, “Drill Down “, “”)
DD Subtitle CASE [Level]
WHEN 0 THEN “by Region”
WHEN 1 THEN “by State”
WHEN 2 THEN “by City”
WHEN 3 THEN “by Zip Code”
WHEN 4 THEN “Return to”
END

STEP 3. CREATE THE SHEET FOR THE DRILL DOWN MENU.

Move to the shelves of:

  • Filters ⬅   Year of Order Date = 2019
  • Filters ⬅   “DD Filter” = TRUE
  • Rows ⬅   “DD Level” and “DD Path”
  • Text ⬅   “DD Label”
  • Tooltips ⬅   “DD Title” and “DD Subtitle”

The “DD Level” and “DD Path” fields must be hidden, for which, the field must be selected with the right click and deselect the “Show Header” option.

The “DD Title” and “DD Subtitle” fields will be used to define the sheet header.

Adjust colors and formats.

Picture2

DEFINITIONS FOR DRILL UP

STEP 4. CALCULATE THE DIMENSION TO PRESENT DRILL UP MENU OPTIONS.

Up to 5 levels of Drill Up will be handled: Overall, Region, State, City and Zip Code. That is one level for each level of Drill Down that has been navigated in the hierarchy of dimensions, plus an additional level with the option to return to the “Overall”.

As we don’t have in Superstore a dimension with such values, we apply a small trick, which consists in creating the new dimension from another field, although there is no relationship between the new created dimension and the original dimension. In this exercise we will use the Order Date dimension to create the new dimension.

DU Level INT([Order Date]) % 5

Convert “DU Level” to dimension with the right click or just drag it to the dimensions.

The formula described here calculates the residual of dividing the numerical value of the date of the “Order Date” field by 5, giving possible values ​​as numbers between 0 and 4. That is, we are arbitrarily grouping the days into five groups (0, 1, 2, 3, 4), one for each required value for the new dimension or menu options required.

Update: 07/13/2020

If your data doesn’t have a date field, don’t worry, since it’s possible to use any other dimension, including any of the dimensions of the hierarchy with which you are going to work, the only requirement is that the original dimension has at least as many different values, such as what is required for the Drill Up menu (in this case, at least 5 different values). You can also use another type of formula to create the new dimension, for example, the Case () function could be useful for you.

End Update

This technique is known as “Internal Data Densification” and as we had already seen in the start of the year post “How to order Asc / Desc by a Dimension or Measurement with Parameter Actions” it is a very useful tool for defining menus where options are not defined in the data source. Make sure to check, whether the selected field from the data (here based on Order Date) gives back a valid new dimension with the required number of members.

Unlike the exercise carried out in the previous blog, where the options and the menu legends were fixed, since each menu option was assigned a unique name of measures or dimension, the definition of the menu in this new exercise is quite dynamic, since both the amount of options to be presented, as well as the legends have been displayed, will vary and depend on the user’s previous selections.

STEP 5. CALCULATE THE REST OF THE DIMENSIONS THAT WILL SUPPORT US WITH THE DRILL UP.

Once the “DU Level” dimension has been defined under the Internal Data Densification technique, we will calculate the rest of the dimensions required for the Drill Up.

Each of the new dimensions will be determined by the respective value of the “DU Level” dimension and the values of the parameters, whose value is determined by the decisions made by the user.

Update: 06/16/2020

Compact formula:

DU Path LEFT([Path], FINDNTH([Path] + “/”, “/”, [DU Level] + 1) – 1)

Original formula:

DU Path “Overall” +
IIF([DU Level] >= 1, “/” + SPLIT([Path], “/”, 2), “”) +
IIF([DU Level] >= 2, “/” + SPLIT([Path], “/”, 3), “”) +
IIF([DU Level] >= 3, “/” + SPLIT([Path], “/”, 4), “”) +
IIF([DU Level] >= 4, “/” + SPLIT([Path], “/”, 5), “”)

End of update

Update: 06/29/2020

Compact formula:

DU Label IIF([DU Level] = 0, “”, “/”) + SPLIT([DU Path], “/”, -1)

Original formula:

DU Label CASE [DU Level]
WHEN 0 THEN “Overall”
WHEN 1 THEN “/” + SPLIT([Path], “/”, 2)
WHEN 2 THEN “/” + SPLIT([Path], “/”, 3)
WHEN 3 THEN “/” + SPLIT([Path], “/”, 4)
WHEN 4 THEN “/” + SPLIT([Path], “/”, 5)
END

End of update

The “DU Level” and “DU Path” dimensions will be used to update the parameters defined in step 1. The “DU Label” dimension will contain the visible menu legend for the user to select the desired Drill Up option.

To finish the definitions for the Drill Up, a filter is required to select the levels of the Drill Up menu that will be visible and the color definition of the menu options that will allow us to distinguish the last level used, from the upper levels in the hierarchy.

DU Filter [DU Level] <= [Level]
DU Color [DU Level] = [Level]

STEP 6. CREATE THE SHEET FOR THE UPPER MENU OF THE DRILL UP.

Move to shelves of:

  • Filters ⬅   Year of Order Date = 2019
  • Filters ⬅   “DU Filter” = TRUE
  • Columns ⬅   “DU Level” and “DU Path”
  • Text ⬅   “DU Label”
  • Color ⬅   “DU Color”

The “DU Level” and “DU Path” fields must be hidden, for which, the field must be selected with the right click and deselect the “Show Header” option.

Adjust colors and formats.

Initially, the Drill Up menu will only show the first menu option, the rest of the options will appear one by one, as the Drill Down is applied to the hierarchy when using the Parameter Actions.

Picture3

DEFINITION OF THE MAIN GRAPHICS

PASO 7. DEFINITION OF THE MAIN GRAPHICS.

The main graphics that I used, come from a set of graphics included in one of the great templates that Andy Kriebel recently shared and so I took advantage of the work done in its design on the one hand and, on the other hand, I took advantage of the calculations made in the annual comparisons. The template can be viewed here.

The four KPI sheets defined in the template allow us to view monthly sales, utility, number of orders and customers for the year 2019. These 4 sheets are the ones I incorporated into the final design of my exercise.

But it’s also perfectly valid, that you define your own main graphics. These can be as simple as showing the total quantities of 2019 sales or these can be as elaborate as you want to implement this technique.

To synchronize the level of Drill Down and Drill Up with the main graphs, the filter of “DD Filter” = TRUE must be included

If you, like me, used the 4 KPI graphics of the template, you need to define the filter as context, so that the calculations of the KPI graphics are correctly performed.

However, if you defined your own graphic, make sure that in addition to using the “DD Filter” filter, also use the filter that corresponds to the year of 2019.

CREATE THE DASHBOARD

STEP 8. A dashboard is created that integrates the menu sheet for the Drill Down, the menu sheet for the Drill Up and the main graphics.

DEFINITION OF PARAMETER ACTIONS

STEP 9. DEFINITION OF PARAMETER ACTIONS.

For the dashboard created in the previous step, the following Parameter Actions will be defined for:

The Drill Down the left menu.

PARAMETER ACTION SOURCE TARGET PARAMETER FIELD OR VALUE AGGREGATION
DD Level Left Menu Level  DD Level Average
DD Path Left Menu Path DD Path None

The Drill Up the top menu.

PARAMETER ACTION SOURCE TARGET PARAMETER FIELD OR VALUE AGGREGATION
DU Level Top Menu Level DU Level Average
DU Path Top Menu Path DU Path None

To understand more about how the Drill Down and Drill Up menus work, then I share an image with the value of the three main variables involved, when the last user selection was the New York State.

Picture1

The image also shows how the definitions of both menus start from different sources.

Ready!

The interactive version can be view here.

Picture0

SUMMARY

In this blog we work with a new technique that allows us to handle Drill Down and Drill Up with a lower number of parameters and that Drill Up options can be shown on a single sheet.

For the definition of the Drill Up menu options, we use the Internal Data Densification technique, which, as we have seen in previous examples, I consider it ideal for the definition of menus where the options are not defined in the data source.

In this exercise, we were able to exemplify how the definition of the menu with the Internal Data Densification technique can be as dynamic as required, since the amount of options to be presented, as well as the legends to be used in the menu are not static, since they are the result of the previous selections of the user.

The procedure used in this exercise can also be used for graphs where the Drill Down is applied directly in the main graph, for which, only small adjustments in some of the formulas will be required. You can view the following two examples:

An adjustment in the formulas for these examples was that I removed the last level of the Drill-Down, since when the Drill Down was immersed in the main graph, the last level didn’t offer additional information, unless the graph is related to other graphs on the dashboard, which was not the case in these examples.

I would like to thank Andy Kriebel for the great templates he recently shared with us, since by using them they facilitate our work by taking advantage of the design and definition of the calculations necessary for the construction of the main graphs.

I also wish to thank for wise feedback along the way to Ann Jackson, Klaus Schulte and Luke Stanke.

Thank you very much and if you have any questions about the post, do not hesitate to contact me on Twitter (@rosariogaunag).