By: Rosario Gauna @rosariogaunag

A few months ago, @RodyZakovich prepared us a challenge that required presenting three levels of Drill-Down on a single sheet, seeking to provide a better user experience (UX).

When this challenge was published, the version of Tableau 2018.3 was not yet released, so it was necessary to use a set of advanced tricks, including the use of data modeling and “Actions Filters” to solve the challenge. Link to the Blog

With the arrival of the #SetActions with Tableau 2018.3, the handling of Drill-Down in the Visualizations became much simpler.

Although in previous blogs I have provided clues to the main steps required to achieve this challenge with #SetActions. Given the questions I have still received, I considered it convenient to write a new blog dedicated to detailing each of the steps to solve this challenge using the functionality of #SetActions.

First let’s remember the main points of the challenge:

  • It’s required to work with three levels of Drill-Down: 1) Year, 2) Month, and 3) Day.
  • Initially, a bar chart with information at the Year level is displayed.
  • Subsequently, when selecting one or more of the years, the view must be replaced with a bar chart with the monthly information corresponding to the year or selected years.
  • Then, when selecting one or more of the months, the view should be replaced with a line chart, with the daily information corresponding to the selected month or months.
  • When finished, the original bar chart with the information at the year level must be displayed again.

Below I share the steps I used, in this new version:

FOR LEVEL 1 OF THE HIERARCHY (YEAR)

Step 1: Create a field called “YEAR”.

Since the three levels of the hierarchy are implicit in a single dimension: the order date, the first thing we will do is to have a new field that contains the values of the first hierarchy: Years.

The new field will be calculated truncating to years the value of the original date.

Year =

DATE(DATETRUNC(‘year’, [Order Date]))

Step 2: Create the “Year Set” Set.

The Set will be associated with the dimension created in the previous step.

picture02

Step 3: Create the Set Action “Year Set Action”

At this point, let’s define the first #SetActions.

Remember that the new functionality of the #SetActions allows users to update the values of a Set (i.e., which members of the Set are: IN / OUT Set), interacting directly with the “marks” of a Viz.

In the definition of the #SetActions you must indicate the Set to be updated or Target Set. In this case we will use the “Year Set” defined in the previous step.

Choose the option that all Set values should be removed when the selection is cleaned.

picture03

FOR LEVEL 2 OF THE HIERARCHY (MONTH)

Step 4: Create a Drill-Down Dimension “Month-DD”.

In this step, the value of the date truncated at the month level will be assigned to the new dimension when the year to which the record belongs is defined as “IN” of the “Year Set”. Otherwise, it will be assigned the value of NULL.

That is, it will contain the value of the month, as long as it corresponds to one of the years selected by the user in level 1.

How is this achieved? With the combined use of Set and #SetActions.

  • Remember that with the #SetActions, for the first time, the user is allowed to modify the original definition of a Set, by interacting directly with the Viz.
  • How is that? With the Set Action the user can control with their interaction with the “marks” which members are IN or OUT with the associated Set.
  • That is, the “Year Set Action” defined in step 3, will update which members are IN or OUT in the “Year Set” defined in step 2, according to the “marks” selected by the user in the Viz.

That is, the formula to be defined will evaluate for each record if the Year is IN in the SET: “Year Set”. If yes, the value of the month will be assigned, otherwise it will be assigned the value of NULL.

Month DD=

IIF([Year Set], DATE(DATETRUNC(‘month’, [Order Date])), NULL)

Step 5: Create the “Month DD Set” Set.

The new Set will be associated with the dimension created in the previous step.

picture04

Step 6: Create the Set Action “Month Set Action”.

Define that your Target Set is the “Month DD Set”, defined in the previous step. And choose the option that all Set values should be removed, when the selection is cleaned.

picture05

FOR LEVEL 3 OF THE HIERARCHY (DAY)

Step 7: Create a Drill-Down Dimension “Day DD”.

In this step, the value of the date of the order will be assigned to the new dimension, that is, to day level, when the condition that the year to which the record belongs is defined “IN” in the “Year Set” and in addition, the condition that the month to which the record belongs is defined “IN” in the “Month DD Set” is fulfilled. Otherwise, it will be assigned the value of NULL.

That is, it will contain the value of the day as long as it corresponds to one of the months-months selected by the user.

Day DD =

IIF([Year Set] AND [Month DD Set], [Order Date], NULL)

TO FILTER AND SHOW ONLY THE MOST DETAILED LEVEL MARKS

The Drill-Down on a single sheet implies using a Drill Down with exclusion or filter, that is, it’s required to show only the “marks” of the next level of hierarchy according to the level of hierarchy of the “marks” previously selected by the user.

This means that, if the user selects one or more “marks” of years, the next view should show only “marks” at the month level. In contrast, when the user selects one or more “level” marks of the month the following view should show only “marks” at the day level.

The next two steps will help us with this requirement.

Step 8: Create the Dimension “Level.

This variable will help us identify the maximum level of hierarchy that is being handled in each record according to the values of the “Day DD” and “Month DD” dimensions.

Level =

IIF(NOT ISNULL([Day DD]), 3,

IIF(NOT ISNULL([Month DD]), 2, 1))

Step 9: Create the Dimension “Filter”.

This variable compares the level of each record vs. the maximum level in the table. (TRUE / FALSE)

Filter =

[Level] = {FIXED : MAX([Level])}

TO CREATE A PERSONALIZED AXIS BY LEVEL OF DETAIL

Although it’s not a requirement of the original challenge, I decided to customize the date axis to show string formats depending on the displayed level of the data.

Step 10: 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 [Level]

WHEN 1 THEN STR(DATEPART(‘year’, [Order Date]))

WHEN 2 THEN LEFT(DATENAME(“month”,[Month DD]),3) + “, ” + RIGHT(STR(DATEPART(‘year’, [Order Date])),2)

WHEN 3 THEN LEFT(DATENAME(“month”,[Month DD]),3) + ” ” + STR(DATEPART(‘day’, [Order Date])) + “, ” + RIGHT(STR(DATEPART(‘year’, [Order Date])),2)

END

Step 11: Create the Dimension “Sort”.

This new dimension will allow to order the values of “Date Axis” according to their chronological order.

Sort  =

CASE [Level]

WHEN 1 THEN [Year]

WHEN 2 THEN [Month DD]

WHEN 3 THEN [Day DD]

END

CREATE THE CHART

Step 12: Create the bar and line chart.

Move to the shelves of:

  • Columns ⬅  “Date Axis” and sort ascending by “Sort”.
  • Rows ⬅  “Bar Sales” for the bar chart and

“Line Sales” for line chart.

  • Detail ⬅  “Year”, “Month DD” and “Day DD”

The chart uses a double axis for sales. You must synchronize both axes and hide the axis on the right side of the chart.

Adjust the tooltips and the formats.

Ready!

Link to Tableau Public

pictureaa

 

Below I share a summary of the main steps related to the use of the Set and the #SetActions in this exercise.

picture06

I hope this blog is useful for you.

If you have any questions about the blog, do not hesitate to contact me on twitter (@rosariogaunag)

Regards!

Rosario Gauna

Advertisements