Drill-down master dimensions are a type of Qlik Sense master item. They allow the user to use the same filter pane section to filter to a deeper level with each subsequent filter application. For example, one may filter by the country first, the region second, and the city third, with the filter pane option changing with each subsequent filter applied.
This article will explain how to create drill-down master dimensions and how users will interact with them. We will also discuss some limitations of such filters and how we may address them.
How to create a drill-down master dimension
To create a drill-down master dimension, enter the sheet ‘edit’ mode and choose ‘Master items’ in the asset menu on the left. Click on ‘Dimensions > Create new’.
In the pop-up, choose ‘Drill-down’. Then, add the fields you require. You can use the list of fields on the left or click on the formula icon in the ‘Fields’ box to open the expression editor. Dimension formulas are allowed. Note that the drill-down works top to bottom. First, the user will see the topmost field. Once they choose a value in that field, they will see the second field and so on.
Name the master dimension and add a description if applicable. Choose a dimension colour if needed.
Add tags and glossary terms if applicable.
Click ‘Create’.
Once you click ‘Create’, the new dimension is added to the list of master dimensions. It can be added to visualisations in the same way a normal master dimension can be.
How to interact with a drill-down master dimension as a user
Users interact with drill-down dimensions in a slightly different way. In a filter pane, the field the user filters by each time changes with each subsequent level.
In our example, the app contains visitor data for a small DIY blog. The drill-down consists of the Google Analytics Session Medium as the topmost field and the Google Analytics Session Campaign as the bottom field. When the user first goes to filter by the dimension, they see the values in the GA Session Medium field. Once they select one and click on the filter again, they see the values in the GA Session Campaign field.
Note the third screenshot. Once the lowest level of the drill-down is reached, the filter remains showing that field.
When used in a chart, applying a filter in the topmost field changes the values the chart shows.
On the left screenshot, we haven’t applied any filters; thus, the dimension is the GA Session Medium. On the right screenshot we applied the ‘pin’ medium filter. As a result, the chart has been updated to show the values in the GA Session Campaign field.
Drill-down master dimension limitations
Limitation 1: you must filter by a single value only for the filter to drill down
Drill-down dimensions can be useful in the right context. The limitation of drill-down dimensions is that to drill down to the next level, you have to select only one value. If more than one value is selected, the filter will not drill to the next field. Using the above example, if our user selected ‘pin’ and ‘organic’ in the GA Session Medium, the filter would still show the GA Session Medium as a result rather than changing to show the GA Session Campaign.
This is often relevant and friendly for users. However, in some situations, it isn’t. For example, let’s assume that we created a drill-down dimension of dates: year, month, day-month. At first, we see the choices of the year. Once a year is selected, we see the month and, after, the day-month.
Imagine a situation where you want to filter by two months and still see the data in a day-month format. Using the current drill-down master dimension, this isn’t possible.
Solution 1: use a normal dimension and an expression to change what values are shown, given the current selections
The first solution to this limitation is not to create a drill-down dimension but instead to vary what values are shown using an expression. Note that this method can have issues when used on more than one level drill, so make sure to double-check that the filters are working as expected. Take the following expression:
=If(GetSelectedCount("=Month(Date)") > 3 or GetSelectedCount("=Month(Date)") = 0, "=Month(Date)",
Dual(Num(Day(Date),'00')&'-'&Month(Date), Num(Month(Date),'00')&Num(Day(Date),'00')))
This expression will return the month if the user has not filtered by a month or has selected more than three months. If they filter by one to three months, the expression will return the day-month. Note how the year filter is left out of the expression. This is because if a nested ‘If’ is used, when the user filters by the year and then by the relevant months, the year filter will reset to show all the relevant years for the months selected. This is the limitation of this method – it’s not possible to create a drill of more than one level deep using this example.
Solution 2: provide multiple filters or multiple dimensions in the chart axis
An easy solution if your user needs to be able to filter by more than one value is not to use a drill-down dimension at all but to provide filters for all the relevant fields. Any visualisations can be given alternative dimensions so the user can switch between the axis values they see. Using our date, month and day-month example, we can create the following bar chart:
The user sees the year to begin with. When they filter by a year, they still see the year until they use the alternative axis selection and choose the month. When they filter by the month, they still see the month until they switch to the day-month alternative axis.
From a user experience perspective, this is a bit more tedious but it gives a lot more flexibility to what users can see.
Solution 3: provide a button to change the filter or dimension axis values
Another solution that may be a bit more user-friendly than solution 2 is to use a variable to define the dimension and give the user a button or a drop-down visualisation to choose the appropriate option. Imagine this expression in the bar chart dimension:
Pick(Match($(vDateView),1,2,3),
Year(Date),
Month(Date),
Dual(Num(Day(Date),'00')&'-'&Month(Date), Num(Month(Date),'00')&Num(Day(Date),'00'))
)
The expression checks the value of the variable vDateView and chooses the appropriate field to return. The variable vDateView is set to 1 by default but is used in a button variable visualisation with the year, month and day month options for the user to choose from:
The user still has to click on the relevant button when they want to change the chart axis, but it’s one click per change vs two when using the alternative axis, so it is a tad more user-friendly.
Limitation 2: you cannot rename derived dimensions in the drill-down
The second limitation is that when you choose the dimensions that will be part of the drill-down, you cannot label them. Thus the user sees the unfriendly dev field names. In addition, if you used an expression to derive a dimension, the user will see the expression. Note this screenshot (also used above):
Because we derived all the dimensions using expressions, when the user filters by them, the names are not easy to understand for users on the axis.
Solution 1: create the necessary dimensions in the load
The obvious solution is to ensure all the dimensions you need for the drill-down are created in the load and named appropriately. Here is an example of the same chart using dimensions derived during the load:
The names of each subsequent drill-down field are much clearer. These were derived using a calendar definition script, however, if you create the fields as normal, the names you give them can be even more user friendly.
Solution 2: hide the axis title
The second solution is to simply hide the axis title on the chart. Note that this isn’t a suitable solution if you use the drill-down dimension in a filter pane. Depending on your dimensions, hiding the axis may not be appropriate. In our date example, it’s fine to hide because it’s clear to the user that we’re working with dates. If you’re working with something less obvious, you should leave the axis title to avoid confusion and use solution 1 to clean up the dimension labels.
