After the challenge launched the week 13 by Luke Stanke in #WorkoutWednesday, Ann Jackson did not want to be left behind and challenges us to join her in an adventure to the dark side.

WoW! Promise of assured fun.

The challenge comes from the quest to design a Viz that allows for more user-directed comparisons and to keep the information of other peers in the background, so that the user should know what is being clicked and more information should appear more information of support in the context.

In this exercise we will work with SuperStore’s 2018 data for the “Office Supplies” category, where we want to have two control panels that dynamically highlight two of the subcategories of “Office Supplies” to compare them, and reveal their monthly averages.

That is, when a Sub-Category of the top control panel is clicked, the Sub-Category chosen should be highlighted in the main chart and a line with its average will appear. This selection must also modify both control panels: 1) In the top panel the chosen Sub-Category should move to the left  and change color and 2) In the bottom panel the Sub-Category chosen in the top panel should disappear.

This same logic should be applied when a Sub-Category of the bottom control panel is selected, only with some adjustments such as the use of another color.

Next, I share the steps that I followed.

Step 1: Create extract of the selected data the Category of “Office Supplies”.

Step 2: Define the variable “Upper Sub-Category”.

Upper Sub-Category = UPPER([Sub-Category])

Step 3: Define Sets.

In order to identify which Sub-Category was selected in each of the control panel menus, we will use “Sets” and “Set Actions”. In this step we will define a “Set” for each of the control panels. Both “Sets” will be created from the variable defined in the previous step.

To create the “Sets” you only need to position yourself in the “Upper Sub-Category” field and with the right click select the “Create Set” option. To identify each of the “Sets” use the names of “Top Menu Set” and “Bottom Menu Set”.

Picture02Step 4: Create the “Month” variable.

The chart shows the monthly information, for which a new variable would be defined that allows grouping the dates belonging to the month.

Month = DATE(DATETRUNC(“month”, [Order Date]))

Another way to achieve this is to select the “Order Date” field and with the right click, choose “Create Custome Date” at the month level.

Step 5: Calculate the average monthly sales, in case the Sub-Category has been selected by the user.

Average Sales =

IIF([Top Menu Set] OR [Bottom Menu Set],

{FIXED [Sub-Category] : AVG(

{FIXED [Month], [Sub-Category]: SUM([Sales])})}, NULL)

The internal LOD calculation {FIXED [Month], [Sub-Category]: SUM ([Sales])} allows us to obtain the value of the monthly sales of each Sub-Category.

The external LOD calculation allows us to obtain the average value of the monthly sales of each sub

Step 6: Define the variable for the color.

Three colors will be used, depending on whether the Sub-Category has been selected in the upper menu or in the bottom menu or if it has not been selected.

Color =

IIF([Top Menu Set], “1-Top”,

IIF([Bottom Menu Set], “2-Bottom”, “3-Others”))

Step 7: Create variable that will help us with the labels in the Viz.

Label Name =

IIF([Top Menu Set] OR [Bottom Menu Set],

UPPER([Sub-Category]), NULL)

Label Average Sales =

IIF(DATEPART(“month”,[Order Date]) = 6,

[Average Sales], NULL)

Step 8: Create the main chart.

Two-line chart with synchronized double axis will be used.

Define the chart:

  • Columns ⬅   “Month” use “Exact Date” and “Continuous”
  • Rows ⬅   “SUM(Sales)” and “AVG(Average Sales)”
  • Color ⬅   “Color”
  • Detail ⬅   “Upper Sub-Category”
  • Label ⬅   “Label Name” for the main axis and “Label Average Sales” for the secondary axis

Picture03Step 9: Create the top control panel.

A bar type chart will be used.

Define the chart:

  • Filters ⬅   “IN/OUT(Bottom Menu Set)” = “Out”
  • Columns ⬅   “IN/OUT(Top Menu Set)” and “Upper Sub-Category”
  • Rows ⬅   AVG(1) the axis is adjusted between 0 to 1
  • Color ⬅   “Color”
  • Label ⬅   “Upper Sub-Category”

Picture04Step 10: Create the bottom control panel.

A bar type chart will be used.

Define the chart:

  • Filters ⬅   “IN/OUT(Top Menu Set)” = “Out”
  • Columns ⬅   “IN/OUT(Bottom Menu Set)” and “Upper Sub-Category”
  • Rows ⬅   AVG(1) the axis is adjusted between 0 to 1
  • Color ⬅   “Color”
  • Label ⬅   “Upper Sub-Category”

Picture05

Step 11: Join the charts in a Dashboard and define the “Set Actions”.

Picture06

Ready!

Link to Tableau Public

Picture01I want to thank Ann Jackson for inviting us to join her in this fun adventure on the dark side. Let’s remember: No Pain, No Gain!

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

Regards!

Rosario Gauna

Advertisements