Update 02/27/2020: A new blog with a new technique to use Drill Down and Drill Up with Parameter Actions was published with the name “Drill Down with left menu and Drill Up with top menu“.
With Tableau 2019.2 the interactions with a visualization became more powerful, since the new functionality of Parameter Actions allows users to modify the value of a parameter through direct interaction with a visualization, such as clicking or selecting a “mark”.
The Parameter Actions have a lot of possibilities, among them we can talk about that it helps us to establish reference lines, we can use them in calculations, in filters, in colors, in sizes, in Drill Down and there are many ways of use for help us customize the way the data is displayed in a visualization.
In addition, the parameters are widely used. Who has not used or doesn’t know how the parameters work? The use of parameters is quite simple and that makes it easy to learn very quickly to use Parameter Actions.
Within the multiple possibilities offered by the use of Parameter Actions, I wish to dedicate the following blogs to deepen their use in different Drill Down techniques, given that I believe that in addition to facilitating the definition of complex Drill Down, it also speeds up the response time to be interacting with the visualization.
Parameter Actions for:
- Drill Down by Dates
- Asymmetric Drill Down
- Drill Down & Up
- Asymmetrical Drill Down with Subtotals
I chose to start with Drill Down by dates, a design that we have already done with other techniques and that is one of the basic or frequent requests.
- Definition of three levels of Drill Down hierarchy for Sales: Year, Month and Day.
- Initially, the annual sales information is displayed on a bar chart.
- When the user selects an annual sales mark, the chart will be updated and only the monthly sales information will be displayed on a bar chart for the selected Year.
- When the user selects a monthly sales mark, the chart will be updated and only the daily sales information for the selected month will be displayed in a line chart.
- When the user selects a daily sales mark, the chart will return to its original condition and display the annual sales information in a bar chart.
Next I share the steps that I used using Parameter Actions:
Step 1: Create a parameter of type date for each level of Drill Down (Three Levels – Three Parameters).
As the new functionality of Parameter Actions is going to be used, the first thing we have to do is create the parameters.
A parameter must be created for each different value that you want to identify when the user interacts directly with the visualization, that is, one for each level of the hierarchy. In this case: One for selected year, another for selected month and one last for the selected day.
The initial value assigned must be that of a date that we previously knew and that does not exist in the data to work. In this exercise I used the date of 1/1/1900 as initial value.
The parameters will be named: “DD Parameter1”, “DD Parameter2” and “DD Parameter3”, for year, month and day respectively.
Step 2: Create field with the value of the date used as the initial value of the parameters.
|Blank Date =
Step 3: Calculate the level of Drill Down.
Initially, the values of the three parameters are equal to the value of “1/1/1900” and therefore the level to start is 1.
Later, when the Parameter Actions are defined and the user begins to interact directly with the visualization, the values of each of the three parameters will be updated according to the mark selected by the user.
With the update of the values of the three parameters, automatically the value of the level field will be recalculated and its value will be changing between 1, 2 and 3.
|Max Level =
[DD Parameter1] = [Blank Date] OR
[DD Parameter3] <> [Blank Date], 1,
IIF([DD Parameter2] = [Blank Date], 2, 3))
Convert to Dimension.
Step 4: Create dimensions with the sales value for the bar chart and for the line chart.
|Bar Sales =
IIF([Max Level] <> 3, [Sales], NULL)
|Line Sales =
IIF([Max Level] = 3, [Sales], NULL)
Step 5: Create a new date dimension for each level of Drill Down, truncated to year or month or day according to the level of Drill Down that each variable represents.
A variable is created for each level of the hierarchy: Year, Month and Day and for each #ParameterAction. Its value will depend on the level of consultation that is being carried out.
These variables are affected by the action of the user on the chart and, in turn, when the #ParameterAction is activated, they serve to update the value of the associated parameter.
|DD Dim1 =
DATE(DATETRUNC(“year”, [Order Date]))
|DD Dim2 =
IIF([Max Level] >= 2,
DATE(DATETRUNC(“month”, [Order Date])), [Blank Date])
|DD Dim3 =
IIF([Max Level] >= 3, [Order Date], [Blank Date])
Step 6: Create a dimension to filter the records.
It’s required to create a filter to show only the next level of detail, according to the last mark selected by the user in the visualization.
For example, when the user selects the 2018 brand, the following query should show only the months corresponding to the year 2018.
[Max Level] = 1 OR
[Max Level] = 2 AND [DD Dim1] = [DD Parameter1] OR
[Max Level] = 3 AND [DD Dim2] = [DD Parameter2]
TO CREATE A PERSONALIZED AXIS BY LEVEL OF DETAIL
Step 6: Create a dimension for the “X” axis: “Date Axis”
To customize the signs of the date axis according to the level shown in the Drill-Down, a dimension type string will be created.
|Date Axis =
CASE [Max Level]
WHEN 1 THEN STR(DATEPART(‘year’, [Order Date]))
WHEN 2 THEN LEFT(DATENAME(“month”,[Order Date]),3) + “, ” + RIGHT(STR(DATEPART(‘year’, [Order Date])),2)
WHEN 3 THEN LEFT(DATENAME(“month”,[Order Date]),3) + ” ” + STR(DATEPART(‘day’, [Order Date])) + “, ” + RIGHT(STR(DATEPART(‘year’, [Order Date])),2)
Step 7: Create the Dimension “Sort”.
This new dimension will allow you to order the “Date Axis” values according to their date value.
CASE [Max Level]
WHEN 1 THEN [DD Dim1]
WHEN 2 THEN [DD Dim2]
WHEN 3 THEN [DD Dim3]
CREATE THE CHART
Step 8: Create the bar and line chart.
Move to the shelves of:
- Filter ⬅ “Filter” = True
- Columns ⬅ “Date Axis” and sort ascendingly by “Sort”.
The aggregation level in the Sort must be Min.
- Rows ⬅ “Bar Sales” for the bar chart and
“Line Sales” for line chart.
The variables Color, Detail and Tooltips apply on both axes.
- Color ⬅ “Max Level”
- Detail ⬅ “DD Dim1”, “DD Dim2” and ATTR ([DD Dim3])
- Tooltips ⬅ SUM (Sales)
The chart uses a double axis for sales. Both axes must be synchronized.
The variables DD Dim1 and DD Dim2 will be taken as discrete dimensions selecting them with the right click or as a second option, select the variables and with the right click activate the options “Exact Date” and “Discrete”
The variable “DD Dim3” will be taken by attribute, selecting the option with the right click. This feature is necessary for the line chart.
CREATE THE PARAMETER ACTIONS
Create a dashboard with the name “D_Drill Down” that includes the newly created chart.
Create a Parameter Actions for each level of Drill Down “Year – Month – Day” (Three Levels – Three ParameterActions).
To define them, use the “Change Parameter” option in the “Actions” menu, within the newly created dashboard menu.
|PARAMETER ACTION||TARGET PARAMETER||FIELD OR VALUE|
|Parameter1||DD Parameter1||DD Dim1|
|Parameter2||DD Parameter2||DD Dim2|
|Parameter3||DD Parameter3||ATTR(DD Dim3)|
Adjust formats and Ready!
In this exercise, we use the Parameter Actions to perform Drill Down of a date field within the same sheet.
The logic of this exercise requires for each level of hierarchy, the definition of:
1) A parameter of type date
2) A Parameter Action
3) A Dynamic Dimension to update the value of the associated parameter, when the Parameter Action is activated.
Each time a “mark” of the visualization is selected, the Parameter Actions are activated.
Each of the Parameter Actions must update the value of its associated parameter, according to a dimension.
Once all the parameters have been updated, the calculated fields that depend on the value of the parameters will be updated.
With the new values, the Visualization will in turn be updated.
Once again a “mark” is selected, the update cycle is restarted.
Thank you very much and if you have any questions about the blog, do not hesitate to contact me on Twitter (@rosariogaunag)