Challenge, Modeling, Union, Actions Filter, UX, Workout Wednesday
by: Rosario Gauna @rosariogaunag
Although quite late, I want to share my experience with a very special challenge from @RodyZakovich presented in week 26 of Workout Wednesday. Link to challenge
In the first lines of the writing Rody told us very properly, that he wanted to spice it up with something harder their challenges.
Ooh! Ooh! That writing on behalf of Rody, was already an alert of what awaited us.
The challenge is to achieve three levels of Drill Down on a single sheet, through the use of data modeling and “Actions Filters”.
Last year I had the opportunity to learn from @VizWizBI, the two-level Drill Down technique on the same sheet through the “State to City Drill Down” challenge of week 41 of the year 2017. Link to the challenge
But had you heard of Drill Down from three or more levels on the same sheet? I do not.
Since I read the challenge, it was for me one of those challenges that I can’t stop thinking about, until I manage to solve the enigma.
Next, I share the final procedure that I used:
Step 1: Model the data – Create a copy of the Order data for each level of Drill Down to be used.
One of the techniques to achieve this goal is to create the “Union” of the data of “Orders” of Superstore with itself. In this case, three copies of the “Orders” data are required.
Why do I need a copy of the data for each level of Drill Down to use? Because each of the copies will present the information at the level of grouping or detail requested and will contain different data for filtering.
Remember that to identify the copy of the data in question, Tableau automatically creates a new dimension called “Table Name” with the values “Orders”, “Orders1” and “Orders2”.
Step 2: Create the variables of “Year”, “Month” and “Day”.
|Year = DATEPART(‘year’, [Order Date])|
|Month = DATEPART(‘month’, [Order Date])|
|Day = DATEPART(‘day’, [Order Date])|
Step 3: Create the variables to execute the “Actions Filters”.
For each level of Drill Down (or copy of the data) two variables will be defined for the “Actions Filters”:
- “Current Filter” will define the filter applied in the current Drill Down (according to the required level indicated in step 1).
- “Next Filter” will define the filter to be applied in the next level, once the” Action Filter “is activated.
Example of the values of the variables “Current Filter” and “Next Filter” for a subset of the data:
Step 4: Create variable to group the sales level: “Yearly” or “Monthly” or “Daily”
This new variable will also serve to customize the axis of the “x” depending on the level of the Drill Down.
- Yearly sales (text simulating yyyy format)
- Monthly sales (text simulating mmm, yy format)
- Daily sales (text simulating mmm d, yy format)
|X Axis =
CASE [Table Name]
WHEN “Orders” THEN STR([Year])
WHEN “Orders1” THEN LEFT(DATENAME(“month”,[Order Date]),3) + “, “ + RIGHT(STR([Year]),2)
WHEN “Orders2” THEN LEFT(DATENAME(“month”,[Order Date]),3) + “ “ + STR([Day]) + “, “ + RIGHT(STR([Year]),2)
Step 5: Calculate the “Sort” variable to order the variable used in the “x” axis. Turn it into dimension.
INT(CASE [Table Name]
Step 6: Calculate the value of the sales for the bar chart and for the line chart.
|Sales Bar = IIF([Table Name] <> “Orders2”, [Sales], NULL)|
|Sales Line = IIF([Table Name] = “Orders2”, [Sales], NULL)|
Step 7: Create the bar and line chart.
Move to the shelves of:
- Columns ⬅ “X Axis” and sort ascending by “Sort”.
- Rows ⬅ “Sales Bar” for the bar chart and Sales Line” for the line chart.
- Detail ⬅ “Next Filter”.
The chart uses a double axis for sales, you must synchronize both axes and hide the axis on the right side of the chart.
The chart will take shape until after the “Action Filter” of the next step has been executed.
Step 8: Define and use the “Action Filter” for the worksheet.
It is important to note that the “marks” selected by the user will send the “Next Filter” value to the “Current Filter” field once the “Action Filter” is applied.
Next, I share a diagram of the execution of the “Action Filter” in the three levels of Drill Down:
The example shows an “Action Filter” of a single selection, but the selection of multiple “marks” is allowed.
That is, if for example the “marks” of the years 2015 and 2016 are selected, the next level of Drill Down will show the 24 months corresponding to the two selected years.
Step 9: Adjust the tooltips and the formats.
Before concluding, I would like to take this opportunity to tell you that I am flattered to have been selected within the group of Tableau Ambassadors.
Those who know me know that, for me, Tableau was love at first sight. For many years I worked and suffered with traditional data analysis tools, but when I saw Tableau, I knew I had to learn it.
My first training was through the materials and videos that Tableau provides us on the internet. I was amazed at the potential that was discovered before my eyes.
Quickly what used to involve a lot of time and effort in traditional tools, could easily be improved in a short period of time with Tableau.
My second stage of training began when I became acquainted with the Community of Tableau, first with the Community of #MakeoverMonday and later with that of #Workout Wednesday.
Impossible to list each of the people who have contributed to my learning with their participation. Being able to learn from the designs they share and / or learn new techniques and tricks in Tableau has been an enriching experience.
I thank each one of you! ◕‿↼◕‿↼
To conclude and return to the challenge, I thank @RodyZakovich for sharing this interesting challenge.
If you have any questions about the blog, do not hesitate to contact me on twitter (@rosariogaunag)