Tag: Dummy Axis

Can you label & sort small multiples?

Donna ColesLeave a comment

A colourful #WOW2022 challenge this week set by Kyle Yetter and using his favourite data – Baseball. Let’s jump straight in.

Building the required calculations

First up we need to calculate the core measure the viz is based on – % of wins

Win %

SUM([Wins])/SUM([Games])

I formatted this to 3 decimal places, then applied a custom number format to remove the leading 0 (custom number format looks like ,##.000;-#,##.000).

We also need to know the number of losses as this is part of the tooltip.

Losses

SUM([Games]) – SUM([Wins])

Let’s pop all these out into a table (I formatted all the whole numbers to display without any decimal places).

The viz however isn’t plotting the actual Win%, it’s plotting the difference from 50% (or 0.5), so values less than 50% are negative and those above are positive.

Plot Postion

[Win %] – 0.5

And we also need to know whether the Win% is above 50% or not

Above 50%

[Win %]>0.5

Pop these out onto the table too

The viz also displays the overall Win% for each team, and also uses this to sort the data. As it is used for sorting, we need to use an LoD calculation (rather than a table calculation).

Overall Win% LOD

{FIXED [Team]:SUM([Wins])} / {FIXED [Team]: SUM([Games])}

for each team, get the total wins, and divide by the total games for the team. Format this to 3 dp with no leading 0 as before.

pop this into the view (you’ll see it’s the same value for each row for a single team), and then apply a Sort on the Team field to sort descending by the Overeall Win% LOD.

Now we have the data sorted, we can create the fields needed to build the trellis chart.

I have already blogged challenges relating to trellis charts / small multiples (see here) which in turn reference other blogs in the community, so I’m not going to go into all the details. We just need to build two calculated fields to identify which row and which column each Team will sit in. The table is fixed at 6 columns wide as the data wea re using is static. Some solutions work with a more dynamic layout depending on how many entities you need to display. We’re keeping things simpler.

Cols

FLOAT(INT((INDEX()-1)%6))

Rows

FLOAT(INT((INDEX()-1)/6))

Add both these fields to the table as discrete dimensions (blue pills), and as they are both table calculations, set them both to Compute Using – > Team.

Building the Core Viz

On a new sheet, add Cols to Columns as discrete dimension, Rows to Rows as discrete dimension and Team to Detail. Set both Rows and Cols to Compute Using Team.

Add Year as continuous (green) pill to Columns and Plot Position to Rows and change the mark type to Bar and reduce the size. Sort the Team field based on Overall Win% LOD descending.

Add Wins, Losses, and Win% to the Tooltip shelf and adjust the tooltip to display as required. Add Above 50% to the Colour shelf (you may need to readjust the size). Leave the colours as they are for now – we’ll deal with this later.

Adding the labels

Create a new calculated field

Dummy Plot

FLOAT(IF [Year]=2000 OR Year = 2020 THEN 0.35 END)

This is basically going to position a mark at height 0.35 but only if the year is either 2000 or 2020. These values were all just based on a bit of trial and error as to what worked to get the desired result.

Also create a field

LABEL:Team

IF [Year]=2000 THEN [Team] END

and

LABEL:Win%

IF [Year]=2020 THEN [Overall Win % LOD] END

format this to 3dp and exclude the leading 0.

Add Dummy Plot onto Rows and change the mark type of this measure to circle. Amend the Tooltip of this marks card so it’s empty.

Add LABEL:TEAM and LABEL:Win% to the Label shelf, and adjust the label so both fields sit side by side (only 1 value will only ever actually display). Adjust the table calculation of both the Rows and Cols pills so they now compute using both the Team and the LABEL:Team fields.

Adjust the alignment of the labels so they are positioned bottom centre. Set the font colour to match mark colour and bold.

Then reduce the size of the circle mark to as small as possible, reduce the opacity of the mark colour to 0.

Now make the chart dual axis and synchronise the axis. Remove the Measure Names field that has automatically been added to the All marks card.

Hide all the headers and axis (uncheck Show Header), remove all grid lines, zero line, axis rulers.

Hide the null indicator (bottom right).

Colouring by Team

Copy the colour palette text Kyle provided into your preferences.tps file (usually located in the My Tableau Repository directory). For more information on working with custom colour palettes see this Tableau help article.

You’ll need to save your workbook and re-open for the new palette to be available for use.

In order to prevent having to manually set all the colours (and believe me you don’t want to do this!), perform the following steps in order

  • Add Team to also be on the Colour shelf. Click on the 3 dots (…) that are to the left of the Team pill on the All marks card, and change it to Colour. This means there are now 2 fields on colour. Move the Team field so it is listed above the Above 50% pill. This means your colour legend should be listed as <Team>, <True|False>
  • Adjust the Sort of the Above 50% pill, so it is manually sorted to list True before False.

  • Now change the Sort on the Team field so it is sorted alphabetically ascending instead. This will cause the viz to change its sort order, but don’t worry for now. It also changes the list on the colour legend, so ARI, True is listed first then ARI, False etc.

  • Now edit the Colour Legend and select the new MLB Team Colours palette we added. Click the Assign Palette button to automatically assign the colours. As we’ve made sure the entries listed are in the right order, they should get the correct colours.

  • Change the Sort on the Team field back to be based on Overall Win% LOD descending

And that should be it. You can now add the viz to a dashboard and publish. My published version is here.

Happy vizzin’!

Donna

Advertisement

Customer Purchasing Habits (RFM Analysis)

Donna ColesLeave a comment

Ann set this week’s #WOW2021 challenge during #TC21 and chose to live stream her build. I couldn’t watch it but I did then manage to catch a snippet of Kyle Yetter who bravely chose to share his attempt at recreating the challenge via a live stream too with Ann & Luke watching. I had already completed my build by the time I watched Kyle, and it was interesting to see where our approaches differed.

The main via is a single chart, so I started by building out all the data I needed in tabular form, so I could verify the sort.

Defining all the calculations for the main viz

First up, the data needs to be compared to ‘today’ where ‘today’ is set to 01 Jan 2022. I used a parameter to store this

pToday

Date constant set to 01 Jan 2022

We also need to understand the latest order date per customer, so need

Max Order Date By Customer

DATE({FIXED [Customer ID]: MAX([Order Date])})

and so with this we can calculate the days since last order, which is one of our key measures.

Days Since Last Order

DATEDIFF(‘day’, [Max Order Date By Customer], [pToday])

The other measures we need are

#Orders

COUNTD([Order ID])

Avg. Order Value

SUM([Sales])/[#Orders]

along with Sales and Quantity.

These are the 5 main measures displayed, but there are some additional calculations needed for the display labels and tooltips.

The label to indicate the ‘time since last purchase’ is displayed in days or years, so for this I created 2 specific fields

LABEL:Days Since Last Order

IF [Days Since Last Order] <365 THEN [Days Since Last Order] END

This will only store a value when the days is less than 365. I then formatted this field to have a ‘ days’ suffix

Similarly, I created

LABEL:Years Since Last Order

IF [Days Since Last Order]>=365 THEN [Days Since Last Order]/365 END

which stores a value for records >=365 only. This was formatted to have 1 dp and the ‘ years’ suffix instead.

The colour of the first measure is based on whether the customer is considered ‘active’ or not. The threshold for this is managed via a parameter

pActiveCustThreshold

An integer parameter defaulted to 90.

We can then create

Is Active Customer?

[Days Since Last Order]<=[pActiveCustThreshold]

This is a boolean field and will return true or false, but to get the tooltip value to display appropriately, I edited the alias of this field (right click > Aliases)

For the tooltip on the ‘total products’ (ie quantity measure), we need to display the number of distinct products orders, which we can capture in

#Products

{FIXED [Customer ID]: COUNTD([Product ID])}

Pop all these fields, along with the Customer ID and Customer Name into a table and you can see validate all the values are as you expect

Sorting the Sort

We need another parameter to manage the sort.

pSort

I used an integer parameter, but set the display to the required text strings. This is because writing logic on integers is more efficient than strings

I then created a calculated field to determine the measure to sort by

Sort By

CASE [pSort]
WHEN 1 THEN SUM([Days Since Last Order]) * -1
WHEN 2 THEN [#Orders]
WHEN 3 THEN SUM([Sales])
WHEN 4 THEN [Avg. Order Value]
WHEN 5 THEN SUM([Quantity])
END

Notice the first measure is being multiplied by -1 since this measure is to be displayed in Ascending order rather than Descending.

In the table, amend the Customer ID pill to Sort by the Sort By field descending

Show the pSort parameter and test the functionality by switching the values.

Building the main viz

Having got all the calcs defined, this viz is relatively straight-forward. It is a multiple axis viz by Customer ID and Customer Name (note I use Customer ID as well, which I hide, just in case there are multiple customers with the same name).

  • Customer ID and Customer Name on Rows. Apply sort to Customer ID as described above. Hide Customer ID (uncheck Show Header). Align Customer Name to right, and adjust font.
  • Type in MIN(1) on Columns. Edit Axis to be fixed from 0-1. Add Is Active Customer? to Colour. and adjust. Add LABEL:Days Since Last Order and LABEL:Years Since Last Order to Label. Align Centre and match mark colour. Make sure the two fields are side by side on the label, and not underneath each other. Add Max Order Date By Customer to the Tooltip shelf. Adjust Tooltip
  • Add #Orders to Columns. Strip off all the fields that have been automatically added to this marks shelf. Set the Colour accordingly. Show the Label at the end of the bar. Adjust Tooltip
  • Add Sales to Columns. Set the Colour accordingly. Show the Label at the end of the bar. Adjust Tooltip
  • Add Avg. Order Value to Columns. Set the Colour accordingly. Show the Label at the end of the bar. Adjust Tooltip
  • Type in MIN(0) on the Columns shelf. Change mark type to Circle. Set the Colour accordingly. Add Quantity to Label. Add #Products to Tooltip. Adjust Tooltip
  • Remove all row & column borders and gridlines, zero lines etc.
  • Add a 0 constant Reference Line to the #Orders, Sales and Avg. Order Value axes.
  • Hide the axes, hide field labels for rows.

Building the Viz in Tooltip

The Viz in Tooltip shows the Top 10 Products per Customer by Sales.

On a new sheet, add Customer ID, Customer Name and Product Name to Rows and Sales and Quantity (via Measure Values) to Text.

Add a Rank Quick Table Calculation to the Sales pill, then edit the table calc to be unique and to compute by Product Name

Click on the Sales rank pill , press Ctrl and drag it into the measures pane. Name the field Sales Rank. Format the field to have a # prefix. Add Sales back into the view.

Now drag the Sales Rank field from the Measure Values section and add to Rows, then change to be a discrete (blue) field, then move so it is to the left of the Product Name field. This should cause everything to re-sort into the required order automatically. It’s always worth double checking the table calc is still computing as expected.

Click on the Sales Rank pill and press Ctrl and this time drag to the Filter shelf and check values 1 to 10.

Now hide the Customer ID and Customer Name fields, and format the display.

Finally, set the display to Entire View. This will make the ‘view’ unreadable, but is necessary to ensure you don’t get a ‘viz is too large to display’ message when you get to add the Viz to the tooltip.

Adding the Viz in Tooltip

Back to the chart sheet, and edit the tooltip of the MIN(0) marks card. Insert the Top 10 sheet via Insert > Sheets. Adjust the width and height values and change the filter to just use <Customer ID>

Hovering on the circle mark should then display the Top 10 Products for that specific customer.

Adding the column headings

On the dashboard, use a horizontal layout container above the main viz. Add 6 text objects, enter the relevant text and centre. Adjust the width of each text object to line up with each column.

The help icon

Add a floating text object to the sheet – it’ll appear over the top of the chart. Position the text object to the top left, and set the background to white. Enter the text. From the context menu of the object, select the Add Show/Hide Button. Position the button in the appropriate location and resize. Edit the button options to have a grey background and show ? when hidden and X when shown.

I think that’s covered all the core points. My published viz is here.

Happy vizzin’! Stay Safe!

Donna