After connecting to the data, add Team to Rows and Timestamp as a continuous (green) pill at the Day level to Columns. Create a new field
Call Count
COUNT([synthetic_call_center_data.csv])
and then add this to Rows.
Add Timestamp to Filter, and select relative date. Set the options to Last 3 weeks, and then additionally check the anchor relative to check box and enter 23 Dec 2024. This is because the data set only goes up to the end of December 2024. Not setting this field will apply the date filter based on ‘today’ so it’s unlikely anything will appear.
(Note – you may also need to update the date properties of the data set to ensure a week starts on a Sunday to get matching numbers: right click the data source and select the date properties option).
To add a marker to the last point, create a field
Call Count – Most Recent
IF LAST()=0 THEN [Call Count] END
Add this to Rows and adjust the table calc setting so it is computing specifically by Day of Timestamp only. By default it was doing this via the Table (across) option, but I tend to always prefer to always explicitly fix what the calculation is computing over, as it won’t then matter where I then move that field too if I choose to change the layout of the viz.
Set the mark type on the Call Count marks card to line, and then adjust the colour to grey and reduce the size. Set the mark type of the Call Count – Most Recent marks card to circle, set the colour to blue and increase the size. Hide the null indicator (right click > hide).
Set the chart to dual axis, synchronise the axis and then remove the Measure Names field from the All marks card.
remove both the axis titles (right click axis > edit axis), hide the right hand axis (right click, untick show header), and format to remove the column divider from the header section only.
Now we’ve got the core display, we need to create the following fields
No. of Calls
WINDOW_SUM([Call Count])
Highest Call Vol
WINDOW_MAX([Call Count])
Lowest Call Vol
WINDOW_MIN([Call Count])
Avg Call Vol
WINDOW_AVG([Call Count])
format this to a number with 0 dp
Calls this period
WINDOW_MAX([Call Count – Most Recent])
the window_max is required here, as the data set we’re displaying at the day level, has 2 values – the latest value and null. We only want to return 1 value, which is the maximum of these.
Previous Period
WINDOW_MAX(IF LAST()=1 THEN [Call Count] END)
LAST()=1 returns the value of the next to last record, and the window_max is again applied, as the nested IF clause will return null for all others records.
Period Var
[Calls this period] – [Previous Period]
Add each of these fields, one by one, to Rows following the steps below
Add to rows (it will automatically display as a green continuous pill).
change to discrete (right click on the pill and select discrete – the pill will turn blue and move to before the green pills)
Explicitly set the table calc to be computing by Timestamp (as above)
Once, you should have something that looks like this
but I noticed, that the display in the solution is sorted based on the total number of calls and not by Team, so add a Sort to the Team pill to sort by Call Count descending
Update the Tooltip if you wish, and then add the viz to a dashboard, floating the Timestamp filter.
Erica collaborated with Giulio D’Errico for this week’s #WOW2025 challenge, which contained lots of features, though the main challenge was to display filter on Region, which along with the Region name also displayed a KPI indicator that could change based on selection from other parameters.
Defining the parameters
We need 3 parameters for this challenge
pMeasure
strig parameter that lists the two options Profit and Sales; defaulted to Profit.
pProfitThreshold
integer parameter that lists the specified values, defaulted to 2,000
pSalesThreshold
integer parameter that lists the specified values, defaulted to 30,000
Building the core scatter plot
Add Sales to Columns and Profit to Rows and Sub-Category to Detail. Show the 3 parameters.
When pMeasure = Profit, we need to display horizontal reference lines against the Profit axis, and when Sales is selected we need to display vertical reference lines against the Sales axis. We need the following fields to return the user defined thresholds:
Ref – Profit Threshold
IF [pMeasure] = ‘Profit’ THEN [pProfitThreshold] END
Ref – Sales Threshold
IF [pMeasure] = ‘Sales’ THEN [pSalesThreshold] END
We also need to define the average per measure for each region:
IF [pMeasure] = ‘Profit’ THEN [Profit Avg Per Region] END
Ref – Sales Avg
IF [pMeasure] = ‘Sales’ THEN [Sales Avg Per Region] END
Add the 4 Ref – XXX fields to the Detail shelf. Then add 2 reference lines to the Sales axis; one referencing Ref – Sales Avg (coloured as a purple dashed line at 100% opacity) and one referencing Ref – Sales Threshold (coloured as a black dashed line at 50% opacity). Display a custom label and then format the label to be aligned vertically and coloured based on the relevant line, and with a 0% shading. Set the pMeasure to Sales to make these display.
Then repeat, adding 2 reference lines to the Profit axis instead. Change pMeasure to Profit for these to appear.
Change the mark type to square. Each mark needs to be coloured whether it is above or below the average for the measure selected.
Colour
IF [pMeasure] = ‘Profit’ THEN IIF(SUM([Profit]) >= SUM([Profit Avg Per Region]),1,0) ELSE IIF(SUM([Sales]) >= SUM([Sales Avg Per Region]),1,0) END
Set this to be discrete and then add to the Colour shelf and adjust accordingly.
Creating the colour-coded filter
The viz needs to be filtered by Region, but the filter displayed needs to show the region name along with an indicator based on whether the average for the region is above the threshold or not. This took a bit of an effort, but I finally managed it.
We need a field to capture the ‘KPI indicator’ based on which measure was selected. It also needs to be computed per region. We need a FIXED LoD for this, and I made use of coloured unicode characters from this site.: https://unicode-explorer.com/ (large yellow circle and large green circle which you can just ‘copy and paste’ into the calculation)
Region Indicator
{FIXED [Region]: (
IF [pMeasure] = ‘Profit’ THEN IIF(SUM([Profit Avg Per Region])>=[pProfitThreshold],’🟢’,’🟡’) ELSE IIF(SUM([Sales Avg Per Region])>=[pSalesThreshold],’🟢’,’🟡’) END )}
I then created
Filter – Region
[Region Indicator] + ‘ ‘ + [Region]
Add this to the Filter shelf, select all values and show the filter. If you change the pProfitMeasure to Profit and pProfitThreshold to 6,000, you’ll see the KPI indicators change.
Format the Tooltip
The tooltip requires several calculated fields to be created. Certain fields only need a value based on the pMeasure and others have conditional formatting (different colours) applied. For the tooltip, I created all these fields:
Add all these fields to the Tooltip. Update the Tooltip to reference the fields and the pMeasure parameter, colouring the text as required. Some of the fields will only display based on the filters selected, so they get places side by side with no spacing.
Finalise the viz by updating the title to reference the pMeasure and the Label – Region field. Remove all gridlines, axis rulers, zero lines etc. Colour the background of the sheet to pale blue.
Building the Overall Indicator
For the bonus challenge, we need to display an indicator that is green if all the regions are green and yellow if at least 1 region is yellow.
On a new sheet, add Region and Region Indicator to Rows and show the 3 parameters.
We’re going to create a field that will return 1 if the Region Indicator is yellow and 0 otherwise.
Region Indicator – Is Below
IIF( [Region Indicator] = ‘🟡’,1,0)
Set to be discrete and add to Rows. Change the pProfitThreshold to 6000 to see the flag change.
Create
Overall Region Indicator
IIF(WINDOW_MAX(MAX([Region Indicator – Is Below]))=1,’🟡’, ‘🟢’)
This says, if the maximum value of the rows ‘in the window’ is 1, then display a yellow indicator, else green. Add to Rows, and adjust the pProfitThreshold to see the behaviour.
Create a field
Filter – Index = 1
INDEX() = 1
Add to Filter shelf and set to True
Move Region to Detail. Remove Region Indicator and Region Indicator – Is Below. Adjust the table calculation setting of Overall Region Indicator to compute using Region only, and do the same for the tableau calculation on Filter – Index = 1 (re-edit the filter after changing, so only True is selected).
Note – originally I used a transparent shape mark type and displayed the indicator as a label, but after publishing to Tableau Public, the indicator became distorted, so I adjusted how this was built.
Set the mark type to circle and add Overall Region Indicator to Colour. Adjust to be green or yellow, depending on the colour of the KPI indicator (you’ll need to change the pProfitThreshold value to ensure you set the colour for both the yellow and green options).
Finally create
Tooltip – Overall Indicator
IIF([Overall Region Indicator]=’🟢’,’All regions meet the ‘ + [pMeasure] + ‘ target’, ‘At least one region does NOT meet the ‘ + [pMeasure] + ‘ target’)
Add this to Tooltip and then set the background colour to pale blue.
Building the dashboard & adding dynamic zone visibility
Using layout containers, add the viz to a dashboard. Use a horizontal layout container to arrange the parameters, the Region filter and the Overall Indicator sheet. I used a floating text object to display the ‘legend key’ again copying come unicode characters from the website referenced earlier.
Then create 2 new boolean calculated fields
Is Profit
[pMeasure] = ‘Profit’
Is Sales
[pMeasure] = ‘Sales’
Select the Sales Threshold parameter object, then update the visibility so it only displays if Is Sales is true (via the Layout > Control visibility using value option)
Repeat the same for the Profit Threshold object, but reference the Is Profit field instead.
Now as you switch the measure between Sales and Profit, the relevant threshold parameter will display. My published viz is here.
Yusuke set this interesting challenge : to combine a ‘bump’/’slope’ chart visualising the change in rank whilst also visually displaying the Sales value for the relevant Sub-Category in the ranked position.
Defining the calculations
This challenge will involve table calculations, so I’m going to start by building out the various calculations that will be required and displaying in a tabular view.
Add Category to Filter and select Office Supplies. Then add Sub-Category and Order Date at the Year level as a discrete (blue) pill to Rows. Add Sales to Text.
Create a new field
Sales Rank
RANK(SUM([Sales]))
And add to the table, and verify the table calculation is set to compute by Sub-Category only.
We will need to ‘colour’ the viz based on the rank compared to the previous year. For this create
Is Min Year
{MIN(YEAR([Order Date]))} = YEAR([Order Date])
which will return true for the first year in the data (in this instance 2022) and then create
Colour
IF [Sales Rank] = LOOKUP([Sales Rank],-1) OR ATTR([Is Min Year]) THEN ‘Same as last year’ ELSE ‘Different from last year’ END
If the rank is the same as the previous one, or it’s the first year, then treat as the same, otherwise treat as different.
Add the Colour field to the table, and this time make sure the table calculation for Colour is computing by Year of Order Date only (while the nested calc for Sales Rank should still be computed by Sub-Category only)
The labels on the viz only want to show in certain scenarios – if it’s the first record (ie for 2022) or there has been a change in rank. We need
Label : Rank & Sub Cat
IF ATTR([Is Min Year]) OR [Colour] = ‘Different from last year’ THEN STR([Sales Rank]) + ‘ | ‘ + MIN([Sub-Category]) END
and
Label : Sales
IF ATTR([Is Min Year]) OR [Colour] = ‘Different from last year’ THEN SUM([Sales]) END
format this to $ with 0dp
Add these to the sheet, and double check the nested table calculations on each pill are computing as required (Sales Rank by Sub-Category only, Colour by Year Order Date only)
Now we have all this, we can start building
Creating the Viz
On a new sheet, add Category to Filter and select Office Supplies. The add Order Date to Columns, but set to be continuous (green) pill at the Year level. Add Sub-Category to Detail and add Sales Rank to Rows as a discrete (blue) pill. Verify the table calculation setting against the Sales Rank pill is by Sub-Category only.
Change the mark type to line and then add Order Date to Path. By default it should be at the Year level as a discrete pill.
This is the ‘bump’ chart.
Now add another instance of Order Date to Columns as a continuous pill at the Year level to essentially duplicate the display. On the 2nd marks card, change the mark type to Gantt
This gives us the ‘starting point’ for each ‘bar’. But we need to determine the size for each bar. First we’re going to ‘normalise’ the sales values for all the sales being displayed so we get a value between 0 and 1, where 0 is the smallest sale, and 1 is the largest.
To see what this is doing, format the field to 2dp, then add the field to the tabular view, and ensure the table calculation is computing by both Sub-Category and Year Order Date.
But the ‘axis’ we want to plot the bar length against is in years, so we need to adjust this size to be a proportion of a year (ie 365 days)
Gantt Size
//proportion of a year [Normalised Sales] * 365
Add this to the Size shelf on the 2nd marks card on the viz. Adjust the table calc setting so it is computing by all the fields listed.
We now have the core concept so now we can start finalising the display.
Make the chart dual axis and synchronise the axis.
Set the view to fit height.
On the 1st marks card (that represents the line)
change the line style to dotted (via the Path shelf)
reduce the Size to suit
change the colour to pale grey
Add Label : Sales and Label : Rank & Sub Cat to the Label shelf.
Adjust the table calc settings of each so the nested table calcs in each have Sales Ranks by Sub Category only and Colour by both the Year Order Date fields only.
Adjust the layout of the text as required
Align the font to be top right
Change the font style (bold & black)
Ensure the Label is set to ‘allow labels to overlap marks’
Remove the Tooltip
On the 2nd marks card, the gantt bar
Add Colour to the Colour shelf and adjust the colours accordingly.
Verify the table calc settings are as expected
I chose to reduce the opacity slightly, so I could see the dotted line underneath (set to 70%)
Add Sales to Tooltip (format to $ with 0 dp) and the adjust Tooltip as required
Then we just ned to finalise the formatting/display
Set the font of the years and rank numbers to black & bold.
hide the Sales Rank label heading (right click > hide field labels for rows)
remove row & column dividers
Add black column gridlines (I set to the 2nd thickness level), and remove any row gridlines
Edit the top axis to have a fixed start (use default option) and end at 31/12/2025 so the 2026 label and line disappears.
Remove the title from the top axis.
Edit the bottom axis – remove the tile, and then set the tick marks to None, so the bottom axis now looks empty.
And that should be it. Now add the sheet to a dashboard and display the category filter as a single select, customising the remove the ‘all’ option.
It was Yoshi’s turn to set the challenge this week. The requirement was to build a waterfall chart, and I have to confess I did end up having to have a sneak peak at Yoshi’s solution to point me in the right direction.
I tend to always be looking for generic solutions, and in this case trying to make use of Measure Names / Measure Values, but struggled to do this. When I peaked at the solution, I found there was an element of ‘hardcoding’ being applied for the specific layout. Armed with that knowledge, I was then able to build a solution which ended up differing from Yoshi’s, but (looks to) produces the same outcome.
Defining the reporting period
The viz is driven by a Base Date input control that allows the user to select a date. Based on the date selected, the viz then displays information for the whole of the previous month, and compares that to the same month in the previous year. This means if the user selects any date from 01 Aug 2025 to 31 Aug 2025, the viz shows the information related to the whole of July 2025 and compares it to July 2024.
We will use a parameter to capture the date inputted by the user, but rather than ‘hardcode’ the data to use, I’m going to set it based on a field in the data set that I’ll create
Date Default
IIF(YEAR(TODAY())>2025, #2026-01-01#, TODAY())
The data set we’re using goes up to 31 Dec 2025. To ensure the viz still shows data if it’s accessed in 2026 or beyond, I’m going to set the date to 01 Jan 2026 if we’re looking at the viz in 2026 or later, otherwise I’ll default to whatever ‘today’ may be. This means that from 01 Jan 2026 onwards, the viz by default will always show the data for December 2025 compared to December 2024.
With this field, I can then create the parameter
pDate
Date parameter that references the Date Default field when the workbook is first opened
And with the date captured by the user, we can then determine the date of the previous month we’ll be reporting over
This truncates the pDate value to the 1st day of that month, then subtracts a month to return the 1st day of the previous month. Eg if pDate is 20 Aug 2025, it is first truncated to 1st Aug 2025, then 1 month is subtracted to return 1st July 2025.
We’ll then refer to this field when determining all the measures we need to build.
Calculating the measures
There are multiple measures we need to determine to build this viz – information for the previous month, the previous month last year and then the difference between the two. So what follows is just a list of all these 🙂
Sales – Last Month
IIF(DATETRUNC(‘month’, [Order Date]) = [Date – Last Month], [Sales], NULL)
format this to $ with 0 dp.
Sales – Last Month LY
IIF(DATETRUNC(‘month’, [Order Date]) = DATEADD(‘year’,-1,[Date – Last Month]), [Sales], NULL)
format this to $ with 0 dp.
Sales – Last Month YoY
(SUM([Sales – Last Month]) – SUM([Sales – Last Month LY])) / SUM([Sales – Last Month LY])
custom format this to a % with 0 dp with an explicit + or – prefix : +0.0%;-0.0%;0.0%
Profit – Last Month
IIF(DATETRUNC(‘month’, [Order Date]) = [Date – Last Month], [Profit], NULL)
format this to $ with 0 dp.
Profit – Last Month LY
IIF(DATETRUNC(‘month’, [Order Date]) = DATEADD(‘year’,-1,[Date – Last Month]), [Profit], NULL)
format this to $ with 0 dp.
Profit – Last Month YoY
(SUM([Profit – Last Month]) – SUM([Profit – Last Month LY])) / SUM([Profit – Last Month LY])
custom format this to a % with 0 dp with an explicit + or – prefix : +0.0%;-0.0%;0.0%
Profit Margin – Last Month
SUM([Profit – Last Month])/SUM([Sales – Last Month])
format to % with 1 dp
Profit Margin – Last Month LY
SUM([Profit – Last Month LY])/SUM([Sales – Last Month LY])
format to % with 1 dp
Profit Margin – Last Month YoY
[Profit Margin – Last Month] – [Profit Margin – Last Month LY]
custom format this to a % with 0 dp with an explicit + or – prefix : +0.0%;-0.0%;0.0%
SUM([Discount – Last Month]) – SUM([Discount – Last Month LY])
Cost
[Sales]-[Profit]
Cost – Last Month
IIF(DATETRUNC(‘month’, [Order Date]) = [Date – Last Month], [Cost], NULL)
Cost – Last Month LY
IIF(DATETRUNC(‘month’, [Order Date]) = DATEADD(‘year’,-1,[Date – Last Month]), [Cost], NULL)
Cost – Last Month YoY
SUM([Cost – Last Month]) – SUM([Cost – Last Month LY])
There are additional fields we’ll need, but we’ll define these at the point we need them, as it’ll make more sense.
Building the KPIs
On a new sheet, double click into the Columns shelf and manually type MIN(0) to create a ‘fake axis’. Repeat this 2 more times, so 3 instances of MIN(0) exist and 3 marks cards exist. These are the placeholders for each of the KPIs we need to display.
On the 1st MIN(0) marks card, add Profit-Last Month and Profit – Last Month YoY to Label. Widen the row so you can see the text and change the mark type explicitly to Text. Adjust the label wording, layout and formatting as required, but don’t adjust the font colour. Instead create a new field
Colour – Profit
[Profit – Last Month YoY]>=0
and add this to the Colour shelf and adjust accordingly. Note – you will only ever get a true or false displayed and never both. You will need to adjust the date parameter to find a time period when the value is the opposite in order to set the opposite colour value.
Hide the Tooltip.
Repeat the process, adding Sales – Last Month and Sales – Last Month YoY to the 2nd MIN(0) marks card. Create
Colour – Sales
[Sales – Last Month YoY]>=0
and add to the Colour shelf.
The add Profit Margin – Last Month and Profit Margin – Last Month YoY to the 3rd MIN(0) marks card. Create
Colour – Profit Margin
[Profit Margin – Last Month YoY]>=0
and add to the Colour shelf. Hide column & row dividers, and name the sheet KPIs or similar.
Building the Waterfall
As mentioned above, this Waterfall chart involves a bit more “hardcoding” and the ‘explicit placement’ of the various measures into the viz.
We are displaying 5 different measures, each one in a ‘specific column’. We’re going to make use of the Row ID field to define what measure is displayed where and how it will be formatted.
Add Row ID to the Dimensions pane (drag above the line in the data pane, so it is above Measure Names). On a new sheet, add Row ID to Filter and filter to rows 1-5 only. Then add Row ID to Columns. Create a new field
Header
CASE [Row ID] WHEN 1 THEN ‘Profit (LY)’ WHEN 2 THEN ‘List Price Sales YoY’ WHEN 3 THEN ‘Total Discount’ WHEN 4 THEN ‘Total Cost YoY’ WHEN 5 THEN ‘Profit (TY)’ END
add this to Columns. Set the sheet to Fit Width.
This gives the start of the structure. We now want to display the relevant measure in each column, but we need a single field to do this, and the values of the fields need to be cumulative based on the preceding values.
Display Value
CASE [Row ID] WHEN 1 THEN {FIXED: SUM([Profit – Last Month LY])} WHEN 2 THEN {FIXED:SUM([Profit – Last Month LY])} + {FIXED:([List Price – Last Month YoY])} WHEN 3 THEN {FIXED:SUM([Profit – Last Month LY])} + {FIXED:([List Price – Last Month YoY])} + (-1*{FIXED:[Discount – Last Month YoY]}) WHEN 4 THEN {FIXED:SUM([Profit – Last Month LY])} + {FIXED:([List Price – Last Month YoY])} + (-1*{FIXED:[Discount – Last Month YoY]}) – {FIXED: [Cost – Last Month YoY]} WHEN 5 THEN {FIXED: SUM([Profit – Last Month])} END
Here, we’re using a FIXED LoD calculation to ensure the measures we need are calculating across the whole data set and not segmented by the Row ID which we’re just using as an arbitrary placeholder.
Add this to Rows and change the mark type to gantt bar.
The size of the gantt bar is determined by the specific measures (rather than the cumulative values)
Size
(CASE [Row ID] WHEN 1 THEN {FIXED: SUM([Profit – Last Month LY])} WHEN 2 THEN {FIXED:([List Price – Last Month YoY])} WHEN 3 THEN (-1*{FIXED:[Discount – Last Month YoY]}) WHEN 4 THEN -1*{FIXED: [Cost – Last Month YoY]} WHEN 5 THEN {FIXED: SUM([Profit – Last Month])} END) -1
Add this to Size
To label the bars create
Label
ABS([Size])
format this to $ with 0 dp. Add to Label and align centrally.
For the colouring create
Colour
IF ([Row ID]) = 1 THEN ‘Light’ ELSEIF ([Row ID]) = 5 THEN ‘Dark’ ELSEIF -1 * [Size] > 0 THEN ‘Blue’ ELSE ‘Red’ END
and add to Colour and adjust accordingly. And then create
Label Indicator
IF [Row ID] = 2 AND [Colour]= ‘Blue’ THEN ‘+’ ELSEIF (([Row ID] =3) OR ([Row ID]) = 4) THEN IF [Colour]=’Blue’ THEN ‘-‘ ELSE ‘+’ END END
Add to Label and adjust the font and layout of the label text accordingly.
Tidy up the formatting by
Adjust font style of the Header label.
Hide the Row ID pill (uncheck show header)
Hide the ‘header’ label (right click > hide field labels for columns)
Hide the axis title
Adjust the font style of the axis
Hide all axis lines/zero line, row & column dividers
Adjust the Tooltip
Name the sheet Waterfall or similar
Add the two sheets onto a dashboard and arrange with the parameter as required.
For this week’s challenge, Lorna visualised the output of a crazy golf game she had with the latest Data School cohort, and thought it would be a fun challenge to share.
While the data set is very small, there’s several core calculations required, which we’ll start off creating.
Defining the core calculations
After connecting to the data, I initially dragged the Hole field from Measures to Dimensions (dragged it to be above the line on the data pane).
We need to know the average score per person (across all 11 holes)
Overall Average Score
{FIXED [Person]: AVG([Score])}
format this to 1dp
We also need the average score per hole
Avg Score Per Hole
{FIXED [Hole]: AVG([Score])}
format this to 1 dp
We need each person’s score up to 9 holes
Score up to 9
{FIXED [Person]:SUM( IF [Hole]<=9 THEN [Score] END)}
We need the tie break score for the 10th & 11th holes
Tie Break
{FIXED [Person]:SUM( IF [Hole]>9 THEN [Score] ELSE 0 END)}
We need the average score across 9 holes
Avg Score
[Score up to 9]/9
format to 1 dp
We need to the lowest score per person
Lowest
{FIXED [Person]: MIN([Score])}
With this information, we can then define a field we can use for sorting which is a numeric field that is a combination of multiple fields as stated in the requirements
Sort
(SUM([Score up to 9])*10000) + (SUM([Tie Break]) * 1000) + (SUM([Avg Score]) * 100)+ SUM([Lowest])
Building the Heat Map
On a new sheet add Score up to 9 to Rows and change it to be discrete. Repeat the same for Tie Break, Avg Score and Lowest. Then add Person to Rows and Hole to Columns.
Double click into Columns and manually type MIN(1.0) to create a ‘fake axis’. Change the mark type to bar and increase the Size to the maximum. Edit the axis to be fixed from 0 to 1 and the hide the axis. Manually increase the width of each row and set the chart to fit width.
Add Score to Colour and choose a diverging colour palette and then manually change the start & end colours to match the solution (or define your own colour style). I used #62a14b (green) and #13316d (dark blue). Check the box to use full colour range
Update the Tooltip as required.
We then need to include some indicators on the heat map. To determine the arrow indicator we first need
Previous Score
LOOKUP(SUM([Score]),-1)
and then create
Change Indicator
IF SUM([Score]) > [Previous Score] THEN ‘↗’ ELSEIF SUM([Score]) < [Previous Score] THEN ‘↘’ ELSEIF SUM([Score]) = [Previous Score] THEN ‘→’ END
Add these to Label too and adjust label so it is aligned bottom centre, and coloured white. You may need to adjust size of font for each label item, and increase the height of each row to get the information to display.
Finalise the formatting by
Set the background colour of the whole sheet to #7299aa
Set the font of all the column headings and label headings to Tableau Medium, 9pt, white
Hide the Hole label heading (right click the label and hide field labels for columns)
Add a white border to the bars via the Colour shelf
Remove all column dividers
Remove row dividers from the pane only, ensure the row dividers for the headers remain.
Remove all gridlines , zero lines, axis rulers & ticks
Name the sheet HeatMap or similar.
Building the Average Score Per Person bar chart
On a new sheet, add Person to Rows and Overall Avg Score to Columns. Add a Sort to the Person pill that references the Sort field ascending
Add Overall Avg Score to the Colour shelf, and then adjust the colour scale so it uses a diverging colour scale which is then edited to use the same colour range as before and spans the same range, which means explicitly stating the start (-1) , middle (0) and end (10) values
We want to display a * against the winner
Winner Icon
IF SUM([Overall Avg Score]) = WINDOW_MIN(SUM([Overall Avg Score])) THEN ‘★’ ELSE ” END
Add this to Rows and adjust the table calculation so it is explicitly computing by Person only.
Create
Winner Label
IF SUM([Overall Avg Score]) = WINDOW_MIN(SUM([Overall Avg Score])) THEN ‘Coach Always Wins!’ END
and add this to Label along with Overall Avg Score. Increase the width of the bars to see the text and align middle left and change the font to white. Adjust the Tooltip.
Format the sheet
Set the worksheet background colour
Hide the Person column (uncheck Show Header)
Hide the Overall Avg Score axis (uncheck Show Header)
Remove all column/row dividers, gridlines, axis lines, zero lines
Add a white border around the bars (via the Colour shelf)
Increase the Size of the bars so there is a small gap between
Format the * to be white font
Hide the Winner Icon label (right click > hide field labels for rows)
Make the Winner Icon column as narrow as possible
Name the Sheet Avg Per Person or similar.
Building the Average Score Per Hole bar chart
On a new sheet, add Hole to Columns and Avg Score Per Hole to Rows. Add Avg Score Per Hole to Colour and adjust the scale as we did above so it ranges from green to blue and -1 to 10.
Show mark labels, adjust the tooltip. Set the background colour of the worksheet and remove all gridlines, zero lines, row/column dividers etc. Hide the Avg Score Per Hole axis and the hole labels (uncheck show header on the Hole pill). Adjust the font of the labels to be Tableau Medium and white. Add a white border around the bars. Increase the Size to leave a small gap.
Name the sheet Avg Per Hole or similar.
Building the dashboard
Set the background of the dashboard to the same colour of the worksheets.
Using containers, add a horizontal container and add a text object (for the title), the Avg Per Hole sheet and then a blank object. Remove the container that gets added with the colour legend.
Add another Horiztonal container beneath and add the Heatmap sheet and the Avg Per Person sheet.
Remove all padding. Remove all titles from the sheets. Set all the charts to fit entire view. Manually line up everything, but you’ll find you have an issue getting your horizontal bar chart to align to the player rows due to the header in the heatmap
(Note – you may notice your labels on the heat map aren’t displaying due to the space available). You can continue to adjust on Desktop so they do appear (make the heatmap have more vertical space), or wait until you’ve published to Tableau Public and see if you get the desired result… although at the point of writing , Tableau Public is having issues with the table calcs and causing odd behaviour with the display).
To fix this, go back to the Avg Per Person sheet and double click into column and manually type “” to create a dummy header row with no text. Hide the “” label (right click > hide field labels for columns). You can then adjust the height of this header label section to help get the alignment right.
Then make any final adjustments required – add the title, and any imagery etc. My published viz is here.
Kyle set this challenge while I was on my holidays. And because I was away and short on time, I’m cheating a bit this week. I’m not going to provide a full solution guide, but am writing this blog to maintain consistency 🙂
The challenge was very specific to just the new dynamic spatial parameters feature, which is well documented. I used these two resources to familiarise myself with the feature and to build the challenge
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.
Donna + DataViz 