The first week of 2020, Luke Stanke prepared us for #WorkoutWednesday the challenge: “Can you sort dimensions with a single click”.

The challenge seeks to improve the User Experience (UX) when selecting between different measures options to sort the information.

Main requirements of the challenge:

  • Create a sheet that acts as a heading where you will select the option to sort descending, the main table.
  • Three selection options will be used: Sales, Sales/Order and Profit Ratio.
  • The selected option must have an “▼” arrow indicating the descending order of the measure and be shown in a darker color to the rest of the options.
  • Create a sheet for the main table. This table should:
    • Include bar charts for Sales and for the Profit Ratio.
    • Include text for the Sales/Order, which should be aligned to the right.

The challenge statement led me to new questions about whether this or that is possible. When this happens to me, I seek to find where the new questions lead me.

This blog is the end result of this process and contemplates some additional requirements to the original challenge:

  • On the sheet that acts as a header:
    • A fourth option will be included: Sub-Category. Which represents the first option of a dimension name, unlike the three requested options, which deal with measure names.
    • It will be allowed, through clicks on the same option, to change the type of sorting between: Descending ▼, Ascending ▲, and clearing the sorting.
    • The respective global figures will be included in the heading: “Number of Sub-Categories”, “Sales”, “Sales / Order” and “Profit Ratio”.

I imagine that to build the Header Viz, for many of us, the first idea is to use the “Measure Names” field as a technique and update the Parameters.

For the solution that I detail below, I chose to use a different technique, looking for that: 1) In addition to using measure names, we could use dimension names; and 2) Get more control over colors, sizes, text to include, etc. of these names to build the menu.

Next, I share the steps I followed.

STEP 1. CREATE TWO PARAMETERS.

Since we will use Parameter Actions, the first thing we must do is create the parameters. For this exercise we will require two parameters: “Sort By” and “Sort Order”.

PARAMETER TYPE ALLOWABLE VALUES CURRENT VALUE
Sort By String All Sales
Sort Order String All

STEP 2. DEFINE THE DIMENSIONS TO BE USED IN THE MENU.

For each of the defined parameters, we require an associated dimension that contains the possible selection options and that will be displayed in the header.

DIMENSION VALUES
Menu Sort By “Sub-Category”, “Sales”, “Sales/Order” y “Profit Ratio”
Menu Sort Order “▼”  => For Descending
“▲”  => For Ascending
“ ”  => A blank space to clean or deselect.

To create the two necessary dimensions, we apply a small trick, which consists of creating the two new dimensions from another field, knowing in advance that there is no relationship between the created dimensions and the original data.

Menu Sort By CASE DATEPART(“day”, [Order Date]) % 4
WHEN 0 THEN “Sales”
WHEN 1 THEN “Sales/Order”
WHEN 2 THEN “Profit Ratio”
WHEN 3 THEN “Sub-Category”
END
Menu Sort Order CASE DATEPART(“month”, [Order Date]) % 3
WHEN 0 THEN ” ”
WHEN 1 THEN “▼”
WHEN 2 THEN “▲”
END

For example, to create the “Menu Sort Order” dimension with 3 possible values, we are using an existing dimension: the months of the year, arbitrarily grouping the 12 months, into three groups, one for each required value in the new dimension.

This technique is known as “Internal Data Densification” and to be used it is first necessary to validate that certain requirements are met. To learn more about this technique, consult the article published by Klaus Schulte and me.

STEP 3. CALCULATE THE “FILTER” DIMENSION FOR THE HEADER SHEET.

By combining the 4 values of the “Menu Sort By” dimension and the 3 values of “Menu Sort Order” we have as a result 12 marks. It’s required to filter the data to select the 4 marks to be displayed in the Header Menu, according to the values of the Parameters.

Filter ([Menu Sort By] = [Sort By] AND [Menu Sort Order] = [Sort Order]) OR
([Menu Sort By] <> [Sort By] AND [Menu Sort Order] = ” “)

Below is a table with the 12 possible combinations and in green the 4 combinations that meet the filter conditions are highlighted considering the initial values established for the two parameters of step 1 (“Menu Sort By” = “Sales ”  and  “ Menu Sort Order ”= ▼).

Picture02STEP 4. CALCULATE THE DIMENSIONS ASSOCIATED AT THE LEVEL OF “SORT ORDER” TO BE APPLIED.

There are three possible levels or values in the “Sort Order” parameter. Its value will determine the calculation of the following four dimensions.

Adjust Sort By IIF([Sort Order] = ” “, ” “, [Sort By])
Arrow? IIF([Menu Sort By] = [Adjust Sort By], [Sort Order], ” “)
Menu Color [Arrow?] <> ” “
Next Sort Order IIF([Menu Sort By] <> “Sub-Category”,
CASE [Menu Sort Order]
WHEN ” ” THEN “▼”
WHEN “▼” THEN “▲”
WHEN “▲” THEN ” ”
END,
CASE [Menu Sort Order]
WHEN ” ” THEN “▼”
ELSE ” ”
END)

STEP 5. CALCULATE THE AMOUNTS TO BE DISPLAYED IN THE HEADER.

In this exercise we will define 4 measures, which allows us to customize the format of each of the measures: Sign format, unsigned format, percentage format, or millions format.

Global Sub-Category IIF([Menu Sort By] = “Sub-Category”,  { COUNTD([Sub-Category]) }, NULL)
Global Sales IIF([Menu Sort By] = “Sales”, { SUM([Sales]) }, NULL)
Global Sales/Order IIF([Menu Sort By] = “Sales/Order”,  { SUM([Sales]) / COUNTD([Order ID]) }, NULL)
Global Profit/Ratio IIF([Menu Sort By] = “Profit Ratio”,  { SUM([Profit]) / SUM([Sales]) }, NULL)

Why we are using LOD calculations? Because when one of the dimensions created under the technique of “Internal Data Densification” is used in a Viz in combination with the result of calculations of one or more measures, the use of LOD Calculations is required to avoid or evade arbitrary clusters or sub-data sets generated dimensions that create under technique.

In this exercise I use LOD calculations fixed to work with complete data from the data source.

STEP 6. CREATE THE HEADER FOR GRAPHIC (NAME: HEADER).

Include filter by the “Filter” field selected the value of TRUE.

Move to shelves of:

  • Columns ⬅   “Menu Sort By”
  • Color ⬅   “Menu Color”
  • Detail ⬅   “Next Sort Order”
  • Text ⬅   “Menu Sort By”
  • Text ⬅   “Arrow?”
  • Text ⬅   “AVG(Global Sub-Category)”, “AVG(Global Sales)”,  “AVG(Global Sales/Order” & “AVG(Global Profit/Ratio)”

Adjust colors, formats and font sizes.

STEP 7. CALCULATE THE VALUES OF “SALES / ORDER” AND “PROFIT RATIO” AND DETERMINE IF THE PROFIT RATO IS POSITIVE.

The second graph will not use any of the dimensions created under the “Internal Data Densification” technique, therefore, the definition of the numerical calculations to be used in the second graph doesn’t require any special consideration.

Sales/Order SUM([Sales]) / COUNTD([Order ID])
Profit Ratio SUM([Profit]) / SUM([Sales])
Bar Color [Profit Ratio] >= 0

STEP 8. CALCULATE THE “SORT VALUE” ACCORDING TO THE OPTIONS SELECTED BY THE USER.

Sort Value CASE [Adjust Sort By]
WHEN “Sales” THEN SUM(Sales)
WHEN “Sales/Order” THEN [Sales/Order]
WHEN “Profit Ratio” THEN [Profit Ratio]
ELSE INDEX()
END *
IIF([Sort Order] <> “▼” OR [Adjust Sort By] = ” “, 1, -1)

It’s important to highlight:

  • That to sort by the name of the Sub-Category, we use the INDEX () function, which allows us to have a numerical value to sort by the dimension field.
  • And that, to exchange, between ascending or descending ordering a field, we must only multiply the values, by 1 or by -1, as required.

STEP 9. DEFINE A DIMENSION TO CLEAN SORT OPTIONS FROM THE MAIN SHEET.

Reset “ “

STEP 10. CREATE THE GRAPHICS FOR THE MAIN SHEET (NAME: TABLE)

Move to shelves of:

  • Columns ⬅   “Sales”, AVG(0) & “Profit Ratio”
  • Rows ⬅   “Sort Value” hide header (discrete value)
  • Rows ⬅   “Sub-Category”

For the SUM(Sales) axis apply bar chart type:

  • Detail ⬅   “Reset”
  • Color ⬅   “Bar Color”
  • Text ⬅   “SUM(Sales)”

For the AGG(AVG(0)) axis apply circle chart type:

  • Detail ⬅   “Reset”
  • Text ⬅   “AGG(Sales/Order)”

For the AGG(Profit Ratio) axis apply bar chart type:

  • Detail ⬅   “Reset”
  • Color ⬅   “Bar Color”
  • Text ⬅   “AGG(Profit Ratio”

Adjust colors and formats.

STEP 11. DEFINITION OF PARAMETER ACTIONS.

Create a Dashboard that includes the two graphs created and define the Parameter Actions.

PARAMETER ACTION Source Sheet TARGET PARAMETER FIELD OR VALUE
Sort By Header Sort By Menu Sort By
Next Sort Order Header Sort Order Next Sort Order
Reset Sort By Table Sort By Reset

Ready!

The latest version is here.

Jan-05-2020 19-46-56

SUMMARY

When the Parameter Actions were introduced by Tableau, one of the widely shared techniques for its use, it was to use the “Measure Names” field within a Viz to update a Parameter. This technique opened new possibilities for interaction in the Viz, or as Luke comments, to spice up our classic graphics.

Looking for new alternatives to improve the User Experience (UX), another technique is presented to update a Parameter with the name of the fields in the data table. Which consists of using a dimension created with the names of the measures and/or dimensions, instead of directly using the “Measure Names” field to update a Parameter.

To create the dimension with the names of the measures and/or dimensions, the “Internal Data Densification” technique is used that allows us to create new dimensions from existing fields in the table under the fulfillment of certain conditions.

Using a dimension, instead of directly using the “Measure Names” field to update a Parameter, offers the following advantages:

  • Can be used interchangeably for dimension names or for measure names.
  • Allows you to combine the selection between dimension names and measure names in the same options menu.
  • It allows to obtain greater control over the format of the menu options, to establish different font colors, background colors, font sizes, etc., depending on which options meet certain conditions or not.
  • It allows in the same mark to easily combine the dimensions that represent the names of the measures and/or dimensions, with other dimensions. In this exercise the dimensions were combined:
    • “Sort By” representing the name of the measures and/or dimensions, with
    • “Sort Order” to indicate the type of ordering.
  • By combining more than one dimension per “mark”, it’s possible to activate more than one “Parameter Action”.
  • Being able to update more than one Parameter with each “mark” allows you to configure different actions or behaviors depending on the number of consecutive clicks on the same “mark”. In this exercise it was configured:
    • First click. Indicates that the information will be sorted descending, taking the measure or dimension selected by the user.
    • Second click. Indicates that the information will be sorted ascendingly.
    • Third click. Indicates that the measure or dimension is deselected or that the sort option is cleared.

Thank you very much and if you have any questions about the post, do not hesitate to contact me on Twitter (@rosariogaunag).