For this week’s challenge we’re going to look at building an alternative to a stacked bar chart. This challenge was inspired by this post by the Flerlage twins, which in turn was inspired by other members of the #datafam community. Once again, we’re making use of some Premier League data.
Building the viz
Add Team to filter and select Manchester United, Chelsea, Arsenal and Manchester City then add Season to filter and select the last five seasons. Then add Team and Season to Columns and Points to Rows.
Sort team by the field Points descending. Add Team to colour and adjust accordingly. Show Mark labels to display the number of points for each bar.
Add subtotals via the analysis menu > totals > show all subtotals
Hide the subtotal bar by clicking on one of the total bars and selecting hide from the drop down which is defaulted to automatic
Right click on the Total label on the axes and select format – in the left hand side, delete the label field
Create a new calculated field
Total Points
WINDOW_SUM(SUM([Points]))
And add this to rows. Remove Team from the colour shelf on the marks card, and change the colour to pale grey. Increase the size of the bars to be as wide as possible. Uncheck show mark labels. Hide the total bar like we did above.
Make the chart dual axis and synchronise axis. If need be, right click the right axis and move marks to back. Adjust the table calculation on Total Points so it is computing by Season only. Hide the total bar again if it reappears.
We want to label the total bar with the Team and the Total Points so we need to create some more calculated fields
Label: Team
IF [Season] = ‘2021-22’ THEN [Team] ELSE NULL END
We’re being sneaky here, and just labelling the central bar.
Label: Total Points
IF MIN([Season]) = ‘2021-22’ THEN [Total Points] END
Add Label: Team to the label shelf of the Total Points marks card and adjust so it is an attribute – this means it’s not referenced in any table calculations. Add Label: Total Points to the label shelf too.
Adjust the label as required and then change the alignment so the direction of the text is swapped. Format the font as desired.
Delete all the text from the Tooltip on the Total Points marks card and adjust the text on the Points marks card to match the required format.
Hide the right hand axes (right click axis > uncheck show header). Hide the Team column heading. Remove all gridlines/ axis lines/ zero lines and row and column dividers.
Hide the Season label heading (right click on the label > hide field labels for columns).
Format the axes font to be smaller and rotate the axes labels on the Season axis.
Name the sheet Bar chart or similar and add to dashboard.
This week, Sean decided to revisit a challenge from 2017, week 12, which was originally posted by Emma Whyte, one of the #WorkoutWednesday founding coaches.
I’ve been completing the #WOW challenges since their inception, so had the original solution already published to my Tableau Public.
Back then, parameter actions didn’t exist, so I decided to build this latest version using them instead of the parameter dropdown list included in the original requirement.
Building the basic viz
Create a new parameter to capture the Sub-Category we want to highlight
pSubCat
string parameter defaulted to ‘Bookcases’.
(NOTE – if I wanted to use a drop down for the user selection, I would instead have set this parameter to be a list populated from the Sub-Category field when the workbook opens).
I can’t always recall quickly the positioning of all the fields I need to build a treemap, so I started by simply double clicking the fields I needed in turn : Category, Sub-Category, Sales to add them onto the canvas, and then selecting the TreeMap icon in the Show Me tab to reposition the fields as required.
Then move the Category field from Text to Detail.
Colouring the blocks
The requirement is to show the selected Sub-Category in one colour, but also show a graduated colour palette for the non selected Sub-Categories.
First, let’s identify the selected Sub-Category.
Show the pSubCat parameter on the canvas. Then create
Is Selected Sub Cat
[Sub-Category] = [pSubCat]
Change the Sales pill on the Colour shelf from continuous (green) to discrete (blue). This will result in a rainbow of colours
Then add Is Selected Sub Cat to the Detail shelf. Then click on the icon next to the pill that indicates it’s on the detail shelf, and change it to Colour, so 2 fields are now on the Colour shelf.
Move the Is Selected Sub Cat field on the colour shelf so it is listed above the Sales field on the colour shelf. The selected sub-Category should now be highlighted, and the other blocks are graduated.
However, the highlighted sub-category is ‘separated’ from the Category block it belongs in. To resolve this, change the Is Selected Sub Cat field on the colour shelf so it is an Attribute. By setting this, the treemap is now only dividing itself by the Dimension fields of Category and Sub-Category.
Format the Sales field to $ with 0dp, and update the Tooltip as required.
Create the sheet title
Create a new fields
Selected Sales
{FIXED:SUM(IF [Is Selected Sub Cat] THEN [Sales] END)}
format to $ with 0dp and add to the Detail shelf.
Update the title of the sheet to reference the pSubCat parameter and the Selected Sales field and format as desired.
Add the interactivity
Add the sheet to a dashboard ,then add a dashboard parameter action
Set Sub Cat
On select of the treemap sheet on the dashboard, set the pSubCat parameter, passing in the value from the Sub-Category field. When the selection is cleared, keep the current value
However, when the treemap is clicked, the selected block gets ‘highlighted’ and the rest fade. To prevent this, create a new field
HL
‘dummy’
and add to the Detail shelf of the Treemap sheet. Then create a new dashboard Highlight action
Deselect
On select of the Treemap sheet on the dashboard , target the same sheet with the HL field only
As all marks have this HL value set, this has the effect of actually highlighting all marks ‘on click’ rather than just the actual one clicked, so making it look like nothing is actually highlighted.
Erica set this week’s challenge, focusing on the ability to compare specific entities against themselves and ‘the whole’ without resulting in a mess of coloured spaghetti. 3 levels of difficulty were provided. As it stated the levels didn’t necessarily follow on from each, I just built (and am therefore blogging about) level 3 – the advanced challenge.
Defining the core parameters
For the user to select the main element they want to analyse we need
pPrimarySubCat
string parameter, that is sourced from a List based on the Sub-Category field when the workbook opens. Default to Binders.
This parameter will be visible to the user to select from a drop down list control.
To capture the secondary element to compare against, we need
pSecondarySubCat
string parameter defaulted to Bookcases.
This is just a ‘type in’ field, that won’t ultimately be displayed to the user, but populated via a dashboard parameter action on select of a line in the chart.
To control the different type of display options, we need
pDisplay
integer parameter sourced from a manual list which aliases the integer values for the displayed text strings. Defaulted to 2 (Difference from Primary)
Defining the additional calculations
As I often do, we’ll build out a tabular display to determine all the calcs required. On a new sheet, add Region and Sub-Category to Rows, then add Order Date at the Quarter level as a discrete (blue) pill to Columns. Add Sales to Text. Show the 3 parameters created above.
We need to identify which Sub-Categories will be coloured. This is based on whether they are a primary or secondary Sub-Category.
Is Primary or Secondary Sub Cat
[pPrimarySubCat] = [Sub-Category] OR [pSecondarySubCat] = [Sub-Category]
Add this to Rows. Based on existing selections, the rows for Binders and Bookcases should be set to True.
We will also need to identify which is the the Primary Sub-Category only to help determine how many rows are displayed, so create
Is Primary SubCat?
[pPrimarySubCat] = [Sub-Category]
Add to rows. In this case just Binders should be True at this point.
With this field, we can then work out how many ‘rows’ are going to be in our final viz display.
Display Row
IIF([pDisplay] = 0, TRUE, [Is Primary SubCat?])
ie, if the pDisplay parameter is ‘Raw values – overlay’ , then we’ll just display 1 row (so all rows set to True), otherwise there will be 2 rows, split based on whether the Sub-Category is the selected value in the pPrimarySubCat parameter or not.
Add this to Rows, and change the pDisplay parameter to see how this field changes.
We also need to display different values depending on what pDisplay option is selected. When the ‘Difference from Primary’ option is selected, then we need to show the Sales value for theprimary Sub Category, but the difference from this value for all others. For this we first need to capture just the sales for the primary Sub-Category
Sales For Primary Sub Cat
IF [Is Primary SubCat?] THEN [Sales] END
Add to the table and adjust Measure Names so it is displayed after the Order Date field. Rows for this column will only have values when the Sub-Category is the primary one selected.
Now we calculate the difference, but only if it’s not the primary Sub-Category; we want Sales in that instance
Sales Difference
IF MIN([Is Primary SubCat?]) THEN SUM([Sales]) ELSE SUM([Sales]) – WINDOW_MAX(SUM([Sales For Primary Sub Cat])) END
Here we’re using a WINDOW_MAX table calc to essentially ‘spread’ the value in the Sales for Primary Sub Cat column across all rows associated to the Region. Add this to the table, and adjust the table calculation setting of the pill, so it is computing by all fields except Region and Order Date
Finally, we need a field that will decide whether we’re displaying Sales or Sales Difference based on the pDisplay selection
Again, add to the table, adjust the table calc as above and then test the output of the field, as you adjust the pDisplay parameter.
While we’re here, we’ll just define another couple of calcs needed for the viz
Label Sub Cat
IF [Is Primary or Secondary Sub Cat] THEN [Sub-Category] END
Used to only display a label for either of the two selected Sub-Categories.
Tooltip – Value Label
IIF([pDisplay]=2 AND NOT([Is Primary SubCat?]), “Difference from ” + [pPrimarySubCat] + ” Sales”, “Sales”)
Will be used on the Tooltip to ensure the correct text is displayed depending on type of display selected.
Building the Viz
On a new sheet, show the 3 parameters and set them to the defaults (ie Binders, Bookcases and Difference from Primary).
Add Region to Columns, then add Order Date at the Quarter level as a continuous (green) pill to Columns. Add Display Row to Rows and adjust the Sort on the pill to be a manual sort, where True is listed first. Add Sub-Category to Detail, then add Value to Display to Rows and adjust the table calc so all fields except from Region and Order Date are selected.
Add Is Primary or Secondary Sub Cat to Colour. Some lines will disappear, but don’t worry. Then add Region to Detail, and then select the ‘detail’ icon to the left of the pill on the marks shelf, and change it to Colour so 2 pills are now on the Colour shelf. Adjust the table calculation setting of the Value to Display pill to ensure the Is Primary or Secondary Sub Cat field is also now checked – this should make all the lines reappear.
Then adjust the colours in the colour legend so all the entries that start ‘False’ are grey and the others are as required.
Adjust the sort on the Is Primary or Secondary Sub Cat pill on the marks card, so it is manually sorted with True first. This ensures the coloured lines are ‘on top’ and always visible. Add Is Primary SubCat? to Size shelf. Readjust the table calc on Value to Display again, and then adjust the Size so it is visibly thicker than the rest of the lines, which will probably be by adjusting both the range in the Size legend, and adjusting the slider on the Size shelf.
Add Label Sub Cat to the Label shelf (adjust table calc again), and set label to allow labels to overlap other marks. Add Tooltip – Value Label to tooltip and update the Tooltip as required
Add a reference line to the Value to Display axis, and set to be a constant of 0 displayed as a black dashed line
Edit both axis to update the axis titles on each, hide the Display Row pill (uncheck show header on the pill) and hide the Region column label (right click > hide field labels for columns).
Building the dashboard
Use layout containers to construct the dashboard as required
Create a dashboard parameter action to capture the value of the secondary Sub-Category
Set Second Sub Cat
On select of the Viz, set the pSecondarySubCat parameter with the value sourced from the Sub-Category field. When selection is cleared, set it <none>
Clicking one of the grey lines should now change the comparison Sub-Category. But you’ll notice the rest of the unselected lines are ‘faded’ and your selection is ‘highlighted’. We don’t want this to happen. To resolve, create new calculated field
HL
‘Dummy’
and add to the Detail shelf on the viz sheet itself.
Then add a dashboard highlight action
Un-Highlight
On selection of the Viz sheet on the dashboard, target the viz sheet on the dashboard, selecting the HL field only.
As all the marks have the HL ‘dummy’ field associated to them, they all become ‘highlighted’, giving the appearance of nothing actually being highlighted.
Finally, we need to make the title of the dashboard ‘dynamic’ and reflective of the selections made in the primary and secondary Sub-Category parameters. But the secondary one can be empty, so the text needs to handle this. An additional ‘ and ‘ needs to display if the secondary Sub-Category is set. I chose to use a parameter to help with this, as text objects on a dashboard can reference parameters.
Create a new parameter
pTitle-and
string field defaulted to the text <space>and<space>
Create a calculated field
Param-and
‘ and ‘
and add to the Detail shelf on the viz. Set it to be an attribute (this won’t impact the table calc).
Back on the dashboard, create another dashboard parameter action
Set ‘and’
on select of the Viz, set the pTitle-and parameter passing in the value from the Param-and field. When the selection is cleared, set to <none>.
Then create (or adjust) the title text object so it references the relevant parameters (notice the spacing – or lack of – between some of the fields)
Yusuke set the challenge this week which allowed us to try out a new feature of Tableau – Dynamic Colour Ranges. As a consequence, you’ll need v2025.2 for this.
Setting up the Data for use in a Map
After connecting to the CSV file provided by Yusuke, I had to set the Region field to have a Geographic Role of State/Province
I then double-clicked on Region to generate the map, but due to my location settings, no information displayed.
To resolve this, Edit Locations (via Map menu) and change Country/Region to Japan.
The Your Data – Matching Location fields should then match, and pressing OK presents a map.
Building the Basic Viz
Move Time from the ‘Measures’ section of the Data pane into the ‘Dimension’ section (drag it to be above the line). Format the Time field to be a custom number with 0 dp and to not show ‘,’ as a thousand separator. Then add Time to Filter and select 2016. Add Voting Ratio of election… to Colour. Adjust the Tooltip.
Edit the Colour Legend and choose a diverging colour palette (eg Red-Blue Diverging), then adjust the start and end colours as required
At this point, if you now make a selection on the map, the colours will remain as they are based on the current range
But what we want to happen, is for the colours to reflect a range based on just the selection (dynamic colour range). For this we need to create parameters
pMinValue
float defaulted to 45.5
pMaxValue
float defaulted to 62.9
Edit the Colour Legend again, and this time set the Start and End fields to reference the pMinValue and pMaxValue parameters.
Add the sheet onto a dashboard. Add a dashboard parameter action
Set Min Value
on select of the sheet, set the target parameter pMinValue to the minimum value from the Voting Ratio of election..
Create another dashboard parameter action
Set Max Value
on select of the sheet, set the target parameter pMaxValue to the maximum value from the Voting Ratio of election..
With this you should now find that when making a selection, the colour range is defined by the minimum and maximum values of just the marks selected
However the downside of this, is if you deselect the marks, the range doesn’t reset, and you then see the whole map coloured based on this more restricted range
Bonus – Building a ‘reset button’
On a new sheet, add Time to Text. Go back to the Map sheet, and set the Time filter to apply to ‘selected worksheets’, and select the new sheet you’re working on.
Change the mark type to Shape and choose a transparent shape (see here for details on how to set this up). Set the display to Entire View, then update the text in the Label (I sourced an arrow character from here). Align the text middle centre, set the background of the worksheet to blue and then update the font of the label text to white.
The intention is when the ‘button’ is clicked, we will set the pMinValue and pMaxValue parameters with the smallest and largest values associated to the year selected. His means we need to have some values on the ‘button’ sheet to pass to the parameters. So we need
Min Value for Year
{FIXED [Time]: MIN([Voting ratio of election for the House of Councillors (Single constituencies) %])}
and
Max Value for Year
{FIXED [Time]: MAX([Voting ratio of election for the House of Councillors (Single constituencies) %])}
Add both of these to the Detail shelf. Hide the Tooltip.
Add this sheet as a floating object to the dashboard. Show the Time filter from either the Map or Button sheets.
Add dashboard parameter actions, similar to the ones we did before
Reset Min Value for Range
on select of the Button sheet, set the target parameter pMinValue to the minimum value from the Min Value for Year field
Reset Max Value for Range
on select of the Button sheet, set the target parameter pMaxValue to the Maximum value from the Max Value for Year field.
Now if you ‘click’ the button sheet, the range should reset to the complete range for the relevant year.
Create the Label
Create a new sheet and add Time to Text. As before set the Time filter from another sheet to apply to ‘selected worksheets’, and select the new sheet you’re working on. Change the mark type to Shape and choose a transparent shape. Set the display to Entire View, and align the text middle centre. Hide the Tooltip.
Create a parameter
pSelectionMade
integer parameter, defaulted to 0
Create a field
Selection Made
1
and another field
Reset Selection
0
Move both fields into the Dimension section of the data pane.
On the Map sheet, add Selection Made to the Detail shelf.
On the Reset Button sheet, add Reset Selection to the Detail shelf.
Create a new field
Label: Selection Made
[pSelectionMade]=1 THEN ‘, Selected Prefecture(s)’ END
Add Label: Selection Made to the Label shelf and adjust the text. Set the background of the sheet to transparent (None)
Add the sheet to the dashboard and create the following dashboard parameter actions
Set Label
on select of the Map sheet, set the target parameter pSelectionMade to the Selection Made field, with no aggregation.
Clear Label
on select of the Reset Button sheet, set the target parameter pSelectionMade to the Reset Selection field, with no aggregation.
Final steps are to then arrange the dashboard as required using floating containers to store the filter, legend, ‘button’ and Label sheets. You’ll also need to change the filter to a single value list and customise so ‘All’ isn’t an option.
Yoshi set this week’s challenge which focused on 2 areas : the use of tools such as CHAT GPT to help us create Tableau RegEx functions (which are always tricksy), and the build of the chart itself.
Creating the REGEX calculated fields
I chose to use CHATGPT and simply entered a prompt as
“give me a regex expression to use in Tableau which extracts a string from another string in a set format. The string to interrogate is”
and then I pasted in the string I’d copied from one of the Session Html fields. I got the following response
along with many more code snippet examples. I used these to create
returns the day of the session as a proper date field, all hardcoded to 2023, since this is when the data is from.
Start Date
DATETIME(STR([Date]) + ” ” + [Start Time])
returns the actual day & start time as a proper datetime field.
End Date
DATETIME(STR([Date]) + ” ” + [End Time])
Duration
DATEDIFF(‘second’, [Start Date], [End Date])
Add fields to a table as below
Firstly, when we build the viz, we’ll need to sort it based on the Start Date, the Session Level (where Advanced is listed first, and All Levels last) and the Duration (where longer sessions take higher precedence) . For this we need a new field which is a combination of information related to all these values.
Sort
STR([Start Date]) + ‘ – ‘ + STR( CASE [Session Level] WHEN ‘advanced’ THEN 4 WHEN ‘intermediate’ THEN 3 WHEN ‘beginner’ THEN 2 ELSE 1 END ) + STR([Duration]/100000)
(this just took some trial and error)
Add this to the table after the Session ID pill and then apply a Sort to the Session ID pill so it is sorting by the Minimum value of the field SortAscending. You should see that when the start time and session level match, the sessions are sorted with the higher duration first.
The viz also lists the AM & PM sessions as separate instances, where the 1st session of the PM session is displayed on the same ‘row’ as the 1st session of the AM session. To handle this we need
Session Index
INDEX()
Make this discrete and add to the table after the AM|PM pill. Change the table calculation so it is computing by all fields except AM|PM and Session Date. The numbering should restart at each am/pm timeslot
Finally, the last field we need to build this viz, is another date field, so we can position the gantt chart bars in the right places. As the viz already segments the chart by Session Date and AM|PM, we can’t reuse the existing Start Date field as this will display the information as if staggered across the 3 days. Instead we want to create a date field, where the day is the same for al the sessions, but just the time differs
Baseline Date
DATETIME(STR(#2023-01-01#) + ” ” + [Start Time])
the date 01 Jan 2023 is arbitrary and could be set to any day. Add this into the table, so you can see what it’s doing. You will probably need to adjust the table calculation on Session Index again to include Baseline Date in the list of checked fields.
Building the viz
On a new sheet, add Session Date to Columns and manually re-order so Tuesday listed first. Then add AM|PM to Columns . Add Baseline Date as a continuous exact date (green pill) to Columns . Add Session Id to Detail then add Session Index to Rows and adjust the table calculation so it is computing by all fields except Session Date and AM|PM.
Create a new field
Size
SUM([Duration])/86400
and add this to the Size shelf. 86400 is the number of seconds in 24hrs, so adjusts the size of the gantt bars to fit the timeframe we’re displaying.
Add Session Level to Colour and adjust accordingly. You will need to readjust the Session Index table calc to include Session Level in the checked fields. Add a border to the bars. Edit the Baseline Date axis so the axis ranges are independent
Add Description and Session Title to Detail and again adjust the Session Index table calc to include these fields too. Apply a Sort to the Session Id field so the data is sorted by the Sort field descending
Add Location, Start Time and End Time to Tooltip, and update accordingly. Then apply formatting to
change font of the Session Date and AM|PM header values
remove the Session Date/ AM|PM column heading label (right click > hide field labels for columns)
Hide the Session Index field
Hide the Baseline Date axis
Add column banding so the Wednesday pane is coloured differently
Add the ability to highlight the Session Title and Description by selecting Show Highlighter from the context menu of each pill
Then add the sheet to a dashboard, using containers to display the colour legend and the highlighter fields.
Lorna set this week’s #WOW2025 challenge asking us to recreate what looked to be a simple line and area chart combination. I have to admit this was indeed a bit tricksy. I built out the data relatively quickly, but when trying to create the area chart I just wasn’t getting any joy.
After lots of trial and error, attempting various options, I eventually resorted to checking out Lorna’s solution. I found there were two specific areas that were causing the creation of the area chart to fail..why this happens is still a mystery….
Before we get on with the solution, I figured it was worth detailing at the start what I tried that failed, just in case you’ve been banging your head against a brick wall too 🙂
What didn’t work
The solution requires table calculations; for one calculation we need a percent of sales per quarter which means we also need to work out the total sales for the quarter. I originally used a FIXED LOD for this but found I could only get the area chart to work if I used a WINDOW_SUM() table calculation instead.
This challenge also requires all the customers for each quarter to be in the view to identify which are in the top X. I used a sorted INDEX() table calculation to identify the top X, but this too failed to work when it came to actually creating the area chart. Using the RANK() table calculation instead worked.
Like I said before why? I don’t know… but putting down as one of ‘just those things’ and hoping that maybe I’ll remember this post in future if it happens again!
Now back to the solution guide…
Creating the calculations
We’re dealing with table calculations, so I’m going to build out the required data in a tabular format to start with.
Create a parameter
pTop
Integer parameter ranging from 5 to 20 with the step size of 5 defaulted to 20
Show this parameter on the sheet, and then create a calculated field
Order Date (Quarters)
DATE(DATETRUNC(‘quarter’, [Order Date]))
Format to the YYYY QX style. Add Order Date (Quarters) to Rows as a discrete exact date (blue pill). Add Customer Name to Rows and add Sales to Text.
We need to identify customers who are in the top x for each quarter. Create a new calculated field
Is Top X Customer?
RANK(SUM([Sales]))<=[pTop]
Add to Rows and adjust the table calculation so it is computing by Customer Name only.
We now want the Sales just for those customers who are in the top x, so create
Top X Sales
IF [Is Top X Customer?] THEN SUM([Sales]) END
Add this field to Rows and we should see that Sales are only displayed for those customers that where Is Top X Customer? = True.
We now need the total of these sales per quarter so create
Total Top X Sales
WINDOW_SUM([Top X Sales])
Add this to the table and adjust the table calculation so both nested calculations are computing by just Customer Name. You should see that the total value is the same for every row for each quarter
We also need the value of the total sales in the quarter regardless of whether the customer is in the top X or not so create
Total Sales per Quarter
WINDOW_SUM(SUM([Sales]))
Add this to the table and again adjust the table calculation to compute by Customer Name only.
Now we have these two figures we can calculate the percentage. Create a new calculated field
Sales % per Quarter
[Total Top X Sales]/[Total Sales per Quarter]
Format this as a % to 1dp. Add to the table.
Duplicate this sheet. I like to do this as I make further changes so I don’t want to lose what I had.
Remove all the surplus field so that only Order Date (Quarters), Customer Name and the Sales % per Quarter fields remain
Create a new field
Customer Index
INDEX()
Convert this field to discrete (right click on the field).
Add this field to the Filter shelf and select 1. Adjust the table calculation so that it is computing using Customer Name only and sorted by SUM of Sales
Re-edit the filter and reselect 1 (making changes to the table calculation resets the filter values).
If everything has been set properly then you should end up with 1 row per quarter. Move the Customer Name pill from Rows to Detail.
We’ve now got the core fields we need to build the viz.
Building the Viz
Once again start by duplicating this sheet. Move Order Date (Quarters) to Columns and change to be continuous (green pill), then move Sales % per Quarter to Rows.
Add another instance of Order Date (Quarters) to the path shelf and change to be an attribute. You should now have a line chart. Increase the size of the line a little.
Add another instance of Sales % per Quarter to Rows, making sure the table calculation is set exactly as the existing version. Change the mark type of the 2nd marks card to Area.
Make the chart dual axes and synchronise axes .
Finally, tidy up the chart by
Adjusting the Tooltip
Removing gridlines, zero lines and row/column dividers
Hide the right hand axis
Fix the left hand axis to end at 1 (so the axis goes to 100%)
Edit the left hand axis title
Update the sheet title to reference the pTop parameter
Then add the viz to a dashboard. My published viz is here.
For #WOW2025 Week 25, Kyle challenged us to make use of Set Actions to recreate a viz where the ‘viz in tooltip’ updates based on the option the user interacts with on the base viz.
Modelling the data
The excel file provided contained 3 sheets which needed to be combined to use for this challenge. My data model looks like this
Attendance is related to Divisions on the fields Tm = Team
Attendance is also related to Record on the fields Tm = Tm.
Building the Base Bar Chart
On a new sheet, add Division to Rows and Attend/G to Columns changing the aggregation to AVG.Sort the data descending.
Change the Colour of the bars to dark grey and show mark labels to display the average attendance per game value. Add W-L% and Est.Payroll fields to the Tooltip shelf and set both to be AVG. Format Est. Payroll to be $ with 0dp. Format W-L% to be formatted to 3dp, but then adjust again and use a custom format to remove the leading 0 (,##.000;-#,##.000)
Format the label to be bold and to match mark colour. Format the row labels, remove the row label heading (right click > hide field labels for rows). Hide the axis and remove all gridlines, axis rulers etc. Update the viz title and name the sheet Bar-Division or similar.
Build the Scatter Plot
On a new sheet add W-L% to Columns and Attend/G to Rows, setting both the use an AVG aggregation, and then add Tm (from the Attendance table) to Detail. Adjust the W-L% axis so it doesn’t always include 0 (right click axis > edit axis> uncheck include zero). Adjust the title of the axis too. Adjust the title of the Attend/G axis too. Change the mark type to circle and increase the size.
We need the chart to show a difference between the marks related to a selected Division and those which aren’t. Create a set from the Division field (right click the field > create > and select NL West.
Add Division Set to Colour and adjust accordingly. Add a dark grey border to the circles. Remove all gridlines and name the sheet Scatter or similar.
Build the Attendance by Team Bar Chart
On a new sheet, add Tm (from the Attendance table) to Rows and Attend/G to Columns, setting the aggregation to AVG. Sort descending. Add Division Set to Colour.
Create a new field
Label Attendance
IF [Division Set] THEN [Attend/G] END
and add to the Label shelf. Format the label so it is bold and set to match mark colour. Hide the row label heading and the axis. Hide all gridlines, zero lines et. Name the sheet Bar-Team or similar.
Adding the Viz in Tooltip
Navigate back to the Bar-Division sheet. Update the Tooltip to reference the Division and the other top level measures. I used a double tab between the headings on one line and again on the values on the next line to make the information line up on hover (even though they look misaligned on the tooltip dialog).
To add each sheet to the tooltip, use Insert > Sheets button in toolbar and select the Scatter sheet and then press tab and select the Bar Team sheet.
Then adjust the text inserted so both filtering sections state filter=”None”. This stops the VIT filtering out by default all the data that isn’t associated to the selection you’re coming from. Adjust maxwidth and maxheight to 350 (I had it set larger, but then it didn’t display properly on Tableau Public, so had to adjust there.
Adding the interactivity
Create a dashboard and add the Bar-Divison sheet. At this point showing the tooltip on each bar will always show the information related to NL West since that was the option selected when we created the set. To fix this, create a new dashboard set action
Set Division
On hover of the Bar-Division sheet, target the Division Set, and assign values to the set. When the selection is cleared, keep set values. Only allow this to be run on single-select only
And now as you hover over different bars, the highlighted circles and bars in the viz in tooltip will change.
For this week’s #WOW2025 challenge, I looked to recreate part of Kathryn McCrindle’s TC25 Iron Viz Final dashboard. The main focus was building the ‘cockpit dials’ – the concentric donut charts, but I also wanted to use the challenge to highlight a couple of techniques Iron Viz finalists use to make the build quicker – background images and custom themes, a new feature introduced in v2025.1. I provided 3 files for this challenge, accessible from here.
A simplified dataset
An image for the background
A json file for the custom theme.
Checking the numbers
To start, we’ll build out some of the calculations needed to sense check the logic.
After connecting to the data, make sure all the fields are defined as dimensions (above the Measure Names line on the data pane) – if they aren’t drag them up. The only field in the Measure Values section should be the count of the records.
We first need to identify how many times each part was recorded in a strike (or not). Drag Part to Rows and Struck? to Columns. Create a new field
# Strikes
COUNTD([Strike ID])
Add to Text. This gives our basis for the inner ring.
The True values above, form the basis for which we then want to understand the split between whether the struck part was then damaged or not. On a new sheet, add Part to Rows and Damaged? to Columns, then create field
# Struck
COUNTD([Strike ID – Is Struck])
and add to Text. Additionally add row grand totals (Analysis menu > Totals > Show Row grand totals). The total for each row should match the True value from the previous sheet (pop the sheets side by side on a dashboard to sense check).
Once again, the Damaged? =True values, form the basis for which we then want to understand the split between whether the struck part was then substantially damaged or not. On a new sheet, add Part to Rows and Substantially Damaged and Part Damaged? to Columns, then create field
# Damaged
COUNTD([Strike ID – Is Damaged])
and add to Text and show row grand totals. Add this sheet to the dashboard too. The total for this sheet should match the True values from the Damaged sheet.
Building the Donuts
Now we’re happy with the numbers, we can start to build the viz. We’ll be using map layers for this, but as we don’t actually have a geometric datatype field in the data set, we need to create one. I’ll be following the core principles discussed in this blog post I wrote for the company I work for, Biztory. In the blog post, we’re just building 1 donut chart, so created a Zero point using MAKEPOINT(0,0). In this instance, I want multiple donuts arranged in rows and columns. This can be done in 2 ways:
use the MAKEPOINT(0,0) method and then create 2 dimension fields added to Rows and Columns that represent the row and column for each Part (akin to what we do when building trellis/ small multiple charts) or
use the MAKEPOINT method to define the point to position the donut. This is the method I’m using, but will break the fields required down, so it’s clear.
Create a new field
Row
CASE [Part] WHEN ‘Engine’ THEN 1 WHEN ‘Nose’ THEN 1 WHEN ‘Windshield’ THEN 1 WHEN ‘Lights’ THEN 2 WHEN ‘LandingGear’ THEN 2 WHEN ‘Radome’ THEN 2 WHEN ‘WingOrRotor’ THEN 3 WHEN ‘Fuselage’ THEN 3 WHEN ‘Propellor’ THEN 3 WHEN ‘Tail’ THEN 4 WHEN ‘Other’ THEN 4 END
Column
CASE [Part] WHEN ‘Engine’ THEN 1 WHEN ‘Nose’ THEN 2 WHEN ‘Windshield’ THEN 3 WHEN ‘Lights’ THEN 1 WHEN ‘LandingGear’ THEN 2 WHEN ‘Radome’ THEN 3 WHEN ‘WingOrRotor’ THEN 1 WHEN ‘Fuselage’ THEN 2 WHEN ‘Propellor’ THEN 3 WHEN ‘Tail’ THEN 1 WHEN ‘Other’ THEN 2 END
Ensure both of these are dimensions, by dragging above the Measure Names on the data pane if required. These fields are simply ‘hardcoding’ the position of each Part (and can be used if adopting the ‘trellis’ route).
Then create a field
Grid Position
MAKEPOINT([Column],[Row])
On a new sheet, add Grid Position to Detail and for now, add Part to the Label shelf so we can see what Part each mark represents.
Change the mark type to Circle.
Drag another instance of Grid Position on to the canvas and drop when you see the option Add a Marks Layer. This will create another marks card called Grid Position (2).
Now we have 2 layers, we can reposition the marks. Note – doing the next steps before adding a 2nd marks layer, won’t let you then create any layers.
Use the swap axis button to change the axis so Latitude is on Columns and Longitude on Rows. Edit the Longitude axis (y-axis) and set the scale to be reversed. The Parts should all now be in the right place.
Now we can start to make the donuts. When using map layers, its good practice to rename the marks cards, so start by renaming the marks card Grid Position to be Centre and Grid Position (2) to be Struck Ring.
On the Centre marks card, change the colour to be dark grey (#44505b) and increase the size. Create a new field
Label – # Substantially Damaged
COUNTD(IF [Substantially Damaged and Part Damaged] THEN [Strike ID – Is Damaged] END)
Add this to the Label shelf and move Part to Detail instead. Position the label middle centre, but don’t adjust the font style in any way.
Create new fields
Tooltip – #Damaged
COUNTD(IF [Damaged?] THEN [Strike ID – Is Struck] END)
and
Tooltip – #Struck
COUNTD(IF [Struck?] THEN [Strike ID] END)
and add these to the tooltip shelf of the Centre marks card, and adjust Tooltip text – again don’t adjust the font style.
Click on the Struck Ring marks card. Drag it so it is under the Centre marks card. Add Part to Detail. Increase the Size until you can see a ring larger than the centre circle. Change the Mark Type to Pie and add #Strikes to the Angle shelf. Add Struck? to Colour. Adjust the colours, and re-order the colour legend so True is listed first.
Disable Selection on the Struck Ring marks card
Add another layer by dragging Grid Position on to the canvas. Rename this marks card to Inner Dark Grey Ring and move the card to the bottom. Add Part to Detail and then increase the Size. Change the mark type to Circle and set the colour to dark grey. Disable selection of the card.
Add another layer by dragging Grid Position on to the canvas. Rename this marks card to Damaged Ring and move the card to the bottom. Add Part to Detail and then increase the Size. Change the mark type to Pie, add #Struck to Angle and add Damaged? to Colour and adjust. Reorder the colour legend so True is listed first. Disable selection of the card.
Add another layer by dragging Grid Position on to the canvas. Rename this marks card to Outer Dark Grey Ring and move the card to the bottom. Add Part to Detail and then increase the Size. Change the mark type to Circle and set the colour to dark grey. Disable selection of the card.
Add the final layer by dragging Grid Position on to the canvas. Rename this marks card to Substantial Damage Ring and move the card to the bottom. Add Part to Detail and then increase the Size. Change the mark type to Pie, add #Damaged to Angle and add SubstantiallyDamaged and Part Damaged? to Colour and adjust. Reorder the colour legend so True is listed first. Disable selection of the card.
Note – you may find you have to tweak the sizes of each layer – when published to Tableau Public, you can do this more accurately by setting a size % – on Desktop you just have to guess the slider position
Add in the custom theme
On the Format menu, select import custom theme and select the json file provided. When prompted select Override to apply the theme.
The font style and other properties of the chart should immediately update
Hide the axis, remove axis rulers and re-name the sheet.
Create the dashboard
Create a dashboard and set it to 1000 x 900. Add an Image object onto the dashboard, and select the background image provided, setting it to fit and centred. Remove the outer padding from both the image object, and the tiled container, so that the image object is exactly 1000 x 900.
You can see, that the labels for each donut, are actually part of the background image. So we now need to add the viz so it is positioned in the right place.
Change the dashboard objects to be ‘floating’ and add the Donut viz. Remove the Title, and delete the colour legends. Adjust the position and height/width to be x=448, y=104 and width=516 and height = 762 (this was done by trial and error – Iron Vizzers will have practiced so many times, they will know what they want these numbers).
But the background isn’t visible through the chart, so right click on the Donut sheet (while its on the dashboard), and Format and then set the background to be set to None.
And that is the crux of the challenge. I then added a floating title along with my standard footer. My published viz is here.
Sean set this table calculation based challenge this week. The key to this was for the reference line & bands to change if any of the non-date related filters were change, but for the values to remain constant regardless of the timeframe being reported. Based on this, I figured a table calculation filter would be necessary for the timeframe filter, as these get applied at the end of the ‘order of operations’.
I used the data set Sean provided in the challenge, to ensure I would get the same values to verify my solution. After connecting to the dataset, I did set the date properties on the data source so the week started on a Sunday, again to ensure the numbers aligned (in the UK, my preferences are set to Mondays).
Building out the calculations
Since we’re working with table calcs, I’m going to build the calcs into a table first, so we can verify the figures are behaving. Start by adding Order Date to Rows as a discrete (blue) pill at the Week level. Then add Sales.
Create a new field
Median
WINDOW_MEDIAN(SUM([Sales]))
and add this into the table. The value should the same for every row.
Create a new field
Std D
WINDOW_STDEV(SUM([Sales]))
and add this too – again this should be the same for every row.
But we want to to show the distribution based on +1 or -1 standard deviations, so create
Std Upper
WINDOW_AVG(SUM([Sales])) + [Std D]
and Std Lower
WINDOW_AVG(SUM([Sales])) – [Std D]
and add these to the table.
Each mark needs to be coloured based on whether it is greater than the upper band, so create
Sales above Std Upper
SUM([Sales]) > [Std Upper]
and add to the table on Rows
Add Category, Sub-Category and Segment to the Filter shelf, and show the filters. Adjust them to see that the table calculation fields change, although still remain the same across every row.
To restrict the timeline displayed, we need a parameter
pWeeks
integer parameter defaulted to 18
Show this parameter on the canvas. Then, to identify the weeks to keep, create a new field
Index
INDEX()
and convert it to discrete, then add to Rows to create a ‘row number’ for each row.
But, I want the index to start from 1 from the end of the table, so adjust the tableau calculation on the Index field so that is it computing explicitly by Week of Order Date and is sorted by Min Order Date descending
The first row in the table is now indexed with the greatest number, and if you scroll down, the last row should be indexed with 1.
With this we can now created
Filter – Date
[Index]<=[pWeeks]+1 AND [Index]<>1
I was expecting just to have [Index}<=pWeeks, but Sean’s solution excluded the data associated to the last week, I assume as it wasn’t a full week, so the above calculation resolved that.
Add this to the Filter shelf and set to True.
Changing the value in the pWeeks parameter only should adjust the number of rows displayed, but the values for the table calculated fields shouldn’t change.
Building the viz
On a new sheet, add Order Date as a continuous (green) pill at the week level to Columns and Sales to Rows.
Add another instance of Sales to Rows. Change the mark type of the Sales(2) marks card to circle. Make the chart dual axis and synchronise axis.
On the All marks card, add Median, Std Upper and Std Lower to the Detail shelf.
Add a reference line to the Sales axis, referencing the Median value. Set it to be a thicker darker line.
Then add another reference line but this time set it to be a band and reference the Std Lower and Std Upper fields, selecting a pale grey fill.
On the Sales(2) marks card, add Sales above Std Upper to the Colour shelf, and adjust to suit. Add a border to the circles. If required, adjust the colour and size of the line on the Sales marks card too.
Add the Category, Sub-Category and Segment fields to the Filter shelf, and show the filters. Show the pWeeks parameter. Then add Filter-Date to the Filter shelf. Adjust the table calculation as described above, then re-edit the filter to just show the True values
Finally tidy up by
remove row and column dividers
hide the right hand axis (uncheck show header)
edit the date axis and delete the title
Then add all the information to a dashboard and you’re good to go!
Erica set this fun and incredibly useful challenge this week, based on the TC25 talk by Lorna Brown & Robbin Vernooij, to showcase different methods of normalising data when comparing measures which have drastically different scales.
Building the Raw Values chart
Add Sales to Rows. Then drag Quantity on to the canvas and drop the pill on the Sales axis (when you see the ‘2 column’ icon appear). This has the affect of adding the fields onto a shared axis, and the sheet will update to automatically reference Measure Names and Measure Values. Swap Quantity so it is displayed below Sales in the Measure Values section.
Add Region and Category to Detail and change the Mark type to Circle.
I’m going to incorporate the last requirement at this stage, as it helps with the build, so create parameters
pSelectedRegion
string parameter, defaulted to West
pSelectedCategory
string parameter, defaulted to Furntiture
show both these parameters on the sheet.
Create a new field
Is Selected Region & Category
[pSelectedCategory]=[Category] AND [pSelectedRegion]=[Region]
Add this field to Colour, and swap the values in the legend, so True is listed first. Then change the Region on the Detail shelf, so it is also on colour, by adjusting the icon to the left of the pill. Adjust the colours as required and then reduce the opacity on colour to 80%.
Manually update the entry in the pSelectedRegion parameter to each Region, so the True-<Region> colour combination can be updated to the dark grey.
Add Is Selected Region & Category to Size. Edit the size so they are reversed and the range in size is closer than the default. Once done, then manually adjust the dial on the Size shelf.
Show mark labels, selecting the option to only show the min & max values per cell and aligning middle right
Update the Tooltip. Then create fields True = TRUE and False = FALSE and add both of these to the Detail shelf. We’ll need these to disable the default highlighting later (adding now, as for all the other sheets, we’ll duplicate this one, so makes things easier).
Show the caption (Worksheet menu > show caption) and update the caption to reference the website Erica refers to. Then update the title of the sheet, and name the tab Raw or similar.
Building the Decimal Normalisation chart
Duplicate the Raw sheet, and name Decimal or similar. Update the title.
Create new fields
Sales – Decimal
SUM([Sales]) / 10^6
Quantity– Decimal
SUM([Quantity]) / 10^4
Drag Sales – Decimal onto the canvas and drop directly over the existing Sales pill in the Measure Values section, so it replaces it. Do the same with the Quantity – Decimal pill. Uncheck Show Labels.
Add constantreference line of 0 that displays as a black solid line at 100% opacity
Repeat and create a constant reference line with value of 1. Edit the axis and fix from -0.05 to 1.05 and remove the axis title.
Update the text in the caption.
Building the Max-Min Normalisation Chart
Duplicate the Decimal sheet and rename Max-Min or similar. Update the title.
Drag Sales – Max-Min onto the canvas and drop directly over the existing Sales – Decimal pill in the Measure Values section, so it replaces it. Do the same with the Quantity – Max-Min pill.
Adjust the table calculation setting for each of the measures so they are computing by Category, Region and Is Selected Region & Category.
Adjust the Tooltip if required. Right click on the bottom column headings and Edit Alias to update the text- you may not be able to rename Sales – Max-Min along xyz… just to ‘Sales’, so you may need to be creative and add spaces eg ‘ Sales ‘ or similar. Update the caption.
Building the Z-Score Normalisation Chart
Duplicate the Max-Min sheet, and name Z-Score or similar. Update the title.
Drag Sales – Z-Score onto the canvas and drop directly over the existing Sales – Max-Min pill in the Measure Values section, so it replaces it. Do the same with the Quantity – Z-Score pill.
Adjust the table calculation setting for each of the measures so they are computing by Category, Region and Is Selected Region & Category. Remove the reference line for the constant value of 1. Edit the axis, so the range is now Automatic rather than fixed.
As before, adjust the Tooltip again if required, edit the column labels using the alias feature, and update the caption.
Creating the dashboard and adding the interactivity
Add all 4 charts onto a dashboard, using a horizontal container to arrange the charts side by side. From the object context menu on the dashboard, select the option to show the caption
To disable the default highlighting ‘on click’ create a dashboard filter action based on the True/False method described here – you’ll need to create an action per sheet.
To set the parameters, create a parameter action
Set Category
On select of all the sheets, set the pSelectedCategory parameter passing in the value from the Category field.
Create another similar action called Set Region which sets the pSelectedRegion parameter with the value from the region field.
Finally, add a text section to the top right of the dashboard that references the pSelectedRegion and pSelectedCategory parameters.
Donna + DataViz 