Kyle set the #WOW2025 challenge this week, using a summarised data set based on Superstore that he included in the challenge page.
Building out the core data fields
I started this challenge, by building out the data in a tabular format, so I could verify the calculations I needed. I focused on the ‘month’ level to start with before tackling the ‘year’ level which had the added requirement of containing ‘complete years only’, which I did find a bit tricky. Anyway, to start we need to determine the number of months between the First Order Date and the Order Date.
Months from 1st Purchase
DATEDIFF(‘month’, [First Order Date], [Order Date])
and we also need to identify the number of customers
Move this pill into the Dimension section of the left hand pane (drag it up to the top section above the line)
Count Customers
COUNTD([Customer ID])
and calculate the sales per customer
Sales per Customer
SUM([Sales])/[Count Customers]
format this to $ with 0 dp. Also format Sales to $ with 0 dp.
On a new sheet, add First Order Date to Rows as an exact date dimension (blue pill). Add Months from 1st Purchase Date to Rows. Then add Count Customers, Sales and Sales per Customer into the view.
Add First Order Date to the Filter shelf and select the Month/Year format, then select the 4 months Kyle used – Jan 2021, May 2021, Jun 2022, Nov 2023). Show the filter on the sheet.
Building the Month Level Viz
With the information we have, we can build out the viz at the ‘month’ level of granularity.
On a new sheet, add Months from 1st Purchase Date to Columns and Sales per Customer to Rows. Add First Order Date to the Filter shelf at the Month/Year level and restrict to the relevant dates. Show the filter control. Add First Order Date to Colour and set to the Month/Year level as a discrete (blue) pill. Adjust the colours to suit. I used colours from a palette called CB_Paired I had installed.
Set Stacked Marks to Off (Analysis Menu > Stack Marks > Off).
Create another instance of First Order Date (right click the field and select Duplicate) to crate First Order Date (copy). Rename this First Order Date (for Size). Add to the Size shelf, and set it to be at the Month/Year level and discrete (blue) pill.
Adjust the Sort on the First Order Date (for Size) pill to be by Data source order Descending. This now makes January wider than November.
And then add First Order Date to the Detail shelf at the Month/Year level as a discrete (blue) pill. By default this pill is sorted ascending, and has the effect of moving January to the back and November to the front so all the bars (at least for the 0 entry) are visible. This is why we needed a duplicate instance of First Order Date and we needed it to be sorted in one direction to make the correct months at the front, and in another direction to get the required bar widths.
Add Sales and Count Customer to the Tooltip shelf and adjust the Tooltip as required.
Handling the ‘Year’ requirement
Firstly for this, we need a parameter to drive the change in visual.
pGrain
string parameter listing ‘month; and ‘year’ and defaulted to ‘year’
Our Columns is currently listing the Months from 1st Purchase. We need this to be reflective of the years from 1st purchase instead based on the parameter selected.
Years from 1st Purchase
FLOOR([Months from 1st Purchase]/12)
this rounds the calculation down to the nearest whole number.
Move this into the dimension section of the data pane, and add to Rows of our tabular display, so you can see what it’s doing.
Our X-Axis will then be based on either of these 2 fields
X-Axis
IF [pGrain] = ‘month’ THEN [Months from 1st Purchase] ELSE [Years from 1st Purchase]+1 END
Add this into the table, and show the pGrain parameter and see the change as you flip the options.
We then need to handle the ‘incomplete year’ requirement. This did take some trial and error, and I’m not totally convinced what I’ve done will work in all scenarios, but it seems to match Kyle’s results for the spot checks I did…
I started by determining the maximum month from 1st purchase in each cohort.
Max months per cohort
{FIXED [First Order Date]: MAX([Months from 1st Purchase])}
Add this into the table as a discrete dimension (blue pill), and each row should match the final Months from 1st Purchase value for each First Order Date
I then created a field to identify which of the rows in the data I wanted to keep. So in the case of the above examples for 01/11/2023, I want the rows where Months from 1st Purchase is from 0 to 11 as these represent a whole year’s worth of data for Years from 1st Purchase = 0 (even though there isn’t a a purchase every month in the year). I ended up doing this with the following calculation
Identify Cut Off
[Months from 1st Purchase] < 12 * (FLOOR(([Max months per cohort]+1)/12))
so based on the above example for 01/11/2023, get me all the rows where Months from 1st Purchaseis less than 12 * (FLOOR((13+1)/12) = 12 * (FLOOR(14/12)) = 12 *1 = 12.
Add this into the table
I then created an additional field to filter by
Records to Display
[pGrain] = ‘month’ OR [Identify Cut Off]
and added this to the Filter shelf and set to True.
When we’re at the ‘month’ level, all rows will be included, otherwise, just those we identified will be.
So now we have this, we can adjust the visual (you may choose to take a copy of what was initially built, just in case).
Adjusting the Viz for the Granularity parameter
Show the pGrain parameter on the viz sheet and set to Year. Add Records to Display to the Filter shelf and set to True. Add X-Axis to Columns and remove Months from 1st Purchase Date. Set sheet to Fir Width.
Test changing the parameter and altering the selected dates and verify the behaviour is as expected.
Finally tidy up the formatting – remove all gridlines; edit the axis and remove the title; hide the X-Axis label heading (right click > hide field labels for columns). Add to a dashboard and arrange the colour legend in a single row, and set the First Purchase Date filter to be a multi-value dropdown.
This week’s challenge once again has 3 levels, so I will blog the first solution and then build on/adapt from there. The main requirement is focused on working out how to rank the football teams based on multiple measures, and this is relevant for all the levels. Once again, we’re looking at data related to football teams in the English Premier league, and where they finished each season.
Defining the calculation to rank by
A team’s finishing position is based on a combination of points scored, goal difference and goals scored. But on rare occasions, a team is deducted points in the season, so we need to account for that.
Adjusted Points
SUM([Points]) – ZN(SUM([Points Deducted]))
Putting the key measures (Adjusted Points, Goal Difference and Goals For) per Team into a tabular view for just a single Season (eg 1993), we can see multiple instances of teams ending on the same number of Adjusted Points
Just ranking on this field, won’t necessarily order the teams in the correct order as Goal Difference and Goals For also need to be taken into consideration. We need to create a field we can rank based on a combination of all three fields, and to do this we’ll ‘weight’ each field by multiplying each field by a suitable proportion based on precedence. In the example shared via the community forum post the leading indicator was multiplied by 1000. In my case I’m going to divide.
Add this field into the table, and sort by it descending. You can see that for the 3 teams with 52 points, two of those teams have the same Goal Difference too, but are being correctly ordered based on the Goals For due to the result of the Adj Points | GD | GF calculation
Now we’ve got the core calculation nailed, we can start to build the viz.
Building the Beginner viz
On a new sheet, add Team to Rows and Season End Year to Columns.
We want to be more specific about the season, so create
Label Season
STR([Season End Year]-1) + ‘-‘ + RIGHT(STR([Season End Year]),2)
and add to Columns too.
To make the ‘squares’ rather than use the square mark type which can sometimes be a bit fiddly to get the size just right, I’m going to use a fake axis and a bar mark type.
Double click into Columns and type MIN(1.0) and change the Mark type to Bar. Adjust the Size to be as big as possible. Edit the axis to be fixed from 0-1.
Hide the Season End Year (right click the pill and uncheck Show Header). Rotate the Label Season (right click on the actual label and Rotate Label). Then make each column narrower and widen each row until you’re happy with the sizing.
We want to the Teams to remain listed in alphabetical order, but we want to know the position the team finished in the season, so we create
Rank Desc
RANK([Adj Points | GD | GF], ‘desc’)
Initially add this to Label, and then adjust the table calculation so it is explicitly computing by Team only.
You can verify the number associated to the teams in the 1993 season with the sorted table you made earlier.
But we’re not going to show the rank for every square, so move the Rank Desc from Label to Tooltip.
We do want to label just the team in first though so create
Is First
IF [Rank Desc] = 1 THEN ‘1st’ END
and add this to the Label shelf instead. Again adjust the table calculation as described above.
For the Tooltip we need to create
Tooltip Rank Suffix
IF [Rank Desc] = 1 OR [Rank Desc] = 21 THEN ‘st’ ELSEIF [Rank Desc] = 2 OR [Rank Desc] = 22 THEN ‘nd’ ELSEIF [Rank Desc] = 3 OR [Rank Desc] = 23 THEN ‘rd’ ELSE ‘th’ END
add this to the Tooltip shelf, adjust the table calculation as required. Also create
Tooltip – Note
IF ZN([Points Deducted])<>0 THEN ‘Additionally the team was deducted ‘ + STR([Points Deducted]) + ‘ points due to ‘ + [Notes] END
And then add Adjusted Points and Tooltip – Note to the Tooltip too. Adjust the Tooltip as required.
Finally adjust the look and feel by
Set the background colour of the worksheet to pale lilac (#f1eff6)
Set the mark colour to purple (#938bc1) and add a pale grey border
Set row and column dividers to a thin solid pale grey line (#e6e6e6)
Adjust the font colour of the row and column labels to black
Hide the Team row heading label (right click > hide field labels for rows).
Hide the Label Season column heading label ((right click > hide field labels for columns).
Hide the MIN(1.0) axis (right click pill – uncheck show header)
Make the mark label bold and centred
Set the sheet to fit width
Add a title
Name the sheet Beginner or similar
Building the Intermediate Viz
The requirement here, is we now need to highlight the teams that won and those that were relegated each season.
For this, we’re going to create another ranking field, but this time ranking in the other direction
Rand Asc
RANK([Adj Points | GD | GF], ‘asc’)
and then determine the team’s position in the season, giving consideration to the fact that in the 1994-95 season, 4 teams were relegated instead of the usual 3.
Position
IF [Rank Desc] = 1 THEN ‘C’ ELSEIF (MIN([Season End Year]) =1995 AND [Rank Asc] <=4) OR ([Rank Asc] <=3) THEN ‘R’ END
Duplicate the Beginner viz. Remove Is First from Label and add Position instead. Add Position to Colour and adjust accordingly.
Building the Advanced Viz
Now we want to extend the colouring, so the teams who were not the champions and who weren’t relegated, are coloured based on their league position.
For this duplicate the Intermediate sheet. Add Rank Desc to the Detail shelf, verifying the table calc is computing by Team only. Change it to be a discrete (blue) pill, and the change the pill so it is also on the Colour shelf, by updating the ‘detail’ icon to the left of the pill.
This will result in 2 pills on the Colour shelf and the Colour Legend listing all the permutations.
Adjust the colour legend, so all the R’s are red, the C is yellow, and the Nulls are set from automatically assigning the BuPu 3 Color Seq Palette. Use ctrl (windows) to select multiple entries in the colour legend, rather than assigning one by one.
Note if you don’t already have the BuPu 3 Color Seq Palette in your preferences file, you will need to add and then reload Tableau. For further information see here.
And that should complete all levels of the challenge. My published viz is here.
This week’s challenge was a guest post by Felicia Styer, who wanted us to make multiple selections using just a single parameter, rather than any groupings or sets.
We’ll start by focusing on the initial requirement, which was to bucket into Sub-Categories selected and those not.
Setting up the parameter
The main functionality is controlled via a single string parameter which will just contain a string of 0s and 1s. 0 indicates the Sub-Category is not selected, 1 indicates it is. The position of the 1 or 0 in the string represents the associated Sub-Category. There are 17 Sub-Categories in the data set, so we need to create a parameter of 17 characters. Arbitrarily set some entries to 1 and the rest to 0.
Binary Parameter
string parameter containing the string 01000101000010000
Building the Sales by Subcategory viz
Add Sub-Category to Rows and Sales to Columns. Sort by Sales descending.
We want to assign a number for each row. We can use Index() or Rank the rows based on Sales. I chose to to the latter
Sub Cat Rank
RANK_UNIQUE(SUM(Sales), ‘desc’)
Format this to a number with 0dp but prefixed with #
Add this to Rows, change to discrete (blue pill) and then move to be listed before Sub-Category.
We want to colour the bars based on the ‘bucket’ they’re in according to the Binary Parameter.
Bucket
MID([Binary Parameter],[Sub Cat Rank],1)
This returns the character that is in the nth position in the string – ie Tables is ranked fourth, so this calculation will return the 4th character in the Binary Parameter string.
Add this to Colour and adjust accordingly. Show the Binary Parameter on the sheet, and then adjust the values between 1 and 0 to see the bars change colour.
When a bar is clicked, we want to update the parameter. We will use a dashboard parameter action to drive this functionality, but we need to pass a value into the parameter. This value needs to be a 17 character string of 1s or 0s, where only the character at the nth position based on the rank needs to differ.
For example, the string 01000101000010000 indicates Phones is selected – it’s ranked 2nd in the list and the 2nd character of the string is a 1. When Phones is clicked, we want it to become unselected. So the character in the 2nd position needs to change to a 0, while all the other characters remain the same.
Value for Param
IF [Bucket] = “0” THEN LEFT([Binary Parameter],[Sub Cat Rank]-1) + “1” + MID([Binary Parameter],[Sub Cat Rank]+1) ELSE LEFT([Binary Parameter],[Sub Cat Rank]-1) + “0” + MID([Binary Parameter],[Sub Cat Rank]+1) END
If the row is currently in Bucket 0, then get the portion of the Binary Parameter string before the nth term, concatenate it to 1 and then concatenate that with the portion of the Binary Parameter string after the nth term, otherwise, the row is associated to Bucket 1, so concatenate the preceding and following string with a 0 instead.
Add this to the Detail shelf.
Finalise the display by adding Sales to the Label, removing row & column dividers, updating the Tooltip, hiding the column headers and adjusting the font size.
Adding the selection interactivity
Add the sheet to a dashboard. Add a dashboard parameter action
Set Bucket
On selection of the sheet, set the Binary Parameter parameter passing the value of the Value for Param field. Leave the parameter with the current value when selection is cleared.
Click a bar to test the functionality. The bars should be changing colour. However, on click, Tableau automatically highlights the selected bar and the others ‘fade’. We’re already using colour to identify what’s been selected, so don’t want this to happen. To resolve this we will apply the True/False method to deselect the marks which is documented here.
You will need to create True and False calculated fields, and add them to the Detail shelf of the viz sheet. Then add a dashboard filter action as below.
Now when you click, the bars immediately change to the right colour with a single click of the mouse.
Building the Sales by Bucket bar chart
On a new sheet, add Sales to Columns, Bucket to Rows and Sub-Category to Detail. Adjust the table calculation setting of the Bucket pill so it is computing explicitly by Sub-Category.
Add Bucket to Colour and again adjust the table calculation as above. When you hover over the bar, you will see it is actually a stacked bar of each Sub-Category. We want these ordered so those with the smallest sales are on the left. Apply a Sort to the Sub-Category field on the Detail shelf to sort by Sales Descending
Widen each row. Add Sales to Label and set the Label to only show when selected (so they only appear when the segment of the bar is clicked on)
Add a Reference line to the Sales axis that shows the Sum of Sales per cell, and displays the Value. Don’t show any line or tooltip, and ensure the reference line isn’t recalculated when the bar chart is clicked.
Format the reference line label so it is positioned right middle, and adjust the font size.
Once again remove any row/column dividers and row headings and adjust the font sizes.
Arrange this chart onto the dashboard with the other chart using layout containers as required.
For the bonus challenge to use more than 2 buckets, there was no example actually published. So I interpreted it as each click added it to the next bucket until you reached the maximum number of buckets allowed, at which point the Sub-Category would become deselected.
For this I created a parameter
# of Buckets
integer parameter from 1 to 5
The expectation in this instance was that rather than a string of 1s and 0s in the parameter, the parameter could contain any number from 0 up to # of Buckets – 1.
So the Value for Parameter field just had to change to become
IF [Bucket] = “0” THEN //we’ve clicked once so move it to 1st bucket LEFT([Binary Parameter],[Sub Cat Rank]-1) + “1” + MID([Binary Parameter],[Sub Cat Rank]+1) ELSEIF [Bucket] = STR([# of Buckets]) THEN //we’re already at the end, so reset to the starting position of 0 LEFT([Binary Parameter],[Sub Cat Rank]-1) + “0” + MID([Binary Parameter],[Sub Cat Rank]+1) ELSE // need to move to the next bucket LEFT([Binary Parameter],[Sub Cat Rank]-1) + STR(INT([Bucket])+1) + MID([Binary Parameter],[Sub Cat Rank]+1) END
The Colours associated to the Bucket field also then need to be updated to handle however many buckets you have, which you can set initially by manually updating the Binary Parameter parameter.
Note – as I have both versions in my workbook, I have fields suffixed with ‘bonus’ to represent the calculated fields/parameters needed.
This week’s #WOW2025 challenge was a guest post by Kieran Adair and had two parts to it; one to show Sales and Profit Ratio for 2024 only on the same chart, and the other to show comparisons to 2023 data on the same chart.
I built the first one way, and had to make changes in order to build the second. I could have chosen to rebuild the first chart using the fields I ended up creating for the second, but I chose not to, so this will ultimately be 2 blogs in one, as for each chart I needed different fields.
Restricting the data
We only need data for 2023 and 2024 for this challenge, so apply a data source filter (right click data source > Add data source filter) and restrict to Years 2023 and 2024 only.
Building the 2024 chart
We will be plotting Profit Ratio so need to create
Profit Ratio
SUM([Profit])/SUM([Sales])
format this to % with 0 dp.
Add Sub-Category to Rows and Profit Ratio to Columns. Add Order Date to Filter and restrict to the Year 2024 only. Sort the bars descending |(just click the icon on the toolbar)
Change the Mark type to circle and set the colour to black. Widen the rows a little.
Add Sales to Columns. Change the mark type of the sales marks card to bar. Set the colour to white and add a black border.
Adjust the Tooltip on the All marks card and verify both measures are displayed whether hovering over a circle or a bar.
Edit the Sales axis and set to custom to ensure it is fixed to start at 0 and end ‘automatic’. Adjust the axis title to include the text [Bars◻].
Make the chart dual axis BUT DO NOT synchronise axis. Remove Measure Names from the Colour shelf of the All marks card. Right click on the Sales axis at the to and Move marks to back.
Edit the title of the Profit Ratio axis so that it includes the text [Circles ●]. Remove all gridlines and zero lines and hide the Sub-Category row heading (right click the text and hide field labels for rows). Update the title and name the sheet RAVE 2024 or similar.
Building the YoY chart
For this version, we need to create some additional calculated fields
Sales – 2024
IF YEAR([Order Date]) = 2024 THEN [Sales] END
Sales 2023
IF YEAR([Order Date]) = 2023 THEN [Sales] END
Profit – 2024
IF YEAR([Order Date]) = 2024 THEN [Profit] END
Profit 2023
IF YEAR([Order Date]) = 2023 THEN [Profit] END
PR – 2024
SUM([Profit – 2024])/SUM([Sales – 2024])
format to % with 0dp
PR – 2023
SUM([Profit – 2023])/SUM([Sales – 2023])
format to % with 0dp
On a new sheet, add Sub-Category to Rows and PR – 2024 to Columns. Sort descending. Change the mark type to circle.
Drag PR – 2023 onto the PR -2024 axis and release the mouse when the 2 green column symbol appears.
This will automatically update the viz to include Measure Names and Measure Values. Adjust the Colour legend so PR 2024 is black and PR 2023 is grey, and PR 2024 is listed first (so black circles are on top when the marks overlap)
Add Sales 2024 to Columns. Change the mark type of the Sales 2024 marks card to bar. Remove Measure Names from the Colour shelf of the Sales 2024 marks card. Set the colour to white and add a black border.
Add Sales – 2023 to the Detail shelf of the Sales 2024 marks card. Right click on the Sales 2024 axis and Add Reference Line per cell referencing the Sales 2023 value as below.
Update the title of the Sales – 2024 axis to Sales [Bars◻] and set the axis to be fixed from 0 to automatic. Update the title of the other axis (Value) to Profit Ratio [Circles ●].
On the All marks card add PR 2023, PR 2024, Sales – 2023 and Sales -2024 to the Tooltip shelf and update to suit.
Make the chart Dual Axis but once again DO NOT synchronise the axis. As before move the marks (bars) of the Sales axis to the back, and remove the Sub-Category label. Remove all gridlines and zero lines and update the title.
And that’s it. I added both my sheets to dashboards, juts to improve the presentation slightly. My published viz is here.
It was Yusuke’s turn for this week’s #WOW2025 challenge, posing a twist on a the creation of a highlight table.
Whenever I start a challenge, I take note of what’s going on – I interact with it, move my mouse around to see if there’s any clues. The main takeaway from this, is that I’d need a dual axis so I could have multiple marks cards to style differently – one to be coloured based on the Profit and one to be coloured based on whether the cell was selected or not. So we need to build a table using an axis.
Building the basic table
Add Order Date to Columns and then click the pill to expand the date hierarchy so Year and Quarter are displayed. Add Sub-Category to Rows.
Double click into Columns and manually type MIN(1.0) to create an axis. Change the mark type to bar, increase the size to as large as possible, and edit the axis to be fixed from 0 to 1. Add Profit to Colour and Profit to Label. Adjust the colour scheme as required (I used red-white-blue diverging and reduced the opacity to 70%). Adjust the label font to be grey text.
Widen each row; shrink each column and adjust the row and column dividers to be dashed grey lines. Update the font of the label headings. Adjust Tooltip to suit.
Storing the selected cells
To store the cells that have been selected, we’re going to use Sets. To build this set, click on a cell in the table, and then in the toolbar of the tooltip that displays, click the venn diagram symbol to create set
Name the set Cells Highlighted
Highlighting the selected cells
Each cell in the table we have built is a bar of length 1. We want to use a dual axis to create bars of length 1 only in the cells selected. So we need
Bar Length – Highlighted Cells
IF [Cells Highlighted] THEN 1 ELSE 0 END
We also only want the profit value to be displayed for these cells
Profit – Highlighted
IF [Cells Highlighted] THEN [Profit] END
Add Bar Length – Highlighted Cells to Columns to make a second axis, and a second marks card. Remove both existing Profit pills from this card. and instead change the Colour to black at 100% opacity, and add Profit – Highlighted to Label. Change colour of the Label text, and update the axis to be fixed from 0 to 1.
Make the chart dual axis and synchronise the axis.
Hide the axis (uncheck show header), and hide the Order Date label (right click -> hide field labels for columns)
Update the title of the sheet with the instructions and then add the sheet to a dashboard.
Adding the interactivity
On click, we want to add the cell (if unselected) to the set. For this we need a dashboard set action
Add to highlight
On select, target the Cells Highlighted set, adding values to the set on click, and keeping set values when cleared.
We also want to remove selected cells via a menu option, so create another dashboard set action
Remove from highlight
Display on the menu of the tooltip, and target the set Cells Highlighted by removing values from the set when the menu option is clicked, and keep values when selection cleared.
While these give us the functionality we need, it isn’t the best user experience – we have to click multiple times to get the display due to the ‘default’ behaviour of selected marks being automatically highlighted / non selected marks being faded out.
To resolve this, we need to utilise a couple of techniques blogged about here.
For the marks coloured by profit, we want to disable highlighting using a dashboard filter action and the true/false method.
Create calculated fields called
True
TRUE
False
FALSE
and add to the Detail shelf of the MIN(1.0) marks card only. Then on the dashboard add a dashboard filter action
Now if you click on an unhighlighted cell it should go black immediately. However, if you click on one of the black already highlighted cells, the other cells still fade.
Now we can’t apply the same method to that cell, as we then lose the hyperlink appearing on the tooltip on click. This is because the true/false method ultimately results in the cell being immediately deselected once selected, so the ‘click’ action, which results in the menu option showing, is cleared .
Instead we will use the dashboard highlight action technique also described in the blog.
Create a new field
Dummy
‘Dummy’
Add this to the Detail shelf of the All marks card (or add to both the MIN(1.0) and the Bar Length – Highlighted Cells marks cards). Then create a dashboard highlight action that just targets the Dummy field only, which as it exists in all cells, essentially selects them all.
Highlighting the word ‘colour’ in the title
I just did this by floating a blank object over the text which I set the background colour to black, and then set the object to move backwards. This did mean once I published to Tableau Public, I had to edit the viz online to ensure the object lined up to where I wanted.
The focus of this week’s challenge, set by Yoshi, is showing “detail on demand” using filters.
Defining the calculations
We’re only concerned about current year and previous year, so to simplify this, after connecting to the data source, I added a data source filter based on Order Date to restrict the information to years 2023 and 2024 only.
I then created the following fields
Latest Year
{MAX(YEAR([Order Date]))}
Previous Year
[Latest Year]-1
Sales – LY
IF YEAR([Order Date]) = [Latest Year] THEN [Sales] END
Format to $ with 0 dp
Sales – PY
IF YEAR([Order Date]) = [Previous Year] THEN [Sales] END
On a new sheet, add Category to Columns, and Sales – LY and YoY to Text and format/layout the text as required. Adjust the formatting of the column headings too.
Add YoY to Colour too to match the solution (though personally, if I was doing this for a business need, I wouldn’t do this as some of the text becomes quite washed out).
Building the Sub Category Viz
On a a new sheet, add Category and Sales – LY to Columns and Sub-Category to Rows.
However, you can see that as not every Sub-Category exists in every Category, we get ‘blanks’ in the display, so we need use something on rows that exists for every Category. We can do this using the rank of Sales.
Add Sales -LY to Rows and change it to be a discrete (blue) dimension.
Move Sub-Category to Detail and then apply a quick table calculation of rank to the Sales – LY pill on Rows. Adjust the table calculation so it is computing explicitly by Sub-Category only.
Make the rows a little wider, move Sub-Category to Label. Add Sales -LY to label too. Adjust the format/layout of label and align middle right. Add YoY to Colour.
Add Sales – PY to Detail then add a Reference Line to the Sales – LY axis, that refers to Sales – PY per cell.
Add Latest Year to Tooltip and adjust the text to suit. Finally, hide the Sales – LY Rank row header and the Category column header (uncheck show header) and remove the title of the Sales – LY axis. Add a title.
Building the Product Viz
On a new sheet, add Product Name to Rows and Sales – LY to Columns. Sort by Sales – LY descending. Hide the null value indicator.
Add YoY to Colour. Add Sales – LY to Label. Add Sales – PY to Detail and add a Reference Line as described above. Add Sub-Category to Detail and add Latest Year to Tooltip and adjust. Remove the title of the Sales – LY axis, and hide the Product Name column header title (right click and hide field labels for rows). Add a title.
Filtering the display
The requirement is to be able to ‘filter’ the display based on the Category selected, and in the event not all categories are selected, the Product bar chart should show. Due to this second requirement, just using a standard ‘quick filter’ is a bit tricky – we need a way to understand what has been selected in the filter.
We can use a Set for this. Create a set of categories – right click Category > create > set
Category Set
Select all values
Add this to the Filter shelf of the KPI sheet, and then apply this same filter to the bar chart sheets too (right click the pill on the Filter shelf > apply to worksheets > selected worksheets > select the relevant sheets).
If you select show set, this then displays the input control to select which options are in or out of the set.
To manage the visibility of the Product bar chart, we need to know how many Categories there are in total, and how many Categories are selected. So create
Count Categories
{FIXED: COUNTD([Category])}
And
Count Set Members
{ COUNTD(IIF([Category Set], [Category],NULL))}
Both of these are LODs as we need to reference them in another boolean calculation that will be used to drive dynamic zone visibility.
Not all Categories Selected
{(SUM([Count Categories]) <> SUM([Count Set Members]))}
Building the dashboard
Create a dashboard. The three sheets should be arranged within layout containers. I used a horizontal container, where the left hand column contained a vertical container which in turn contained the KPI sheet on the top and the Sub Category bar chart underneath. The right hand column of the horizontal container contained the Product bar chart.
To control the visibility of the Product bar chart, select the object, then from the Layout tab on the left hand side, select the control visibility using value checkbox, and choose the Not All Categories Selected field.
The bar will initially disappear, but if you then deselect a value in the Category ‘filter’ control, it will reappear.
Added extra
We can also set the Product bar chart to filter if a Sub-Category bar is clicked on. Create a dashboard filter action
Filter Products
On select of the Sub-Category bar chart sheet, target the Product Bar Chart sheet passing all values. Show all values when the selection is cleared.
This week, Kyle set the challenge to test our use of functionality released in 2024.3 – spatial parameters. I hadn’t used these before, so did have a watch of the video link posted in the challenge, which was really useful.
Accessing the data
Kyle provided two links to web pages for the data. You need to download the zipped Shapefile from each
Once downloaded, extract each zip file. There will be a SHP (shape) file in each set of unpackaged files. In Tableau desktop, you will need to create 2 connections to a spatial file, one to each shp file
Build the State Map
From the School_District_Characteristics data source, double click on Geometry to automatically create a map.
Add Lea Name to the Detail shelf and add Statename to the Filter shelf, and select Washington.
From the Map menu, select Background Maps > Dark to set the background.
Name the sheet State or similar.
Building the District Map
On a new sheet, double click on Geometry from the School_Neighbourhood_Poverty_Estimates data source to automatically create another map.
Add Name to Detail.
On the Map menu, select Background Layers, and then set the Style to dark and tick the Street,Highways etc option.
Name the sheet District or similar.
Create the spatial parameter and filter
Create a new parameter to store the geometric data
pLea
a spatial parameter, defaulted to Empty
In the School_Neighbourhood_Poverty_Estimates data source, create a calculated filed
Filter
INTERSECTS([pLea],[Geometry])
this will return true if the geometric data that represents the state(s) selected from the state map overlap with the geometric data that represents the schools.
Adding the interactivity
Create a dashboard, placing the State and the District sheets side by side in a horizontal layout container.
Create a dashboard parameter action
Set Lea
On select of the State sheet, set the pLea parameter passing in the value from the Geometry field. Set the value to Empty when the selection is cleared.
Click on the State map (which will populate the pLea parameter). Then navigate back to the District sheet and add the Filter calculation to the Filter shelf and select True, which will result in the District map being filtered
Return to the dashboard and click on the State map to see the District map change.
When no states are selected though, the District map is empty, which is the desired behaviour, but we want to fill the display with the State map (ie hide the space where the District map is).
To do this, create a calculated field (in either of the data sources)
State Selected
NOT ISNULL([pLea])
This will return True if there is something in the pLea parameter.
Back on the dashboard, select the District object on the dashboard, then from the Layout tab, check the Control visibility using value checkbox, and select the State Selected field
Remove the title from the District sheet on the dashboard, and now when all states are unselected the State sheet fills the view.
Finalise the dashboard by adding the title and any other information required. Set the background of the dashboard to dark grey and show the State parameter.
This week’s challenge by Lorna was to deliver some functionality without using LODs or table calculations. She hinted that parameters and parameter actions would be your friends.
Setting up the parameters
Create a parameter to capture the month selected
pMonth
date parameter defaulted to 01 April 2023
and then create one that will store the value associated to the month selected
pMonthSales
float parameter defaulted to 0
Building the Viz
On a new sheet add Order Date as a continuous (green) pill at the month-year level to Columns. Add Sales to Rows.
Create a new calculated field
Difference from Selected Sales
SUM([Sales]) – [pMonthSales]
and add this to Rows.
Change the mark type of this second marks card to bar and add Difference From Selected Sales to the Colour shelf. Adjust the colour to a diverging scale and centre at 0
Set the Size of the bars to be Manual rather than Fixed and adjust the slider to suit.
Add a reference line to the Order Date axis, that references the pMonth parameter.
Adjust the Tooltips, remove gridlines and add a title.
Adding the interactivity
Add the sheet to a dashboard. Add a dashboard parameter action
Set Date
On select of the viz, update the pMonth parameter with the value from the Month([Order Date]) field.
Add another dashboard action
Set Value
On select of the Viz, update the pMonthSales parameter with the value from the SUM(Sales) field that is aggregated at the SUM level.
Now if you click on a point on the line chart, the bottom bars should alter, but they’ll all appear ‘faded’ initially.
To resolve this, create a new calculated field
HL
“HL”
and add this to the Detail shelf of the All marks card. Then create a dashboard highlight action
Highlight
On select of the viz, highlight the HL field only
When you now click, the bottom marks aren’t faded as they essentially are all ‘highlighted’ too.
I set this week’s challenge and I tried to deliver something to hopefully suit everyone wherever they are on their Tableau journey. The primary focus for this challenge is on table calculations, but there’s a few other features/functionality included too.
I love football – all the family are involved in it in some way, so thought I’d see what I could get from a different data set this week. The data set contains the results of matches played in the English Premier League for the last five seasons – from the 2020-21 season to the current season 2024-25. Matches up to the end of 2024 only are included.
As I wrote the challenge in such a way that should allow it to be ‘built upon’ from the beginner challenge, I’m going to author this blog in the same way. I’ll describe how to build the beginner challenge first, then will adapt /add on to that solution. Obviously, as in most cases, this is just how I built my solution – there may well be other ways to achieve the same result.
Beginner Challenge
Modelling the data
The data set provided displays 1 row per match with the teams being displayed in the Home and the Away columns respectively, and the FTR (full time result) column indicating if the Home team won (H), the Away team won (A) or if the match was a draw (D).
The first thing we need to do is pivot this data so we have 2 rows per match, with a column displaying the Team and another indicating if the team is the Home or Away team. To do this, in the data source window, select the Home and the Away columns (Ctrl-Click to multi-select – they’ll be highlighted in blue), then from the context menu on either column, select the Pivot option.
This will duplicate the rows, and generate two new fields called Pivot Field Names and Pivot Field Values. Rename the fields by double-clicking into the field heading
Pivot Field Names to Homeor Away
Pivot Field Values to Team
Creating the points calculation
We need to determine how many points each team gained out of the match which is based on whether they won (3pts), lost (0pts) or drew (1pt). Create a calculated field
Pts
IF [Home or Away] = ‘Home’ AND [FTR] = ‘H’ THEN 3 ELSEIF [Home or Away] = ‘Away’ AND [FTR] = ‘A’ THEN 3 ELSEIF [FTR] = ‘D’ THEN 1 ELSE 0 END
Creating the cumulative points line chart
We want to create a line chart that displays the cumulative number of points each team has gained by each week in each season.
Start by adding Wk to Columns and Pts to Rows as these are the core 2 fields we want to plot. But we need to split this by Team and by season, so add Team and Season End Year to the Detail shelf.
This gives us all the data points we need, but at the moment, it’s currently just showing how many points each team gained per week in each season.
To get the cumulative value, we add a running total quick table calculation to the Pts field.
which gives us the display we need
While we can leave the SUM(Pts) field as is in the Rows, I tend to like to ‘bake’ this field into the data set, so I have a dedicated field representing the running total. I can create this field in 2 ways
Create a calculated field called Cumulative Points Per Season which contains the text RUNNING_SUM(SUM([Pts])). Add this field to Rows instead of just Pts.
Hold down Ctrl and then click on the SUM([Pts]) pill in Rows and drag into the left hand data pane and then release the mouse. This will automatically create a new field which you can rename to Cumulative Points Per Season. The field will already contain the text from the calculation used within the quick table calculation, and the field on Rows will automatically be updated.
Filtering the data
Add Team to the Filter shelf and when prompted just select a single entry eg Arsenal.
Show the Filter control. From the context menu, change the control to be a single value (list) display, and then select customise and uncheck the show all value option, so All can’t be selected.
Format the line
To make the line stepped, click the path button and select the step option
To identify the latest season without hardcoding, we can create
Is Latest Season
[Season End Year] = {MAX([Season End Year])}
The {MAX([Season End Year])} is a FIXED LOD (level of detail) calculation, and is a shortened notation for {FIXED: MAX([Season End Year])} which basically returns the latest value of Season End Year across every row in the data set. This calculation returns true if the Season End Year value of the row matches the overall value.
Add this field to the Colour shelf and adjust colours to suit. Also add the same field to the Size shelf
The line for the latest season is being displayed ‘behind’ the other seasons. To fix this, drag the True value in either the colour or size legend to be listed before the False value. Then edit the sizes from the context menu of the size legend, and check the reversed checkbox to make True the thicker line.
If the thick line seems too thick, adjust the mark size range to get it as you’d prefer
Label the line
Add Season End Year to the Label shelf. Align middle right. Allow labels to overlap marks. And match mark colour.
Create the Tooltip
We need an additional calculated field for this, as we want to display the season in the tooltip in the format 2024-2025 and not just the ending year of the season.
Season Start Year
[Season End Year]-1
Add this field to the Tooltip shelf, and then edit the Tooltip to build up the text in the required format. Use the Insert button on the tooltip to add referenced fields.
Final Formatting
To tidy up the display we want to
Change the axis titles
Right click on each axis and Edit Axis then adjust the title (or remove altogether if you don’t want a title to display)
Remove gridlines and axis ruler
Right click anywhere on the chart canvas, and select Format. In the left hand pane, select the format lines option and set grid Lines to None and axis rulers to None.
Set all text to dark purple
Select the Format menu option at the top of the screen and select Workbook. The under the All fonts section, change the colour to that required
Update the title to reference the selected team
double click in to the title of the sheet and amend accordingly, using the Insert option to add relevant fields.
Intermediate Challenge
For this part of the challenge we’re looking at using a dual axis to display another set of marks – these ones are circular and only up to 1 mark per season should display. As this now takes a bit more thought, and to help verify the calculations required, I’m going to build up the calculations I need in a tabular form first.
Defining the additional mark to plot
On a new sheet add Team, Season End Year and Wk to Rows. Set the latter 2 fields to be discrete (blue) pills. Add Cumulative Points Per Season to Text. Add Team to Filter and select Arsenal.
We need to identify the date of the last match played by each team, so we can use an LOD for this
Latest Date Per Team
{FIXED [Team] : MAX([Date])}
Add this to Rows as a discrete (blue pill) exact date. For Arsenal, the last match was on 27 Dec 2024, whereas for Chelsea it’s 22 Dec 2024.
With this, we can work out what the latest points are for each team in the current season.
Latest Points Per Team
WINDOW_MAX(IF MIN([Date]) = MIN([Latest Date Per Team]) THEN [Cumulative Points Per Season] END)
Breaking this down : the inner part of the statement says “if the date associated to the row matches the latest date, then return the points associated with that row”. Only 1 row in the table of data has a value at this point, all the rest of the rows are ‘nothing’. The WINDOW_MAX statement, then essentially ‘floods’ that value across every row in the data, because the ‘value’ returned by the inner statement is the maximum value (it’s higher than nothing). Add this field into the table.
We’re trying to identify the week in each season where the points are at least the same as the latest points. We’re going to capture the previous week’s points against every row.
Previous Points
LOOKUP([Cumulative Points Per Season],-1)
This is a table calculation that returns the value of the Cumulative Points Per Season from the previous row (-1). If we wanted the next row, the function parameter would be 1. 0 identifies the ‘current row’.
Add this to the table.
We can see the behaviour – The Previous Points associated to 2025 week 18 for Arsenal is 33, which is the value associated to Cumulative Points Per Season for week 17. But we can also see that week 38 from season 2024 is being reported as the previous points for week 1 of season 2025, which is wrong – we don’t want a previous value for this row.
To resolve, edit the table calculation of the Previous Points field and adjust so the calculation for Previous Points is just computing by the Wk field only.
With this we can identify the week in each season we want to ‘match’ against. In the case of the latest season, we just want the last row of data, but for previous seasons, we want to identify the first row where the number of points was at least the same as the latest points; the row where the points in the row are the same or greater than the latest points, and the points in the previous row are less.
Matching Week
//for latest season, just label latest record, otherwise find the week where the team had scored at least the same number of points as the current season so far IF MIN([Is Latest Season]) AND LAST()=0 THEN TRUE ELSE IF NOT(MIN([Is Latest Season])) AND [Cumulative Points Per Season]>= [Latest Points Per Team] AND [Previous Points] < [Latest Points Per Team] THEN TRUE ELSE FALSE END END
Add this to Rows and check the data. In the example below, in Season 2020-21, in week 26, Arsenal had 37 points. The previous week they had 34 points. Arsenal’s latest points are 36, so since 37 >=36 and 34 < 36, then week 26 is the matching week.
Looking at season 2023-24, in both week 15 and 16, Arsenal had 36 points. But only week 15 is highlighted as the match, as in week 14, Arsenal had 33 points so 36 >=36 and 33 <36, but for week 16, as the previous week was also 36, the 2nd half of the logic isn’t true : 36 is not less than 36.
So now we’ve identified the row in each season we want to display on the viz, we need to get the relevant points isolated in their own field too.
Matching Week Points
IF [Matching Week] THEN [Cumulative Points Per Season] END
Add this to the table
We now have data in a field we can plot.
Visualise the additional mark
If you want to retain your ‘Beginner’ solution, then the first step is to duplicate the Beginner worksheet, other wise, just build on what you have.
Add Matching Week Points to Rows to create an additional axis. By default only 1 mark may have displayed. Adjust the table calculation setting of the field, so the Latest Points Per Team calculation is computing by all fields except the Team field.
Change the mark type of the Matching Week Points marks card to Circle and remove Season End Year from the Label shelf (simply drag the pill against the T symbol off off the marks card)
Size the circles
We want the circles to be bigger, but if we adjust the Size, the lines change too, as the Size is being set based on the Is Latest Year pill on both marks cards. To resolve this, create a duplicate instance of Is Latest Year (right click pill and duplicate). This will automatically create Is Latest Season (copy) in the dimensions pane. Drag this onto the size shelf of the Matching Week Points marks card instead to make the sizing independent (you will probably find you need to readjust the table calculation for the Matching Week Points pill to include Is Latest Season (copy)). Then adjust the sizes as required.
Label the circles
Add Latest Points Per Team to the Label shelf of the Matching Week Points marks card. Adjust the table calculation setting, so the Latest Points Per Team calc is computing just by Wk only, so only the latest value displays.
Then format the Label so the text is aligned middle centre, is white & bold and slightly larger font.
Adjust the Tooltip text on the Matching Week Points mark, so it reads the same as on the line mark. You will need to reference the Matching Week Points value instead.
Then make the chart dual axis and synchronise the axis. Remove the Measure Names field that has automatically been added to the All marks card
Remove Label for Latest Season
We don’t want the season label to display for the current year, so create a new field
Label:Season
IF NOT([Is Latest Season]) THEN [Season End Year] END
Replace the Season End Year pill on the Label shelf of the Cumulative Points Per Season marks card, with this one instead.
Final Formatting
To tidy up
Remove right hand axis
right click on axis and uncheck show header
Remove 165 nulls indicator
right click on indicator and hide indicator
Remove row & column dividers
Right click on the canvas and Format. Select Format Borders and set Row and Column Divider values to None
Advanced Challenge
For the final part of the challenge we want to add some additional text to the tooltip and adjust the filter control. As before either duplicate the Intermediate challenge sheet or just build on.
We’ll start with the tooltip text.
Expand the Tooltip Text
For this, we’ll go back to the data table sheet we were working with to validate the calculations required.
We want to calculate the difference in the number of weeks between the latest week of the current season, and the week number of the matching record from previous seasons. So first, we want to identify the latest week of the current season, and ‘flood’ that over every row.
Latest Week Number Per Team
WINDOW_MAX(MAX(IF ([Date]) = ([Latest Date Per Team]) THEN [Wk] END))
This is the same logic we used above when getting the Latest Points Per Team, although as this time the Wk field isn’t already an aggregated field like Cumulative Points Per Season is, we have to wrap the conditional statement with an aggregation (eg MAX) before applying the WINDOW_MAX.
Add this to the table.
And then we need, the week number in each season where the match was found, but this needs to be spread across every row associated to that season.
Matching Week No Per Team and Season
WINDOW_MAX(IF [Matching Week] THEN MIN([Wk]) END)
Add to the table, but adjust the table calculation so the field is computing by all fields except the Season End Year.
Now we have these 2 fields, we can compute the difference
Week Difference
[Latest Week Number Per Team] – [Matching Week No Per Team and Season]
Add to the table. With this we can then define what this means for the text in the tooltip
Less/More
IF [Week Difference]<0 THEN ‘more’ ELSEIF [Week Difference]>0 then ‘less’ ELSE ” END
and also build the tooltip text completely
Tooltip- other seasons
IF NOT(MIN([Is Latest Season])) THEN IF [Week Difference] = 0 THEN ‘It took the same amount of weeks to accrue at least the same number of points as the current season’ ELSE ‘It took ‘ + STR(ABS([Week Difference])) + ‘ weeks ‘ + [Less/More] + ‘ to accrue at least the same number of points as the current season’ END END
Add this to the Tooltip shelf of the Matching Week Points marks card. Adjust the Tooltip to reference the field.
Adjust the Tooltip – other seasons table calculation, so the Latest Points Per Team nested calculation is computing for all fields except Team (this will make the circles reappear)
and also adjust the Latest Week Number Per Team nested calculation to compute by all fields except Team. This should make the text in the tooltip appear.
Filtering by teams in the current season only
We need to get a list of the teams in the current season only, which we can define by
Filter Team
IF {FIXED [Team]: MAX([Season End Year])} = {MAX([Season End Year])} THEN [Team] END
Breaking this down: {FIXED [Team]: MAX([Season End Year])} returns the maximum season for each team, which is compared against the maximum season in the data set. So if there is a match, then name of the team is returned. Listing this out in a table we get Null for all the teams whose latest season didn’t match the overall maximum
We can then build a set off of this field. Right click on the Filter Team field Create > Set and click the Null field and select Exclude
Then add this to the Filter shelf, and the list of teams displayed in the Team filter display will be reduced.
And with that the challenge is completed. My published viz is here.
This week’s challenge was set by Sean and was a recreation of a PowerBI WOW challenge set a few weeks ago. The data set just contains 2 pieces of information – a datetime field and a glucose measurement.
Creating the calculations
On a new sheet, add Device Timestamp to Rows as a discrete (blue) pill at the hour level.
Then create the following fields, which are aggregations of the glucose levels at each hourly time period.
Minimum
MIN([Glucose mg/dL])
5th Percentile
PERCENTILE([Glucose mg/dL],0.05)
25th Percentile
PERCENTILE([Glucose mg/dL],0.25)
Median
PERCENTILE([Glucose mg/dL],0.5)
75th percentile
PERCENTILE([Glucose mg/dL],0.75)
95th percentile
PERCENTILE([Glucose mg/dL],0.95)
Maximum
MAX([Glucose mg/dL])
Add all these fields to the table and then format the Hour field to use the 12hr format by default
Building the Viz
On a new sheet, add Device Timestamp as a continuous (green) pill at the hour level to Columns. Then add Median to Rows. Set the mark type to line and set the path to be a dashed line and colour black. Format the Device Timestamp pill so the default format on the pane is 12hr format (axis should remain as numeric).
Add Minimum to Rows to create a second marks card. Change the mark type to Area and set the colour to be 100% opacity.
Then drag 5th Percentile to the Minimum axis and drop when the ‘2 green column’ symbol appears.
This will automatically add Measure Values to Rows instead and Measure Names to Colour. Add 25th Percentile, 75th Percentile, 95th Percentile and Maximum to the axis too. Reorder the fields in the Measure Values section so the fields are listed in the appropriate order from Min to Max. Adjust the Colours accordingly. Unstack the marks (Analysis -> Stack Marks -> Off).
Add Measure Names to Label and align middle right. Allow labels to overlap marks.
Add Measure Names to the Label on the Median marks card. Also align middle right and format to be underlined.
Make the chart dual axis, synchronise the axis, and then right click on the right hand axis and move marks to back.
Hide the right hand axis (uncheck show header). Edit the Device Timestamp axis to fix from 0 to 26, set the axis to display a value every 3 hours and remove the title.
Add constant Reference Lines to the left hand axis for the values 80 and 180
Edit the left hand axis title. Remove row and column dividers. Add axis rulers. Add a title.
Add to a dashboard and then publish. My published version is here.
Note – the vertical grid lines that appear on the Tableau Public version are a nuance of Tableau Public. I originally recreated on Desktop thinking it was a requirement of the challenge, by adding multiple constant reference lines for each hour. But when I checked Sean’s solution after publishing, I found he didn’t have any and the intention was not to have any.
Donna + DataViz 