Welcome to the fourth installment of our blog series dedicated to sharing specialized exercises in creating menus designed for the selection of elements within a hierarchy, with the main objective of improving the user experience!

On this occasion, we will focus on enriching the example presented in the previous post, by including the metric amount and incorporating the functionality that allows users to configure whether they want to expand or collapse the branches of the hierarchy tree.

The ability to expand/collapse menu branches can be applied to any parent node. However, by design decision, the ‘All’ root node in this exercise will not be configurable. This is because you want the next level in the hierarchy, corresponding to Categories, to always be visible.

Therefore, this functionality will only be incorporated into the Categories level nodes. In this way, the end user will decide whether they want to show the Sub-Categories corresponding to each Category.

How will we achieve this? To incorporate this functionality, it will be necessary to introduce a new parameter that stores the information of the categories (or parent nodes) that the user has decided to expand, that is, those categories in which they have chosen to show the next level of query.

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 scaffolding 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.

Unlike the previous exercise, this time it will be necessary to replicate the scaffolding table to generate two different ‘marks’ for each option presented on the menu. The first ‘mark’ will be used for the select/deselect buttons, while the second ‘mark’ will be used for the expand/collapse buttons.

STEP 2. DEFINE PARAMETERS.

In addition to the ‘P.Check’ parameter, designated to store the options selected in the menu and the ‘P.Mode’ parameter, used to internally simulate whether the options registered in the first parameter should be considered ‘Include Values’ or ‘Exclude Values’ When deciding to select these values, a third parameter will be introduced. This third parameter will be used to store the values of the top nodes that have been selected to expand that branch and display its child nodes.

PARAMETERTYPEALLOWABLE VALUESCURRENT VALUE
P.ModeStringAll»All=TRUE»
P.CheckStringAll|Furniture»Tables»
P.ExpandStringAll»Furniture»Office Supplies»Technology»

STEP 3. DEFINE FIELDS FOR THE HIERARCHY AND MENU.

At this point, it is crucial to remember that the user has the option to choose between: 1) Select/deselect and 2) Expand/Collapse the branch of the pointed node. Consequently, the formulas must make it possible to identify the action to be applied and determine how to proceed in each case.

We will start by modifying the classification of the ‘Level’ field so that it is recognized as a dimension and then we will define the ‘Action’ field. This last field is essential and will be used to identify the desired action, either 1) Select/deselect menu options or 2) Expand or collapse the branches of the decision tree or menu.

ActionIIF([Table Name] = “TScaffold”, “Check”, “Expand”)

Proceed to generate the field that contains the hierarchy of each node in the tree.

HierarchyCASE [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 in the previous 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 ‘Axis Check’ and ‘Axis Expend’ fields as their names indicate will be used to place the ‘Marks’ within the axis creating the visual effect of spacing to the left depending on the level of the node and finally, the ‘Shape’ field will be defined to identify the images to be displayed.

MenuIIF([Action] = ‘Check’,
CASE [Level]
WHEN 0 THEN ‘All’
WHEN 1 THEN [Category]
WHEN 2 THEN [Sub-Category]
END, “”)
Axis CheckIIF([Action] = “Check”, 0.09 + ([Level] * 0.06), NULL) // Aggregation = Minimum
Axis ExpandIIF([Action] = “Expand”, 0.02 + (Level * 0.07), NULL) // Aggregation = Minimum
ShapeIIF([Action] = “Check”,  
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’)),  
CASE [Level]
WHEN 0 THEN IIF([P.Expand] = ‘»’, ‘Expand’, ‘Collapse’)
WHEN 1 THEN IIF(CONTAINS([P.Expand], ‘»’ + [Category] + ‘»’), ‘Minus’, ‘Plus’)
WHEN 2 THEN ‘End’
END)

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.CheckIIF(FIND([P.Check], ‘|’ + [Category] + ‘»’) = 0, ”,
MID([P.Check], FIND([P.Check], ‘|’ + [Category] + ‘»’)))
Next P.CheckIIF([Action] = ‘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, [P.Check])
Next P.ModeIIF([Action] = ‘Check’,
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, [P.Mode])
Next P.ExpandIIF([Action] = ‘Expand’,
CASE [Level]
WHEN 0 THEN IIF([P.Expand] = ‘»’, ‘»Furniture»Office Supplies»Technology»’, ‘»’)
WHEN 1 THEN IIF([Shape] = ‘Plus’, [P.Expand] + [Category] + ‘»’, REPLACE([P.Expand], ‘»’ + [Category] + ‘»’, ‘»’))
WHEN 2 THEN [P.Expand]
END, [P.Expand])

Next, the definition of a field that will act as a filter will be detailed, allowing only the child nodes whose parent nodes have been marked to be expanded to be displayed.

Show?IIF([Level] > 1, CONTAINS([P.Expand], ‘»’ + [Category] + ‘»’), TRUE)

Finally, in this section, a field will be configured that will contain the sales value of the menu options that are selected.

Sales | SelectedIIF([Is Selected?], [Sales], 0)

STEP 4. DEFINE THE FIELDS TO CANCEL THE HIGHLIGHTING WHEN SELECTING ANY OF THE OPTIONS.

HLTRUE
UHLFALSE

STEP 5. BUILD THE MENU GRAPH.

Create a sheet with the name ‘Ex4’.

Move ‘Measure Name’ to the filter shelf and select ‘Axis Check’ and ‘Axis Expand’.

Move ‘Show?’ to the filter shelf and select the value ‘TRUE’.

Move ‘Measure Value’ to the columns shelf. Set axis range between 0 and 1. Hide the axis.

Move ‘Hierarchy’ to the rows shelf.

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 ‘Action’, ‘Next.P.Mode’, ‘Next.P.Check’, ‘Next.P.Expand’ and ‘HL’ fields to the ‘Detail’ shelf.

Define a new axis, for which a new field will be defined directly in the column shelf with the formula ‘AVG(1.0)’. Indicate that it will work as a double synchronized axle.

Later, we will modify this second axis to represent the data in a circle graph, with a white color with opacity at 0% and adjusting the circles to the smallest possible size. In the ‘Label’ shelf, we will replace the ‘Menu’ field with the ‘Sales | Selected’. Additionally, we will remove the ‘Action’ and ‘Shape’ fields from the detail shelf.

STEP 6. DEFINE PARAMETER ACTIONS TO UPDATE PARAMETERS.

PARAMETER ACTIONSOURCETARGET PARAMETERFIELD OR VALUEAGGREGATION
P.CheckEx4P.CheckNext P.CheckNone
P.ModeEx4P.ModeNext P.ModeNone
P.ExpandEx4P.ExpandNext P.ExpandNone

STEP 7. DEFINE ACTION TO AVOID HIGHLIGHTING.

In the dashboard, we will use a filter action to avoid highlighting when selecting one of the ‘marks’.

Ready! Links here & here

In this fourth episode of our series focused on improving the user experience in managing hierarchies and filters, we worked with the construction of a multiple selection filter in a three-level hierarchy to which the functionality of expanding/collapsing branches was incorporated in the decision tree. I hope you find the techniques shared useful.

I invite you not to miss the last episode of this series, where we will review how to build an individual selection filter in a multi-level hierarchy. In this design, we want the user to perceive that he is working with a single menu, although internally the construction of two independent menus is required: the Drill Down menu and the Drill Up menu.

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!