Challenge, Modeling, Join, Actions Filter, UX, Workout Wednesday

By: Rosario Gauna @rosariogaunag

In Week 29 of Workout Wednesday, @RodyZakovich prepared us a challenge that, through the use of data modeling and “Actions Filters”, allows us to create additional User Experience (UX) functionalities that are not available natively in the product. Link to the challenge

In the challenge, Rody asks us that when a user moves over any Category, all the years of the Category must be connected by a line in ascending order of the year.

It seems simple, but the challenge is to achieve this effect, without the brands of the other Categories being affected (that is, they must not disappear or change their tonality).

Next, I share the procedure that I used:

Step 1: Model the data – Create summary table of sales and profit by Year and Category.

Why is it required to create a summary table?

The goal is to create a table with the minimum number of records required to create the visualization. In this exercise, the summary table will contain 12 rows (4 Years x 3 Categories), one for each “Mark” that will be presented in the Viz.

This table will be basic in the second stage of modeling.

To create the summary data table, you can use Tableau Prep or Tableau Desktop (with an export to crosstab to Excel) or any other tool you prefer.

2

Step 2: Modeling the data – Join.

The second stage of the modeling consists in making a “Join” of the summary table created in step 1, with itself, using the year to join them.

3

What is the use of data joining?

The Join allows us for each of the 12 original records of the table created in step 1, to unite it with the three records that correspond to the same year.

The result table of the Join contains 36 rows:

(4 Years x 3 Categories) x (3 Records corresponding to the same year, one for each Category).

This triplicity in the data resulting from the “Join”, will allow us to later use an “Action Filter” in the query of the “Viz” and always have available at least 12 of the 36 records, one for each “Mark” of sales per Year-Category.

Step 3: Model the data – Rename fields and hide fields.

Of the 4 fields in the table to the right of the Join, only the variable “Category” is required, which we will rename as “Category B”, the other three variables can be hidden.

4

Step 4: Define the “Action Filter” for the worksheet

Before creating the “Action Filter” we must move to the shelf of:

  • Detail ⬅  “Category”

5

It is important to highlight that the value of the “Category” selected by the user with the “Hover” will be translated into an “Action Filter” in the “Category B” field.

If the “Action Filter” is found:

  • OFF: You will be working with the 36 records resulting from the Join.
  • ON: Rows will be filtered and only the 12 records that in the “Category B” field will be selected by the user in the “Hover” will remain selected.

Let’s see two examples, depending on whether the “Action Filter” is OFF or ON.

6

Step 5: Calculate the variable “Sales Line”

This calculation will provide us with the values to link all the points of the same category through a line.

This variable will only have value, when two conditions are simultaneously fulfilled, otherwise, its value will be Null.

  • Condition 1: That the “Action Filter” is “ON”.
  • Condition 2: That the values of “Category” and “Category B” are the same.
Sales Line =

IIF ({EXCLUDE [Category B]: COUNTD([Category B])} = 1 AND

[Category]=[Category B], [Sales], NULL)

Step 6: Create the graph.

It is important to clarify that in the graph I use the aggregation of “AVG” to present the variables of “Sales” and “Profit”. Why?

Remember that if the “Action Filter” is OFF, you are working with the data in triplicate, that is, with the 36 records that result from the Join. So, if we use the aggregation of default of “SUM”, the values of each Year-Category would be tripled.

To obtain the figure without duplicity, aggregations of “AVG” or “MIN” or “MAX” can be used with the same results.

Example: AVG (157193, 157193, 157193) = 157,193

The definition of the graph is as follows:

8

Step 7: To conclude adjust the Tooltips and formats.

Ready!

Link to Tableau Public

1

Thanks to @RodyZakovich, his latest challenges have been very valuable in learning about techniques to improve the end-user experience (UX).

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

Regards!

Rosario Gauna

Advertisements