Welcome to the third blog in a series dedicated to sharing five exercises focused on creating menus designed for the selection of elements within a hierarchy. Our goal is that these menus not only fulfill the selection function, but also act as filters, integrating an attractive design (UI) and friendly interaction (UX).
This time we will focus on creating a multiple selection filter for a three-level hierarchy: All, Categories and Sub-Categories. The filter will allow users to choose or eliminate one or multiple values at the Sub-Category level, or at the Category level and even make selections at the Total level.
Unlike the previous exercise, in which we worked with a two-level hierarchy, now, when addressing a three-level hierarchy, it is required to extend the management of the inclusion/exclusion mode to two levels of the hierarchy. This condition must be applied and analyzed at each of the levels of the parent nodes of the hierarchy: ‘All’ and ‘Category’.
To determine if a Sub-Category is selected, it is necessary to evaluate both the inclusion/exclusion mode of each of its parent nodes until reaching the root node and verify if the Sub-Category is listed in the parameter dedicated for this purpose.
Now, let’s go behind the scenes to understand how this combination of factors operates.
As you can see, the key lies in understanding what happens when one or more conditions are not met. For example, if one of the three conditions is not met, the result is that the Sub-Category is not selected. Instead, if two of the three conditions are not met, we can consider the conditions to neutralize each other, similar to multiplying -1 by -1, resulting in the Sub-Category being considered selected.
Additionally, in this exercise, we will employ a technique that gives us the ability to densify data through the use of a relational data model. In this model, we will connect the Superstore data to a Scaffold table that will include the values zero, one, and two, corresponding to the three levels of the hierarchy we will focus on.
Get ready to explore this new example of hierarchies, filter design, and data densification! Let’s get started!
STEP 1. DEFINE THE DATA MODEL REQUIRED TO APPLY THE DATA DENSIFICATION TECHNIQUE.
We will use a relational model that will establish a connection between the data source provided by Tableau, known as ‘Superstore’, and a supporting table specifically designed to support the Scaffold process. In this table there is a single piece of data called ‘Level’. Each value in this field will represent a level of the hierarchy, being 0) Total, 1) Category and 2) Sub-Category.
The link between both tables will be established through the creation of a new calculation, assigning the value ‘TRUE’ in the calculations of both tables, which will guarantee a record-level association between them.
STEP 2. DEFINE PARAMETERS.
In addition to the parameter intended to store the menu options, called ‘P.Check’, we will introduce a second parameter called ‘P.Mode’. This new parameter aims to internally simulate whether the options stored in the first parameter should be considered ‘Include Values’ or ‘Exclude Values’ when deciding whether or not said values are selected.
| PARAMETER | TYPE | ALLOWABLE VALUES | CURRENT VALUE |
| P.Mode | String | All | »All=TRUE» |
| P.Check | String | All |
STEP 3. DEFINE THE FIELDS FOR THE HIERARCHY AND THE MENU.
Modify the classification of the ‘Level’ field so that it is recognized as a dimension and then proceed to generate the fields that represent the hierarchy of each node in the tree.
| Hierarchy | CASE [Level] WHEN 0 THEN ‘All’ WHEN 1 THEN ‘All’ + ‘ | ‘ + [Category] WHEN 2 THEN ‘All’ + ‘ | ‘ + [Category] + ‘ | ‘ + [Sub-Category] END |
Now, we will move towards defining the field that will be used as a filter in the graphs associated with the filtering menu. This formula will evaluate the three conditions mentioned at the beginning of the post: 1) Mode for ‘All’, 2) Mode for ‘Category’ and if 3) ‘Sub-Category’ is listed.
| Is Selected? | IIF(STARTSWITH([P.Mode], ‘»All=TRUE»’), 1, -1) * IIF(CONTAINS([P.Mode], ‘»’ + [Category] + ‘=FALSE»’), -1, 1) * IIF(CONTAINS([P.Check], ‘»’ + [Sub-Category] + ‘»’), 1, -1) = 1 |
Later, we will address the definition of the visible menu fields. The ‘Menu’ field will contain the label, the ‘Indent’ field will allow us, as its name indicates, to give a spacing to the left depending on the level of the node and finally, the ‘Shape’ field will allow us to see if the option is found. fully or partially selected or not selected.
| Menu | CASE [Level] WHEN 0 THEN ‘All’ WHEN 1 THEN [Category] WHEN 2 THEN [Sub-Category] END |
| Indent | 0.03 + ([Level] * 0.07) // Aggregation = Minimum |
| Shape | IIF([Level] = 2, IIF([Is Selected?], ‘Checked’, ‘Unchecked’), IIF({FIXED [Hierarchy] : MIN([Is Selected?])} = {FIXED [Hierarchy] : MAX([Is Selected?])}, IIF({FIXED [Hierarchy] : MAX([Is Selected?])}, ‘Checked’, ‘Unchecked’), ‘Partial’)) |
Later, we will move towards defining the fields necessary to update both parameters.
| Check? | IIF([Level] = 2, [Is Selected?], {FIXED [Hierarchy] : MIN([Is Selected?])}) |
| Substring P.Check | IIF(FIND([P.Check], ‘|’ + [Category] + ‘»’) = 0, ”, MID([P.Check], FIND([P.Check], ‘|’ + [Category] + ‘»’))) |
| Next P.Check | CASE [Level] WHEN 0 THEN ” WHEN 1 THEN REPLACE([P.Check], LEFT([Substring P.Check], FIND([Substring P.Check], CHAR(13))), ”) WHEN 2 THEN IIF(CONTAINS([P.Check], ‘|’ + [Category] + ‘»’), IIF(CONTAINS([P.Check], ‘»’ + [Sub-Category] + ‘»’), REPLACE([P.Check], ‘»’ + [Sub-Category] + ‘»’, ‘»’), REPLACE([P.Check], LEFT([Substring P.Check], FIND([Substring P.Check], CHAR(13)) – 1), LEFT([Substring P.Check], FIND([Substring P.Check], CHAR(13)) – 1) + [Sub-Category] + ‘»’)), [P.Check] + ‘|’ + [Category] + ‘»’ + [Sub-Category] + ‘»’ + CHAR(13)) END |
| Next P.Mode | CASE [Level] WHEN 0 THEN IIF([Check?], ‘»All=TRUE»’, ‘»All=FALSE»’) WHEN 1 THEN IIF(STARTSWITH([P.Mode], ‘»All=TRUE»’), IIF([Check?], REPLACE([P.Mode], ‘»’ + [Category] + ‘=FALSE»’, ‘»’ ), [P.Mode] + IIF(FIND([P.Mode], ‘»’ + [Category] + ‘=FALSE»’) = 0, [Category] + ‘=FALSE»’, ”)), IIF(NOT [Check?], REPLACE([P.Mode], ‘»’ + [Category] + ‘=FALSE»’, ‘»’ ), IIF(CONTAINS([P.Mode], [Category] + ‘=FALSE»’), [P.Mode], [P.Mode] + [Category] + ‘=FALSE»’))) WHEN 2 THEN [P.Mode] END |
STEP 4. DEFINE THE FIELDS TO CANCEL THE HIGHLIGHTING WHEN SELECTING ANY OF THE OPTIONS.
| HL | TRUE |
| UHL | FALSE |
STEP 5. BUILD THE MENU GRAPH.
Create a sheet with the name ‘Ex3’.
Move the ‘Hierarchy’ and ‘Menu’ fields to the row shelf. Hide the ‘Header’ for both fields.
Move the ‘Indent’ field previously defined with ‘Minimum’ aggregation. Edit the axis in a range of 0 and 1 and hide it. Hide lines and gridlines.
Define chart type of ‘Shape’. Move the ‘Menu’ field to the ‘Label’ shelf and the ‘Shape’ field to the shelf of the same name. Select the desired shapes.
Additionally include the ‘Next.P.Mode’, ‘Next.P.Check’ and ‘HL’ fields to the ‘Detail’ shelf.
STEP 6. DEFINE PARAMETER ACTIONS TO UPDATE PARAMETERS.
| PARAMETER ACTION | SOURCE | TARGET PARAMETER | FIELD OR VALUE | AGGREGATION |
| P.Check | Ex3 | P.Check | Next P.Check | None |
| P.Mode | Ex3 | P.Mode | Next P.Mode | None |
STEP 7. DEFINE ACTION TO AVOID HIGHLIGHTING.
We will use a filter action to avoid highlighting when selecting one of the ‘marks’.
In this third episode of our series focused on improving the user experience in managing hierarchies and filters, we work with building a multiple selection filter in a three-level hierarchy. I hope you find the techniques shared useful.
In the next and final two episodes of this series, we will continue exploring with techniques for managing hierarchies and filters. If you are interested in learning how to build three-level multiple selection filtering, adding the option to expand or collapse the upper levels of the hierarchy, don’t miss the fourth chapter of the series.
Any questions or comments about the exercise addressed in this chapter will be more than welcome. Don’t hesitate to get in touch.
See you next chapter!
RosarioViz 