Tag: Dual Axis

Can you show the difference from selected sales?

Donna ColesLeave a comment

For this week’s challenge, Lorna asked us to complete the challenge by not using LoDs or Table Calcs. Parameters and parameter actions were necessary though (originally I started without using them either, as I misread the information).

Building the core bar chart

As mentioned, we’re going to use parameters to capture the info we need. So start by creating

pSelectedSubCat

string parameter to store the name of the Sub-Category selected – default to Storage

pSelectedSales

float parameter to store the Sales value of the selected sub category. Set this to 224,645 which is the value associated to Storage and set the display format to $ with 0dp.

The plan is that when ‘No Comparison‘ is selected, the pSelectedSubCat will contain nothing ie an empty string of ” “, and pSelectedSales will be 0.

Based on this, we need to define the value to display in the bar, which typically is the difference between the Sub-Category sales and the sales of the pSelecedSubCat (ie the value in pSelectedSales). But in the event No Comparison is selected, we just want the sales. So create

Difference

IIF([pSelectedSales]>0,SUM(Sales)-[pSelectedSales],SUM([Sales]))

is if we have a value in pSelectedSales, return the difference between the Sales value and it, otherwise just return Sales.

Set a custom number format of “$”#,##0;-“$”#,##0;””

Note the last setting after the 2nd “;” is the formatting for a 0 – in this case I’ve set it to ” ” ie nothing/<empty string>, so a value won’t get displayed against the bar of the selected Sub Category

Add Sub-Category to Rows and Difference to Columns. Explicitly sort the Sub–Category pill to be sorted by Sales descending

Show mark labels, and widen the bars a bit. Adjust bar colour as required. Add a column grand total, and display at the top (Analysis menu > totals > show column grand totals, then Analysis menu > totals > column totals to top)

We’ve done this as we need the additional row at the top of the chart to align with the ‘No Comparison’ option in the selector we’ll build, but we don’t want the bar to show. To get rid of it, click on the bar and then select Hide from the ‘automatic’ drop down

which gives us

Hide the Sub-Category row heading and axis (uncheck show header from the pills). Remove all gridlines, row/column dividers and axis rulers/zero lines etc.

We’ll come back to this sheet later.

Building the selector sheet

We’re going to build this using a dual axis of a bar chart and a shape.

On a new sheet, add Sub-Category to Rows and again sort by Sales descending.

Double click into Columns and type MIN(-1.0)

Change the mark type to bar and widen each row a little. As before, add Column Grand Totals to the top.

Create a new field

LABEL: Sub Cat

IF MIN([Sub-Category])<>MAX([Sub-Category])
THEN “No Comparison” ELSE ATTR([Sub-Category])
END

If we just add Sub-Category to the label, the grand total row will show as ‘All’. The above is a sneaky way to change the word ‘All’, as in the ‘grand total’ row, all the Sub-Categories are ‘known about’, so the MIN(Sub-Category) and MAX(Sub-Category) are different.

Add this field to the Label shelf, and align left centre (the axis is -ve, so the alignment has to be to the left, even though it’s displayed on the right).

We need to be able to identify which row (including the grand total row) has been ‘selected, so create

Is Selected SubCat

ATTR([Sub-Category]) = [pSelectedSubCat] OR ([pSelectedSubCat]=” AND [LABEL: Sub Cat ] = ‘No Comparison’)

And add this to Colour, and colour the True to match the bar chart colour you chose, and Null and False to white (so it ‘disappears)

Now create a secondary axis by double clicking into Columns and type MIN(-0.9). Change the mark type to Shape. Remove Label: SubCat from the marks card. Create a duplicate of Is Selected SubCat so you have Is Selected Subcat (copy) and add this field to shape and to Colour and set the colour and shape as required

Make the chart dual axis and synchronise the axis. Hide all the axis and the row headings and remove all row/column dividers, gridlines etc.

Building the dashboard and adding the interactivity

Create a dashboard and using a horizontal layout container arrange the Selector sheet and the Bar sheet side by side, ensuring both ‘fit entire view, which will make sure the rows all align with each other.

We need to change the values of the pSelectedSubCat and pSelectedSales parameters on click of either chart. When we do this, we need to pass the relevant values into the parameter. Because we also have to handle the ‘grand total’ row, we need some additional fields for this

Sub Cat for Param

IIF([LABEL: Sub Cat ]=’No Comparison’,”,ATTR([Sub-Category]))

ie use ‘nothing if the ‘grand total’ is clicked, otherwise use the Sub-Category

similarly

Sales for Param

IIF([LABEL: Sub Cat ]=’No Comparison’,0,SUM([Sales]))

ie use 0 f the ‘grand total’ is clicked, otherwise use the Sales value of the Sub-Category

Add both these pills to the Detail shelf of the All marks card on the Selector sheet, and to the Detail shelf on the Bar sheet.

On the dashboard, create a dashboard parameter actions

Set Sales Value

On select of either sheet, set the pSelectedSales parameter, passing in the value from the Sales for Param field aggregated at the SUM level. Set to 0 when selection cleared.

Set Sub Cat

On select of either sheet, set the pSelectedSubCat parameter, passing in the value from the Sub Cat for Param field. Set to “” when selection cleared.

Finally, as we are already differentiating the ‘selection’ through different colours, we don’t want the click to ‘hihglight’ the mark. Create a new field

Dummy

“HL”

and add this to the Detail shelf on both sheets

Then create a dashboard Highlight action

Highlight

On select of either sheet, target either sheet but only with the selected Dummy field.

And that in principle should give you a functioning solution. The only extension I made, as to make the Tooltips on the bar chart make sense depending on what was being viewed. This involved building up a series of Tooltip Text fields. Check out my solution if you need to see the details.

My published viz is here

Happy vizzin’!

Donna

Fun with Gantt Charts

Donna ColesLeave a comment

Sean used some Super Bowl data for this week’s #WOW2026 challenge , using Gantt charts to display ‘arrowed bars’.

Creating the calculations

It took me a little bit of time to understand what I needed from the data to create the required visualisation, so let’s tackle that first.

We need to know the Season and the number of the Super Bowl in roman numerals. The Super Bowl field has this info as it displays <Super Bowl Number as integer> (<year>) ie 4 (1970) – the 4th Super Bowl for Season 1970.

To quickly generate the data I needed, I used the Split function on the Super Bowl. field (Right click > Transform > Split). This automagically generates fields Super Bowl – Split 1 containing the numeric number and Super Bowl – Split 2 containing the year.

I simply renamed Super Bowl – Split 2 to Season, but for completeness, this is what the calculated field looks like

Season

TRIM( SPLIT( SPLIT( [Super Bowl], “(“, 2 ), “)”, 1 ) )

I then renamed Super Bowl – Split 1 to SB Number (int) and changed the data type from string to whole number. Again the field looks like

SB Number (int)

INT(TRIM( SPLIT( [Super Bowl], “(“, 1 ) ))

But I want the number in the format SB + roman numerals. A quick search on the web, and I found the required logic needed to make the conversion, so I created

SB #

“SB ” +
// Thousands Place
CASE INT([SB Number (int)] / 1000)
WHEN 1 THEN “M” WHEN 2 THEN “MM” WHEN 3 THEN “MMM” ELSE “”
END +

// Hundreds Place
CASE INT(([SB Number (int)] % 1000) / 100)
WHEN 1 THEN “C” WHEN 2 THEN “CC” WHEN 3 THEN “CCC” WHEN 4 THEN “CD”
WHEN 5 THEN “D” WHEN 6 THEN “DC” WHEN 7 THEN “DCC” WHEN 8 THEN “DCCC”
WHEN 9 THEN “CM” ELSE “”
END +

// Tens Place
CASE INT(([SB Number (int)] % 100) / 10)
WHEN 1 THEN “X” WHEN 2 THEN “XX” WHEN 3 THEN “XXX” WHEN 4 THEN “XL”
WHEN 5 THEN “L” WHEN 6 THEN “LX” WHEN 7 THEN “LXX” WHEN 8 THEN “LXXX”
WHEN 9 THEN “XC” ELSE “”
END +

// Ones Place
CASE INT([SB Number (int)] % 10)
WHEN 1 THEN “I” WHEN 2 THEN “II” WHEN 3 THEN “III” WHEN 4 THEN “IV”
WHEN 5 THEN “V” WHEN 6 THEN “VI” WHEN 7 THEN “VII” WHEN 8 THEN “VIII”
WHEN 9 THEN “IX” ELSE “”
END

Let’s add the fields we need into the table; add Season and SB# into Rows and sort Season to be listed descending. Then add O/U Line to Rows as a discrete dimension (blue dis-aggregated pill).

Note – I added the results of the 2026 Super Bowl into the provided data set.

We need to compare the O/U Line number with the combined score of the match. The score can be found in the Final field which has info of the winning team and score eg for PHI 40-22, the combined score is 40+22 = 62. I again used the Split functionality to extract the data needed.

The automatically created field Final – Split 2 contains the 2nd half of the score. I renamed this and changed the data type to a whole number

Result Part 2

INT(TRIM( SPLIT( [Final], “-“, 2 ) ))

I then ‘split’ Final – Split 1 again. The automatically created field Final – Split 1 – Split 2 contains the 1st half of the score. I again renamed and changed data type to int.

Result Part 1

INT( SPLIT( [Final – Split 1], ” “, 2 ) )

I then created

Combined Score

[Result Part 1] + [Result Part 2]

and

O/U Difference

[Combined Score]-[O/U Line]

Pop these onto the table along with the Result field and we have all the fields we need to build the viz

Building the viz

ON a new sheet, again add add Season and SB# into Rows and sort Season to be listed descending. Then add O/U Line to Rows as a discrete dimension (blue dis-aggregated pill). Add Combined Score to Columns and change the mark type to shape. Add Result to Shape and set the filled arrows shapes accordingly. And add Result to Colour and adjust that too.

Add O/U Line to Columns which will create a 2nd marks card. Click on this marks card, and change the mark type to Gantt Bar. Remove the Result pill that is now listed on the Detail shelf (keep the one on Colour). Add O/U Difference to the Size shelf, and then click on the Size shelf, and reduce the width a little.

Make the chart dual axis and synchronise the axis. Remove the Measure Values pill from the colour shelf of the All marks card.

Now add O/U Difference to Columns and change the mark type to bar. Remove the Result pill from this card. Adjust the colour and reduce the Size.

In the solution, these bars have rounded ends, so add another instance of O/U Difference to Columns. Change the mark type to Circle, and set the Colour to match the bars. Make the char dual axis and synchronise axes. Adjust the size of the circle and/or bar marks, so the circle makes the bar look like it has a rounded end.

And that’s the crux of the challenge. Formatting changes include

  • Set background colour of worksheet
  • Remove all gridlines, zero lines, axis rules/tick marks
  • Remove all row & column dividers
  • Add row banding
  • Add Season to Filter and exclude 1967
  • Adjust axis titles
  • Adjust formatting style of the Season, SB #, O/U Line values
  • Add title and description

And then add to a dashboard, and you’re all set. My published viz is here.

Happy vizzin’!

Donna

Can you visualise customer spend insights?

Donna ColesLeave a comment

Lorna set this week’s challenge inspired by a challenge first set in 2018 that was a ‘combo’ challenge with Prep : use Prep to generate the data set, then visualise. In this instance, we’re doing all the data calculations in Desktop itself (and adding on a few extra too).

Building out the calculations

We need to find the first purchase date per customer, so we use a FIXED LOD for this (we’ll be using a lot of these 🙂 )

First Purchase Date

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

We then want, for each customer, then next purchase date, which is the earliest order date, where the date is after the first purchase date

Second Purchase Date

{FIXED [Customer ID]: MIN(IF [Order Date]>[First Purchase Date] THEN [Order Date] END)}

With both these fields we can then get, for each customer, their

First Purchase Sales

{FIXED [Customer ID]: SUM(IF [Order Date] = [First Purchase Date] THEN [Sales] END)}

and their

Second Purchase Sales

{FIXED [Customer ID]: SUM(IF [Order Date] = [Second Purchase Date] THEN [Sales] END)}

and we can get the difference between these

Difference Between Purchases

ABS(SUM([First Purchase Sales]) – SUM([Second Purchase Sales]))

and an indicator about which purchase is higher

Purchase Diff

IF SUM([First Purchase Sales]) >= SUM([Second Purchase Sales]) THEN ‘First Purchase Higher than Second Purchase’
ELSE ‘Second Purchase Higher than First Purchase’
END

Let’s put all this into a table

To determine the type of outlier, we need more fields. We need to get a value for the average of all the First Purchase Sales

Avg First Purchase Order Value

{AVG([First Purchase Sales])}

note this is a short notation for {FIXED:AVG([First Purchase Sales])} and is an instruction to average across the whole data set, as we want a single value that is the same for all the rows of data. We also need

Avg Second Purchase Order Value

{AVG([Second Purchase Sales])}

Pop these into the table too

and we need to know what the standard deviation is for each value, which again are essentially ‘a constant’ across the whole data set

First Purchase Std

{FIXED :STDEV([First Purchase Sales])}

and

Second Purchase Std

{FIXED :STDEV([Second Purchase Sales])}

And now we can determine the outlier type for each customer

Outlier Type

IF SUM([First Purchase Sales])>=10000 OR SUM([Second Purchase Sales]) >= 10000 THEN ‘High Purchase Outlier’
ELSEIF SUM([First Purchase Sales])> (SUM([Avg First Purchase Order Value])+ (3*MIN([First Purchase Std]))) AND SUM([Second Purchase Sales]) > (SUM([Avg Second Purchase Order Value]) + (3*MIN([Second Purchase Std]))) THEN ‘Outlier’
ELSEIF SUM([First Purchase Sales])> (SUM([Avg First Purchase Order Value])+ (3*MIN([First Purchase Std]))) THEN ‘First Purchase 3STD Outlier’ ELSEIF SUM([Second Purchase Sales]) > (SUM([Avg Second Purchase Order Value]) + (3*MIN([Second Purchase Std]))) THEN ‘Second Purchase 3STD Outlier’
ELSE ‘Within Range’
END

Add the Outlier Type into the table and also add to Filter and show the filter, and do some checks on the different types

We’ve got all the data, now we can build.

Building the chart

ON a new sheet, add First Purchase Sales to Columns and Second Purchase Sales tp Rows. Add Customer ID to Detail. Add Purchase Diff to Shape and adjust accordingly. Hide the null indicator

Add Difference Between Purchases to Size and adjust mark size range to suit

Add Outlier Type to Colour, and then add another instance of Purchase Diff to Detail, then click on the ‘detail’ icon to the left of the Purchase Diff pill on the marks card, and change it to Colour, so 2 pills are on the Colour shelf. Adjust colours to suit.

Add Outlier Type to Filter and show the filter and uncheck the High Purchase Outlier option. Then add Customer Name to Tooltip and adjust to suit.

To make the diagonal ‘reference line’, add another instance of First Purchase Sales to Rows. This creates another marks card. Remove all fields from this card, except Customer ID and change the mark type to Line. Remove all text from the Tooltip for this marks card.

Make the chart dual axis and synchronise the axis.

Format the chart by

  • hide the second axis (uncheck show header)
  • set the row and column dividers to not display on the header sections (but to display on the pane sections)
  • Set background of worksheet to grey, but set the pane background to white

And that should be the build

Create dashboard, and use a horizontal layout container. In the right hand side, add the viz. In the left side, add a vertical container and use text objects to display the information, and add the filter control into this section too. Use a blank object to make a vertical divider, and add a border around the ‘parent container’.

And that should be it. My published viz is here.

Happy vizzin’!

Donna

Can you build a parallel coordinates chart?

Donna ColesLeave a comment

This week’s challenge uses data I collated from the stats vest my daughter wears during her football matches and training sessions. The data provided is already pre-filtered to the 2024-25 season and her Reading U15 team.

If you want to watch a video which demonstrates the concept then this video by Andy Kriebel will help. But if you prefer to read, then carry on 🙂

Building out the calculations

In creating this chart we need to normalise all the different stats we want to show; that is we want to convert every measure into a spread between 0 and 1. To do this we calculate the difference between the value and the minimum value as a proportion of the full range of values (that is the difference between the min and max values). We use table calculations for this.

Total Distance (km)

(SUM([Distance – Total (KM)]) – WINDOW_MIN(SUM([Distance – Total (KM)])))
/
(WINDOW_MAX(SUM([Distance – Total (KM)])) – WINDOW_MIN(SUM([Distance – Total (KM)])) )

format to 4 dp

Distance / Min (m)

(SUM([Distance per Min]) – WINDOW_MIN(SUM([Distance per Min])))
/
(WINDOW_MAX(SUM([Distance per Min])) – WINDOW_MIN(SUM([Distance per Min])) )

format to 4 dp

HSR Distance (m)

(SUM([HSR – Total (M)]) – WINDOW_MIN(SUM([HSR – Total (M)])))
/
(WINDOW_MAX(SUM([HSR – Total (M)])) – WINDOW_MIN(SUM([HSR – Total (M)])) )

format to 4 dp

HI Distance (m)

(SUM([HI Distance – Total (M)]) – WINDOW_MIN(SUM([HI Distance – Total (M)])))
/
(WINDOW_MAX(SUM([HI Distance – Total (M)])) – WINDOW_MIN(SUM([HI Distance – Total (M)])) )

format to 4 dp

Max Speed (m/s)

(SUM([Speed – Max (m/s)]) – WINDOW_MIN(SUM([Speed – Max (m/s)])))
/
(WINDOW_MAX(SUM([Speed – Max (m/s)])) – WINDOW_MIN(SUM([Speed – Max (m/s)])) )

format to 4 dp

NOTE – in my solution I called these fields Norm – xxxx . After putting on the chart, I aliased them to the names above. Creating with the actual names I used is the simpler solution 🙂 I don’t know why I didn’t just rename them…

On a new sheet, add Game ID to Rows, and then add all the original key measures and their associated normalised values into the table, so each original measure is next to its normalised value. If you sort by one of the original measures from largest to smallest, you should see that the associated normalised value is distributed from 1 at the top of the list to 0 at the bottom.

The other key requirement for this viz is to identify a selected opposition. For this we create a parameter based on the Opposition field. Right click on Opposition > Create > Parameter and the following dialog with the list of opposition teams pre-populated will display. I set the default as Chelsea Ladies U14

Opposition Parameter

The create a new field

Highlight Match

[Opposition] = [Opposition Parameter]

Show the Opposition Parameter control and add Highlight Match to Rows.

Now we can start building the viz.

Building the chart

On a new sheet, add Game ID to Detail, then add Total Distance (km) to Rows. Adjust the table calculation of the field to be computing by Game ID.

Then add Distance / Min (m) onto the sheet, by dragging the field and dropping it on the y-axis when the 2-column icon appears

This will automatically add Measure Names and Measure Values into the display, including a Measure Values ‘box’

Adjust the table calc of the Distance / Min (m) field to compute by Game ID. Reorder the fields in the Measure Values box, and then change the mark type to line. Set to Fit Width.

Add the other 3 normalised fields into the view, by adding the field into the Measure Values box, and adjusting the table calc each time.

We now have the core viz, we just need to format it and add the additional features.

Show the Opposition Parameter control. Add Highlight Match to the Colour shelf. Adjust colours and re-order so True is listed first. Adjust all the table calcs in the 5 measures so they are also now computing by both Game ID and Highlight Match.

Now add H/A to the Detail shelf and change to be an Attribute (this means by default it is excluded from the table calcs, so we don’t need to make all those adjustments again). Then change the icon to the left of the pill from Detail to Colour, so there are now two fields on the Colour shelf. Adjust to suit.

Add Highlight Match to the Size shelf, and then adjust the size to be reversed, so the True lines are thicker.

Create a new field

Label: Opposition

IF [Highlight Match] THEN [Opposition] END

And add this to the Label shelf and set to be an Attribute. Adjust the Label property so it’s just labelling the line ends and label end of line. Ensure labels set to overlap to, and set font to bold and to match mark colour and align top right.

Alias the field H/A (right click field > aliases) so they display as Home and Away. Then add the following fields to the Tooltip : Category, Cup/League, H/A, Opposition, Round and Date. Also add the following measures to the Tooltip : Distance – Total (KM), Distance per Min, HI Distance – Total (M), HSR – Total (M), Speed – Max (m/s). Then adjust and format the Tooltip

To create the vertical lines, add another copy of Measure Values to the Rows which will create a Measure Values (2) marks card. Remove all the fields from this card except the Game ID. And then move Game ID to the Path shelf which has the effect of creating the vertical lines

Adjust the Colour of the line and then add Measure Names to the Label shelf. Set the label to Min/Max by pane, for the Measure Values field. Align centrally.

Set the chart to dual axis and synchronise the axis. Hide all the axis, and the Measure Names labels at the bottom (uncheck show header against the pills). Remove all row/column dividers and gridlines/zero lines etc. Set the background colour (if desired).

Adding the interactivity

Add the chart to a dashboard then create a dashboard parameter action

Set Opposition

On select of the viz, set the Opposition Parameter parameter, with the value from the Opposition field. Set the value to <empty> when cleared.

Adding the information panel

Add a floating Text object onto the dashboard. Copy and paste the information from the challenge page. Resize and reposition the text box as required. Set the background of the text object and add a border to make it prominent. Then from the context menu of the object, select the Add Show/Hide Button option. The Show/Hide object will appear as an X. Move this into the required position and re-adjust the text object to suit too.

And that should then be it. My published viz is here.

Happy vizzin’!

Donna

Can you create a dynamic moving average chart?

Donna ColesLeave a comment

Lorna set a table calculation focused challenge this week.

Creating the parameters

The challenge requires 3 parameters

pTimePortion

string list parameter containing values week, month and quarter; defaulted to month. Note the capitalisation of the display as value. The value itself should all be lowercase as it will be referenced in calculations later.

pTimeFrame

integer parameter ranging from 12 to 36, with a step-size of 6 and defaulted to 24

pMoveAvg

integer parameter ranging from 3 to 12 with a step size of 3 and defaulted to 3

Creating the calculations

The viz needs to display a dynamic date based on the value of the pTimePortion parameter.

Display Date

DATE(DATETRUNC([pTimePortion],[Order Date]))

It also displays the moving average of Sales based on the pMoveAvg parameter.

Moving Avg

WINDOW_AVG(SUM([Sales]), -1*([pMoveAvg]-1), 0)

Note, as the moving average is to include the Sales value of the ‘current’ date, then we need to subtract 1 from the pMoveAvg parameter. Ie if the pMoveAcg parameter = 3, then we want to calculate the moving average over the ‘current’ mark plus the previous 2 marks, so -2 needs to be fed into the calculation.

Finally, we need to restrict the dates being displayed in the viz. For this I calculated

Latest Date

WINDOW_MAX(MAX([Display Date]))

then fed this into

Date to Display

MIN([Order Date])>DATEADD([pTimePortion],-1*[pTimeFrame],[Latest Date])

ie only include records where the Order Date is greater than pTimeFrame weeks/months/quarters prior to the Latest Date.

Building the viz

On a new sheet, display all the 3 parameters. The add Display Date as a continuous exact date (green pill) to columns and Sales to rows.

Add Moving Avg to rows, make dual axis and synchronise the axis. Adjust colours of the marks to suit.

Add Date to Display to the Filter shelf and set to True. Then add Sales and Moving Avg to the Tooltip of the All marks card, and adjust Tooltip accordingly.

Update the title of the sheet so it references the various parameters

Finally, tidy up by

  • removing row and column dividers
  • hiding the right hand axis (right click, uncheck show header)
  • editing the Display Date axis, so the title of the axis references the pTimePortion parameter (Note – I did find this gets ‘lost’ when publishing to Tableau Public, so I had to re-edit my viz after publishing to reapply this setting).

Then add to a dashboard, and use a horizontal layout container to organise the parameters across the top. My published viz is here.

Happy vizzin’!

Donna

Population Pyramids

Donna ColesLeave a comment

Sean used a new HR dataset from the #RWFD website for this week’s #WOW2025 challenge.

The data provided contained 1 row per person. I started by creating a bin based on the Age field, which I dragged into the dimensions section of the data pane (above the line) after I connected to the data. Right click Age > Create Bins

Age Group

Create bins every 5 ‘units’

Adding this on to Rows you’ll see you get the relevant values

With this field, we can create the other calculations we will need

Total Headcount

{FIXED[Age Group]: COUNT([synthetic_hr_dataset.csv])}

Other Gender

{FIXED [Age Group]:SUM( IIF([Gender]<>’Female’ AND [Gender]<>’Male’,1,0))}

Format both of these to 0 dp

Males

IIF([Gender]=’Male’,1,0)

Format this to 0dp

Females

IIF([Gender]=’Female’,-1,0)

Note, this is -1 due to how we’re going to plot on the chart, but we don’t want the labels displayed with negative numbers, so custom format this field to ,##0;#,##0

Headcount Gap

SUM([Males]) + SUM([Females])

Note – we’re adding since the Females value is actually a negative number

Also custom format this to ,##0;#,##0

Add Total Headcount to Rows and change to be discrete (blue pill). Add Other Gender to Rows too, and again change to discrete.

Add Females to Columns and then add Males to Columns too.

Make the chart dual axis and synchronise the axis. Change the mark type on the All marks card to bar. Adjust the Measure Names colour legend.

Make the rows a bit wider. On the All marks card, on the Label shelf, tick show mark labels and align the labels middle centre and bold.

Add Headcount Gap to Columns. Remove the Measure Names field from the Colour shelf on the Headcount Gap marks card.

Create a new field

Colour – Headcount Gap

[Headcount Gap] <0

and add to the Colour shelf on the Headcount Gap marks card and adjust colours accordingly.

I chose to make the tooltip on all the bars to match exactly and reference the information shown. For this I created some other fields

Tooltip – Least Gender

IIF(NOT([Colour – Headcount Gap]),’women’, ‘men’)

Tooltip – Most Gender

IIF([Colour – Headcount Gap],’women’, ‘men’)

On the All marks card, add Males, Females, Headcount Gap, Tooltip – Least Gender and Tooltip – Most Gender to the Tooltip shelf and adjust the tooltip as required

Add a constant 0 reference line that displays a solid black line to both the Female axis and the Headcount Gap axis.

Finally tidy up by

  • Hide the top axis (uncheck show header)
  • Editing the title of the Female axis to read Headcount
  • Remove all gridlines
  • Remove column dividers
  • Update the viz title.

Add the sheet to a dashboard and ta-dah! My published viz is here.

Happy vizzin’!

Donna

Can you build a satellite chart to represent values exceeding 100%

Donna ColesLeave a comment

For this week’s challenge, we’re using the data from a previous challenge and visualising it using Sam Parson’s ‘satellite’ chart idea – see here.

Modelling the data

Connect to the Food Self-Sufficiency csv file then add another connection to the Circle_Scaffold excel file. Relate the two together using a relationship calculation where 1 = 1 (ie relate every row in left hand data source to every row in the right hand).

Since we only care about data from FY2019, add a data source filter to set Fiscal Year = FY2019.

This saves us from having to apply that filter to each sheet we build.

Building a single spiral

On a new sheet, add Prefecture to the Filter shelf and select Akita-ken (which is near the top of the list and has a % value over 200%).

Create a parameter

pMinRadius

integer parameter defaulted to 500

To build the spiral, we need to plot a mark for every percentage point from 0 up to the Food Self-Sufficiency % value. For this we will need to determine an (x,y) coordinate value for each point, which will require some trigonometry, based on the diagram below

For each point on the circle, we will need to identify the x & y position of where the radius intersects the edge of the circle. As we are building a spiral, the radius of the circle will increase as we move around each percentage point, so we need

Radius

[pMinRadius]+[Path Percent Point]

We then need to determine the angle θ. As a circle is 360°, and a complete circle represents 100%, then 1% is 360/100, so the angle (in radians) for each % point plotted round the circle can be calculated as

Angle

RADIANS([Path Percent Point] * (360/100))

X

(SIN([Angle]) * [Radius])/360

Y

(COS([Angle]) * [Radius])/360

Now create the point

Spiral Point

MAKEPOINT([X],[Y])

Note – the X & Y values are divided by 360 due to the spiral we’re building and the increasing radius when displayed using map layers. If we were just plotting X against Y and not using map layers, this wouldn’t be required.

Double click on Spiral Point to automatically add Longitude and Latitude fields to the sheet.

Change the mark type to line and then add Path Percent Point to the Path shelf.

Add Prefecture to the Detail shelf, as it’ll be needed later when we build the trellis and remove the filter.

At this point, the spiral is showing 3 complete revolutions, as the data in the circle_scaffold data set contains info for up to 300%. We need to restrict it so we only show up to the Self-sufficiency ratio… so we need

Filter Percent Point Displayed

[Path Percent Point] <=[Self-sufficiency ratio for food in calorie base 【%】]

Add this to Filter shelf and set to True.

We now want to colour the spiral based on the percentage point associated to each mark plotted being <100%, between 100% & 199% or >= 200%, so we can use

Colour – Spiral

FLOOR([Path Percent Point]/100)

which will return an integer of 0, 1, or 2

Change this field to be discrete and then add it to the colour shelf and adjust colours accordingly.

Now obviously, you might be thinking things aren’t quite right – we’re not starting at the top and rotating differently. Simply pressing the swap rows and columns icon in the menu bar will resolve this, but if we do that too early, we lose the ability to add map layers, so leave as is for now.

Add the label map layer

Create a 0 point

Zero

MAKEPOINT(0,0)

Drag this onto the canvas and drop when the Add a Marks Layer option appears

This has the effect of creating a 2nd marks card

and now we have this, we can press the swap rows and columns icon in the menu bar to get the start of the spiral at X=0

Change the mark type to circle and add Self-sufficiency ratio… and Prefecture to the Label shelf. Adjust the font style and align centrally. Set the colour of the circle to white and increase the size. Move the Zero marks card to be below the Spiral Point marks card.

Rename the marks cards if you wish.

Add the starting point map layer

We need to create a point for the start of each line which is at the 0% mark

Start Point

MAKEPOINT((IF [Path Percent Point] = 0 THEN [X] END), (IF [Path Percent Point] = 0 THEN [Y] END))

Add this as a new marks layer. Change the mark type to circle and increase the size a bit. Colour the mark to the same base colour you used for your <100% range and remove the border. Rename the marks card to 3.Start Point

Add the end point map layers

Create a new point to represent the end of each line

End Point

MAKEPOINT((IF [Path Percent Point]= [Self-sufficiency ratio for food in calorie base 【%】] THEN [X] END), (IF [Path Percent Point]= [Self-sufficiency ratio for food in calorie base 【%】] THEN [Y] END))

Add this as a new marks layer. Change the mark type to circle and increase the size a bit. Add Colour-Spiral to the Colour shelf as a discrete dimension (blue disaggregated pill) and remove the border. Rename the marks card to 4.End Point Outer.

Add another instance of End Point as another marks layer. Again change the mark type to circle and adjust the size so it is smaller than the previous circle, and set the Colour to white. Rename the marks card to 5.End Point Inner.

Tidy up by

  • removing the Tooltip from each layer
  • disabling selection of each map layer (so nothing happens when you hover over it)
  • Hide the axis
  • Remove axis rulers and gridlines, but make sure the zero lines are shown
  • Hide the null indicator

Name the sheet Single Spiral or similar.

Building the trellis

Duplicate the single spiral (so if things go awry, you can get back to this). Then start by adding Prefecture to the Detail sheet of all the marks card.

When creating trellis charts, we need to create fields that represent which row and which column each Prefecture should sit in. There’s lots of blogs on creating trellis charts. As we know the number of rows and columns we need, I created

Cols

INT((INDEX()-1)%10)

Rows

INT((INDEX()-1)/10)

and make both fields discrete.

Add Cols to Columns and Rows to Rows. Adjust the table calculation setting of each field so it is computing by Prefecture only, and custom sorted descending by Self-sufficiency ratio…

Then show the Prefecture filter and select all values to display the ordered set of Prefectures.

Hide the Rows and Cols fields, remove row & column dividers. Adjust the size of the start and end point circles to suit, and if the zero lines aren’t showing, reduce the size of the label circle map layer and fit to Entire View.

Then name this sheet Trellis or similar and add to the dashboard.

My published viz is here

Happy vizzin’!

Donna

Can you build a ranked heatmap tile?

Donna ColesLeave a comment

Erica had a guest coach, Valerija Kirjackaja setting the challenge this week, asking us to use table calculations to build this heatmap table.

I built an initial version of the heatmap only, which I’ve published here, but I couldn’t get the display to match Valerija’s when I clicked on it. (Note – I wasn’t bothered about Viz in Tooltip part at this point). I used 2 dimensions to display the yellow header section, and you can see below, when clicking, this is noticeable. But Valerija’s solution treats the header as a single entity.

I couldn’t figure out how she’d managed this, so I had to have a look at the solution, and then I built my own instance, which is what I’ll now blog about.

Defining the core fields

Connect to the datasource and add a data source filter to restrict the data by the latest year (as I used Superstore 2025, I filtered to 2025). Doing this meant I didn’t have to worry about adding worksheet level filter fields.

On a new sheet add Category and Sub-Category to Rows and add Sales to Text and then sort descending so you can see the data we need to work with. Format Sales to be $ to 0 dp.

Create a new field

Sales by Cat Rank

RANK(SUM([Sales]))

change it to be discrete and then add to Rows. Adjust the setting of the table calculation so it is computing by Sub-Category only, so the ranking restarts for each Category

We will also need to display the Category in upper case, so create

Category Upper

UPPER([Category])

and add to Rows.

Having this tabular layout just lets us clarify how the table calculation will be working.

Building the Heatmap Table

On a new sheet, add Category to Columns and Sub-Category and Sales to Text and align centrally (Note – I originally put Category Upper on the columns, and then changed later after taking all the screen shots)

Add Sales by Cat Rank to Rows. Change it to a continuous (green) pill and edit the axis to be reversed. Adjust the table calculation so it is computing by Sub-Category only.

Create a new field

One

1

Change the mark type to gantt bar and add One to the Size shelf, setting the aggregation to Avg. Increase the size of the mark via the Size shelf to as large as possible. Add Sales to Colour then add a white border.

We need the axis numbers to be central to the ‘row’, so to fix this, double click into the Sales by Cat Rank pill and change it to subtract 0.5 ( [Sales by Cat Rank] -0.5 )

Re-edit the axis to reverse it again.

So our ‘table’ is now displaying data on a axis from 0.5 up to 9.5. To add the header, we’re going to plot another mark at -0.5, so we can display a section of the same size as the existing ones. For this, double click into Rows and type MIN(-0.5).

This has the effect of creating a second marks card

Remove Sales from Colour and Sub-Category from Label. Add Category Upper to Label. Adjust the label text alignment and formatting and change the Colour to yellow.

Make the chart dual axis and synchronise the axis and we now have the required display.

Tidy up by

  • Remove row & column dividers
  • Remove gridlines, zero lines & axis ticks
  • Hide the right hand axis (right click > uncheck show header)
  • Hide the Category Upper column labels (right click pill > uncheck show header)
  • Remove the left hand axis title
  • Fix the left hand axis from -0.5 to 9.5
  • Format the axis with a custom formatting of #,##0;-#,##0;TOTAL so that 0 is displayed as the word TOTAL (this is very sneaky by the way, and took me a while to work out)
  • Then format the font to be bold
  • Adjust the Tooltip on the Sales by Cat Rank marks card to display the Sub-Category and Sales value.
  • Delete the text from the Tooltip on the MIN(-0.5) marks card
  • Name the sheet Table or similar

Building the Viz in Tooltip

On a new sheet add Order Date to Columns at the discrete month level (blue pill) and add Sales to Rows. Add Sub-Category and Category to Detail. Format the date axis so the dates are just using First Letter.

Create a new parameter

pSubCat

string parameter defaulted to Bookcases

Then create a field

Is Selected SubCat

[Sub-Category] = [pSubCat]

and add to the Colour shelf. Adjust accordingly, then make sure True is listed first in the legend, so the line is ‘on top’

Create a new field

Label Line

IF [Is Selected SubCat] THEN [Sub-Category] END

and add to the Label shelf and update to allow marks to overlap. Hide the Order Date label heading (right click > hide field labels for columns). Title the chart Line or similar

Remove gridlines and row/column dividers

Go back to the Table sheet, and update the Tooltip of the Sales by Cat Rank marks card to reference the Line chart and update the filter to pass as just <Category>

Adding the final interactivity

Create a dashboard and add the Table sheet. Then add a parameter action

Set Sub Cat Param

On hover of the Table sheet, set the pSubCat parameter with the value from the Sub-Category field, setting the value to <empty string> when the selection is cleared.

If all has been applied, then the line chart should just display the lines associated to the Sub-Categories in the same Category

My published viz is here.

Happy vizzin’!

Donna

Let’s make a Tableau Pulse-Inspired Dashboard!

Donna ColesLeave a comment

Yoshi set the challenge this week to build a dashboard which looks like the visual you might get as part of a Pulse metric.

Define the parameters

Create a parameter to define the ‘reporting’ date

pBaseDate

date parameter defaulted to 21 Sept 2025

Create a parameter to capture the number of previous weeks the ‘forecasting’ values should be calculated against

pWeeks

integer parameter defaulted to 8, with a min value of 5 and max of 15, incremented every 1 step

Building the KPI card

This section displays the month to date sales and comparisons to previous month, based on the value in the pBaseDate parameter. We need several calculations

MtD Sales

IF [Order Date] >= DATETRUNC(‘month’, [pBaseDate]) AND [Order Date]<=[pBaseDate] THEN [Sales] END

format to $ with 0 dp.

Prev MtD Sales

IF [Order Date] >= DATEADD(‘month’, -1, DATETRUNC(‘month’, [pBaseDate])) AND
[Order Date]<= DATEADD(‘month’, -1, [pBaseDate]) THEN [Sales] END

MtD Sales Diff

SUM([MtD Sales]) – SUM([Prev MtD Sales])

custom format to +”$”#,##0;-“$”#,##0

MtD Sales % Diff

[MtD Sales Diff] / SUM([Prev MtD Sales])

custom format to +0.0%;-0.0%;0%

On a new sheet add MtD Sales, MtD Sales Diff and MtD Sales % Diff to Text. Change the mark type to shape and set to be a transparent shape (refer to this blog to understand how to set this up). Adjust the layout and style of the text on the Label. Set the sheet to Entire View and align the text to the left. Update the title of the sheet and remove the Tooltip.

Building the Line Chart

Before building the viz, we’ll start by building the calculations and checking them through a tabular display.

On a new sheet, show the pBaseDate and pWeeks parameters. Then add Order Date as a discrete exact date (blue pill) to Rows and add MtD Sales to Text. The MtD Sales values should only display against the dates from 1st to 21st Sept.

For each day, we need to calculate the 25th percentile of the Sales value using the gp model against the same day of the week for the previous data.

25th Percentile

MODEL_QUANTILE(“model=gp”,0.25,SUM([Sales]),ATTR([Order Date]),ATTR(STR(DATEPART(‘weekday’,[Order Date]))))

and we also need the 75th percentile

75th Percentile

MODEL_QUANTILE(“model=gp”,0.75,SUM([Sales]),ATTR([Order Date]),ATTR(STR(DATEPART(‘weekday’,[Order Date]))))

These 2 values give the ‘range’ we want to check the actual MtD Sales value against. Add both values into the table, and update the table calculation setting of each so they are computing explicitly by Order Date

However we only want the calculations to be based on the last x weeks, so we want to filter the display.

Dates to Include

[Order Date]>= DATEADD(‘week’, -1 * [pWeeks], [pBaseDate]) AND [Order Date] <= [pBaseDate]

Add this to the Filter shelf and set to True. This will adjust the 25th & 75th Percentile values as they are only considering data within the display (eg compare the highlighted values for 01 Sept against those in the image above)

But when it comes to ‘plotting this data’ on a chart, we only want to display the data for the current month (based on pBaseDate), so we need to apply a second filter that restricts the dates displayed further, but that does not eliminate the data in such a way that we lose reference to the previous x weeks.

We can do this using a filter based on a table calculation

Filter: Dates for Chart

LOOKUP(MIN([Order Date]),0) >=DATETRUNC(‘month’, [pBaseDate])
AND LOOKUP(Min([Order Date]),0) <= [pBaseDate]

The LOOKUP function is a table calc that is basically returning the same value for Order Date and comparing it against the base date. But because it is a table calc, when applied as a filter, it will only be applied after other computations.

Add this to the Filter shelf and set to True. Then adjust the table calc so it is explicitly computing by Order Date and then re-edit the filter again so it is just considering True values (changing the table calc setting, resets this).

Let’s start to build the viz :

Duplicate the tabular sheet above. Move Order Date to Columns and change to be continuous (green pill), Move MtD Sales to Rows and move Measure Values to Rows. Move the Measure Names field to the Colour shelf of the Measure Values marks card. Unstack the marks ( Analysis menu > stack marks > off) – if you can’t see 2 areas, swap the order of the pills in the Measure Values box so the 25th Percentile is listed first. Adjust the colours of the marks so the 25th percentile is white and the 75th percentile is pale blue (#e6f2fe). Ensure the opacity of these marks are 100%. Set the colour of the Mtd Sales line to bright blue.

Note – by moving the pills around and having already explicitly set the table calculation settings, we know the fields will be computing correctly. If you wish, you can build the viz from scratch, but you will need to explicitly set all the table calc pills to compute by Order Date again.

Make the chart dual axis and synchronise the axis. Right click the right hand axis and move marks to back to make the line display in front.

Create new fields

Ref Line – Start of Month

DATE(DATETRUNC(‘month’, [pBaseDate]))

and

Ref Line – End of Month

DATE(DATEADD(‘day’,-1, DATEADD(‘month’, 1,DATETRUNC(‘month’, [pBaseDate]))))

Custom format both of these to dd/mm and then add both to the Detail shelf of the All marks card. Set to be continuous exact date (green pills).

Add 2 reference lines to the Order Date axis, which reference these pills.

Add pBaseDate to the Detail shelf of the All marks card too, and add an additional reference line to that field. In this instance format the reference line and apply a custom format to the date to be dd/mm

Adjust the Tooltip via the All marks card. Remove all gridlines, zero lines and row/column dividers. Hide the right hand axis and the Order Date axis. Remove the title from the left hand axis.

To show the summary of how the MtD Sales value for the pBaseDate compares to the range, we will use the caption feature of the worksheet, which can reference fields, but we need these fields to essentially be ‘constants’ for every row of data, so we need some new fields.

Sales for Base Date

WINDOW_MAX(SUM(IF [Order Date] = [pBaseDate] THEN [Sales] END))

format this to $ with 0 dp.

25th Percentile for Base Date

WINDOW_MAX(IF MIN([Order Date]) = [pBaseDate] THEN [25th Percentile] END)

75th Percentile for Base Date

WINDOW_MAX(IF MIN([Order Date]) = [pBaseDate] THEN [75th Percentile] END)

Switch back to the tabular view of data and add these 3 fields. As they’re all table calculations, you need to set them to be computing by Order Date only (as we did above).

You should find that the values for the latest row (highlighted below) are displayed against every row in the 3 additional columns

With this, we can now work out the ‘text’ we want to disply in the caption

Expected Range Text

IF [Sales for Base Date] > [75th Percentile for Base Date] THEN ‘above’
ELSEIF [Sales for Base Date] < [25th Percentile for Base Date] THEN ‘below’
ELSE ‘within’
END

Add this to Rows, check the table calc settings, and then adjust the pBaseDate value so you can see the text change.

Now switch back to the line chart, and display the Caption (worksheet menu > show caption). Add Sales for Base Date and Expected range Text to the Detail shelf of the All marks card, adjusting the table calc settings as we’ve done before.

Then edit the caption and remove all text and update, referencing the various fields and parameters.

Building the bar chart

On a new sheet, add Segment to Rows and MtD Sales Diff to Columns. Sort descending. Create a new field

Diff is +ve

[MtD Sales Diff]>=0

Add to Colour and adjust accordingly. Show mark labels and set to match mark colour. Format the display to remove all column/row dividers, gridlines and zero lines. Display the Row Axis Ruler as a thicker grey line.

Then add all the components to a dashboard, using containers and padding to organise the display. Make sure to display the caption for the line chart worksheet.

And that should be it. My published viz is here.

Happy vizzin’!

Donna

Can you build an L-Shaped bar chart?

Donna ColesLeave a comment

My inspiration for this week’s #WOW challenge came from Sarah Palette’s post on X from last year, and has been something I’d been meaning to try for ages, so figured a #WOW challenge was the perfect opportunity. Sarah has her own blog post which contains the core ‘trick’ to nailing the display, but as usual I’m going to walk you through step by step 🙂

After connecting to the data, start by adding Sub-Category to Rows, Sales to Columns, Category to Colour and sort by Sales descending. Adjust the colours to suit.

Format Sales to be $ to 0 dp, and add to Label. Apply a quick table calculation of Percentage of total to the Sales pill, and then format the pill to be % to 1 dp. Add another instance of Sales to Label. Adjust the layout and format the label to 8pt bold and match mark colour. Reduce the Size of the mark.

Double click into the Columns shelf, and manually type MIN(0). Then drag the MIN(0) pill from Columns and drop it on the Sales axis when the green ‘2-column’ icon appears. This automatically adds Measure Names and Measure Values into the view. Re-order the pills in the Measure Values section. This display now has 2 measures sharing a single axis.

Change the mark type to Line and line type to stepped (via the Path pill). Adjust the Label so it is labelling line ends and start of line only. Align label top right. Adjust the size of the line as required.

Click on the MIN(0) pill in the Measure Values section, and while holding down Ctrl drag the pill to Columns to create a copy of the same measure. This is important as typing in another instance of MIN(0) will create another separate measure, and we need it to be the same one.

This will create another marks card (MIN(0)). Remove the Sales pills from the Label shelf, and add Sub-Category instead. Align the label middle right, and add a couple of spaces before the Sub-Category text. Set the chart to be dual axis, synchronise the axis and then remove Measure Names from the All marks card. Adjust the width of each row to push the Sub-Category label into the corner of the L.

To give the labels at the end of the bar some ‘breathing room’, create a field

Max Sales + 10%

WINDOW_MAX(SUM([Sales])) * 1.1

Add this to the Detail shelf on the All marks card. Adjust the table calculation so it is computing by both Category and Sub-Category. Then right click on the bottom axis and Add Reference Line that uses the average of the Max Sales + 10% field for the Entire Table. Don’t display and lines/labels/tooltip

Finally tidy up by

  • Hiding both axis (right click -> uncheck show header)
  • Hiding both the Sub Category and Measure Names headers (right click, uncheck show header)
  • Remove all gridlines, zero lines, row & column dividers
  • Adjust Tooltip as required
  • Add a title colouring the text of the words to match the colours used in the legend.

Add to a dashboard. My published viz is here.

Happy vizzin’!

Donna