I built an initial version of the heatmap only, which I’ve published here, but I couldn’t get the display to match Valerija’s when I clicked on it. (Note – I wasn’t bothered about Viz in Tooltip part at this point). I used 2 dimensions to display the yellow header section, and you can see below, when clicking, this is noticeable. But Valerija’s solution treats the header as a single entity.
I couldn’t figure out how she’d managed this, so I had to have a look at the solution, and then I built my own instance, which is what I’ll now blog about.
Defining the core fields
Connect to the datasource and add a data source filter to restrict the data by the latest year (as I used Superstore 2025, I filtered to 2025). Doing this meant I didn’t have to worry about adding worksheet level filter fields.
On a new sheet add Category and Sub-Category to Rows and add Sales to Text and then sort descending so you can see the data we need to work with. Format Sales to be $ to 0 dp.
Create a new field
Sales by Cat Rank
RANK(SUM([Sales]))
change it to be discrete and then add to Rows. Adjust the setting of the table calculation so it is computing by Sub-Category only, so the ranking restarts for each Category
We will also need to display the Category in upper case, so create
Category Upper
UPPER([Category])
and add to Rows.
Having this tabular layout just lets us clarify how the table calculation will be working.
Building the Heatmap Table
On a new sheet, add Category to Columns and Sub-Category and Sales to Text and align centrally (Note – I originally put Category Upper on the columns, and then changed later after taking all the screen shots)
Add Sales by Cat Rank to Rows. Change it to a continuous (green) pill and edit the axis to be reversed. Adjust the table calculation so it is computing by Sub-Category only.
Create a new field
One
1
Change the mark type to ganttbar and add One to the Size shelf, setting the aggregation to Avg. Increase the size of the mark via the Size shelf to as large as possible. Add Sales to Colour then add a white border.
We need the axis numbers to be central to the ‘row’, so to fix this, double click into the Sales by Cat Rank pill and change it to subtract 0.5 ( [Sales by Cat Rank] -0.5 )
Re-edit the axis to reverse it again.
So our ‘table’ is now displaying data on a axis from 0.5 up to 9.5. To add the header, we’re going to plot another mark at -0.5, so we can display a section of the same size as the existing ones. For this, double click into Rows and type MIN(-0.5).
This has the effect of creating a second marks card
Remove Sales from Colour and Sub-Category from Label. Add Category Upper to Label. Adjust the label text alignment and formatting and change the Colour to yellow.
Make the chart dual axis and synchronise the axis and we now have the required display.
Tidy up by
Remove row & column dividers
Remove gridlines, zero lines & axis ticks
Hide the right hand axis (right click > uncheck show header)
Hide the Category Upper column labels (right click pill > uncheck show header)
Remove the left hand axis title
Fix the left hand axis from -0.5 to 9.5
Format the axis with a custom formatting of #,##0;-#,##0;TOTAL so that 0 is displayed as the word TOTAL (this is very sneaky by the way, and took me a while to work out)
Then format the font to be bold
Adjust the Tooltip on the Sales by Cat Rank marks card to display the Sub-Category and Sales value.
Delete the text from the Tooltip on the MIN(-0.5) marks card
Name the sheet Table or similar
Building the Viz in Tooltip
On a new sheet add Order Date to Columns at the discrete month level (blue pill) and add Sales to Rows. Add Sub-Category and Category to Detail. Format the date axis so the dates are just using First Letter.
Create a new parameter
pSubCat
string parameter defaulted to Bookcases
Then create a field
Is Selected SubCat
[Sub-Category] = [pSubCat]
and add to the Colour shelf. Adjust accordingly, then make sure True is listed first in the legend, so the line is ‘on top’
Create a new field
Label Line
IF [Is Selected SubCat] THEN [Sub-Category] END
and add to the Label shelf and update to allow marks to overlap. Hide the Order Date label heading (right click > hide field labels for columns). Title the chart Line or similar
Remove gridlines and row/column dividers
Go back to the Table sheet, and update the Tooltip of the Sales by Cat Rank marks card to reference the Line chart and update the filter to pass as just <Category>
Adding the final interactivity
Create a dashboard and add the Table sheet. Then add a parameter action
Set Sub Cat Param
On hover of the Table sheet, set the pSubCat parameter with the value from the Sub-Category field, setting the value to <empty string> when the selection is cleared.
If all has been applied, then the line chart should just display the lines associated to the Sub-Categories in the same Category
Yoshi set the challenge this week to build a dashboard which looks like the visual you might get as part of a Pulse metric.
Define the parameters
Create a parameter to define the ‘reporting’ date
pBaseDate
date parameter defaulted to 21 Sept 2025
Create a parameter to capture the number of previous weeks the ‘forecasting’ values should be calculated against
pWeeks
integer parameter defaulted to 8, with a min value of 5 and max of 15, incremented every 1 step
Building the KPI card
This section displays the month to date sales and comparisons to previous month, based on the value in the pBaseDate parameter. We need several calculations
MtD Sales
IF [Order Date] >= DATETRUNC(‘month’, [pBaseDate]) AND [Order Date]<=[pBaseDate] THEN [Sales] END
format to $ with 0 dp.
Prev MtD Sales
IF [Order Date] >= DATEADD(‘month’, -1, DATETRUNC(‘month’, [pBaseDate])) AND [Order Date]<= DATEADD(‘month’, -1, [pBaseDate]) THEN [Sales] END
MtD Sales Diff
SUM([MtD Sales]) – SUM([Prev MtD Sales])
custom format to +”$”#,##0;-“$”#,##0
MtD Sales % Diff
[MtD Sales Diff] / SUM([Prev MtD Sales])
custom format to +0.0%;-0.0%;0%
On a new sheet add MtD Sales, MtD Sales Diff and MtD Sales % Diff to Text. Change the mark type to shape and set to be a transparent shape (refer to this blog to understand how to set this up). Adjust the layout and style of the text on the Label. Set the sheet to Entire View and align the text to the left. Update the title of the sheet and remove the Tooltip.
Building the Line Chart
Before building the viz, we’ll start by building the calculations and checking them through a tabular display.
On a new sheet, show the pBaseDate and pWeeks parameters. Then add Order Date as a discrete exact date (blue pill) to Rows and add MtD Sales to Text. The MtD Sales values should only display against the dates from 1st to 21st Sept.
For each day, we need to calculate the 25th percentile of the Sales value using the gp model against the same day of the week for the previous data.
These 2 values give the ‘range’ we want to check the actual MtD Sales value against. Add both values into the table, and update the table calculation setting of each so they are computing explicitly by Order Date
However we only want the calculations to be based on the last x weeks, so we want to filter the display.
Add this to the Filter shelf and set to True. This will adjust the 25th & 75th Percentile values as they are only considering data within the display (eg compare the highlighted values for 01 Sept against those in the image above)
But when it comes to ‘plotting this data’ on a chart, we only want to display the data for the current month (based on pBaseDate), so we need to apply a second filter that restricts the dates displayed further, but that does not eliminate the data in such a way that we lose reference to the previous x weeks.
We can do this using a filter based on a table calculation
Filter: Dates for Chart
LOOKUP(MIN([Order Date]),0) >=DATETRUNC(‘month’, [pBaseDate]) AND LOOKUP(Min([Order Date]),0) <= [pBaseDate]
The LOOKUP function is a table calc that is basically returning the same value for Order Date and comparing it against the base date. But because it is a table calc, when applied as a filter, it will only be applied after other computations.
Add this to the Filter shelf and set to True. Then adjust the table calc so it is explicitly computing by Order Date and then re-edit the filter again so it is just considering True values (changing the table calc setting, resets this).
Let’s start to build the viz :
Duplicate the tabular sheet above. Move Order Date to Columns and change to be continuous (green pill), Move MtD Sales to Rows and move Measure Values to Rows. Move the Measure Names field to the Colour shelf of the Measure Values marks card. Unstack the marks ( Analysis menu > stack marks > off) – if you can’t see 2 areas, swap the order of the pills in the Measure Values box so the 25th Percentile is listed first. Adjust the colours of the marks so the 25th percentile is white and the 75th percentile is pale blue (#e6f2fe). Ensure the opacity of these marks are 100%. Set the colour of the Mtd Sales line to bright blue.
Note – by moving the pills around and having already explicitly set the table calculation settings, we know the fields will be computing correctly. If you wish, you can build the viz from scratch, but you will need to explicitly set all the table calc pills to compute by Order Date again.
Make the chart dual axis and synchronise the axis. Right click the right hand axis and move marks to back to make the line display in front.
Custom format both of these to dd/mm and then add both to the Detail shelf of the All marks card. Set to be continuous exact date (green pills).
Add 2 reference lines to the Order Date axis, which reference these pills.
Add pBaseDate to the Detail shelf of the All marks card too, and add an additional reference line to that field. In this instance format the reference line and apply a custom format to the date to be dd/mm
Adjust the Tooltip via the All marks card. Remove all gridlines, zero lines and row/column dividers. Hide the right hand axis and the Order Date axis. Remove the title from the left hand axis.
To show the summary of how the MtD Sales value for the pBaseDate compares to the range, we will use the caption feature of the worksheet, which can reference fields, but we need these fields to essentially be ‘constants’ for every row of data, so we need some new fields.
Sales for Base Date
WINDOW_MAX(SUM(IF [Order Date] = [pBaseDate] THEN [Sales] END))
format this to $ with 0 dp.
25th Percentile for Base Date
WINDOW_MAX(IF MIN([Order Date]) = [pBaseDate] THEN [25th Percentile] END)
75th Percentile for Base Date
WINDOW_MAX(IF MIN([Order Date]) = [pBaseDate] THEN [75th Percentile] END)
Switch back to the tabular view of data and add these 3 fields. As they’re all table calculations, you need to set them to be computing by Order Date only (as we did above).
You should find that the values for the latest row (highlighted below) are displayed against every row in the 3 additional columns
With this, we can now work out the ‘text’ we want to disply in the caption
Expected Range Text
IF [Sales for Base Date] > [75th Percentile for Base Date] THEN ‘above’ ELSEIF [Sales for Base Date] < [25th Percentile for Base Date] THEN ‘below’ ELSE ‘within’ END
Add this to Rows, check the table calc settings, and then adjust the pBaseDate value so you can see the text change.
Now switch back to the line chart, and display the Caption (worksheet menu > show caption). Add Sales for Base Date and Expected range Text to the Detail shelf of the All marks card, adjusting the table calc settings as we’ve done before.
Then edit the caption and remove all text and update, referencing the various fields and parameters.
Building the bar chart
On a new sheet, add Segment to Rows and MtD Sales Diff to Columns. Sort descending. Create a new field
Diff is +ve
[MtD Sales Diff]>=0
Add to Colour and adjust accordingly. Show mark labels and set to match mark colour. Format the display to remove all column/row dividers, gridlines and zero lines. Display the RowAxis Ruler as a thicker grey line.
Then add all the components to a dashboard, using containers and padding to organise the display. Make sure to display the caption for the line chart worksheet.
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.
Yoshi set this week’s challenge which focused on 2 areas : the use of tools such as CHAT GPT to help us create Tableau RegEx functions (which are always tricksy), and the build of the chart itself.
Creating the REGEX calculated fields
I chose to use CHATGPT and simply entered a prompt as
“give me a regex expression to use in Tableau which extracts a string from another string in a set format. The string to interrogate is”
and then I pasted in the string I’d copied from one of the Session Html fields. I got the following response
along with many more code snippet examples. I used these to create
returns the day of the session as a proper date field, all hardcoded to 2023, since this is when the data is from.
Start Date
DATETIME(STR([Date]) + ” ” + [Start Time])
returns the actual day & start time as a proper datetime field.
End Date
DATETIME(STR([Date]) + ” ” + [End Time])
Duration
DATEDIFF(‘second’, [Start Date], [End Date])
Add fields to a table as below
Firstly, when we build the viz, we’ll need to sort it based on the Start Date, the Session Level (where Advanced is listed first, and All Levels last) and the Duration (where longer sessions take higher precedence) . For this we need a new field which is a combination of information related to all these values.
Sort
STR([Start Date]) + ‘ – ‘ + STR( CASE [Session Level] WHEN ‘advanced’ THEN 4 WHEN ‘intermediate’ THEN 3 WHEN ‘beginner’ THEN 2 ELSE 1 END ) + STR([Duration]/100000)
(this just took some trial and error)
Add this to the table after the Session ID pill and then apply a Sort to the Session ID pill so it is sorting by the Minimum value of the field SortAscending. You should see that when the start time and session level match, the sessions are sorted with the higher duration first.
The viz also lists the AM & PM sessions as separate instances, where the 1st session of the PM session is displayed on the same ‘row’ as the 1st session of the AM session. To handle this we need
Session Index
INDEX()
Make this discrete and add to the table after the AM|PM pill. Change the table calculation so it is computing by all fields except AM|PM and Session Date. The numbering should restart at each am/pm timeslot
Finally, the last field we need to build this viz, is another date field, so we can position the gantt chart bars in the right places. As the viz already segments the chart by Session Date and AM|PM, we can’t reuse the existing Start Date field as this will display the information as if staggered across the 3 days. Instead we want to create a date field, where the day is the same for al the sessions, but just the time differs
Baseline Date
DATETIME(STR(#2023-01-01#) + ” ” + [Start Time])
the date 01 Jan 2023 is arbitrary and could be set to any day. Add this into the table, so you can see what it’s doing. You will probably need to adjust the table calculation on Session Index again to include Baseline Date in the list of checked fields.
Building the viz
On a new sheet, add Session Date to Columns and manually re-order so Tuesday listed first. Then add AM|PM to Columns . Add Baseline Date as a continuous exact date (green pill) to Columns . Add Session Id to Detail then add Session Index to Rows and adjust the table calculation so it is computing by all fields except Session Date and AM|PM.
Create a new field
Size
SUM([Duration])/86400
and add this to the Size shelf. 86400 is the number of seconds in 24hrs, so adjusts the size of the gantt bars to fit the timeframe we’re displaying.
Add Session Level to Colour and adjust accordingly. You will need to readjust the Session Index table calc to include Session Level in the checked fields. Add a border to the bars. Edit the Baseline Date axis so the axis ranges are independent
Add Description and Session Title to Detail and again adjust the Session Index table calc to include these fields too. Apply a Sort to the Session Id field so the data is sorted by the Sort field descending
Add Location, Start Time and End Time to Tooltip, and update accordingly. Then apply formatting to
change font of the Session Date and AM|PM header values
remove the Session Date/ AM|PM column heading label (right click > hide field labels for columns)
Hide the Session Index field
Hide the Baseline Date axis
Add column banding so the Wednesday pane is coloured differently
Add the ability to highlight the Session Title and Description by selecting Show Highlighter from the context menu of each pill
Then add the sheet to a dashboard, using containers to display the colour legend and the highlighter fields.
Lorna set this week’s #WOW2025 challenge asking us to recreate what looked to be a simple line and area chart combination. I have to admit this was indeed a bit tricksy. I built out the data relatively quickly, but when trying to create the area chart I just wasn’t getting any joy.
After lots of trial and error, attempting various options, I eventually resorted to checking out Lorna’s solution. I found there were two specific areas that were causing the creation of the area chart to fail..why this happens is still a mystery….
Before we get on with the solution, I figured it was worth detailing at the start what I tried that failed, just in case you’ve been banging your head against a brick wall too 🙂
What didn’t work
The solution requires table calculations; for one calculation we need a percent of sales per quarter which means we also need to work out the total sales for the quarter. I originally used a FIXED LOD for this but found I could only get the area chart to work if I used a WINDOW_SUM() table calculation instead.
This challenge also requires all the customers for each quarter to be in the view to identify which are in the top X. I used a sorted INDEX() table calculation to identify the top X, but this too failed to work when it came to actually creating the area chart. Using the RANK() table calculation instead worked.
Like I said before why? I don’t know… but putting down as one of ‘just those things’ and hoping that maybe I’ll remember this post in future if it happens again!
Now back to the solution guide…
Creating the calculations
We’re dealing with table calculations, so I’m going to build out the required data in a tabular format to start with.
Create a parameter
pTop
Integer parameter ranging from 5 to 20 with the step size of 5 defaulted to 20
Show this parameter on the sheet, and then create a calculated field
Order Date (Quarters)
DATE(DATETRUNC(‘quarter’, [Order Date]))
Format to the YYYY QX style. Add Order Date (Quarters) to Rows as a discrete exact date (blue pill). Add Customer Name to Rows and add Sales to Text.
We need to identify customers who are in the top x for each quarter. Create a new calculated field
Is Top X Customer?
RANK(SUM([Sales]))<=[pTop]
Add to Rows and adjust the table calculation so it is computing by Customer Name only.
We now want the Sales just for those customers who are in the top x, so create
Top X Sales
IF [Is Top X Customer?] THEN SUM([Sales]) END
Add this field to Rows and we should see that Sales are only displayed for those customers that where Is Top X Customer? = True.
We now need the total of these sales per quarter so create
Total Top X Sales
WINDOW_SUM([Top X Sales])
Add this to the table and adjust the table calculation so both nested calculations are computing by just Customer Name. You should see that the total value is the same for every row for each quarter
We also need the value of the total sales in the quarter regardless of whether the customer is in the top X or not so create
Total Sales per Quarter
WINDOW_SUM(SUM([Sales]))
Add this to the table and again adjust the table calculation to compute by Customer Name only.
Now we have these two figures we can calculate the percentage. Create a new calculated field
Sales % per Quarter
[Total Top X Sales]/[Total Sales per Quarter]
Format this as a % to 1dp. Add to the table.
Duplicate this sheet. I like to do this as I make further changes so I don’t want to lose what I had.
Remove all the surplus field so that only Order Date (Quarters), Customer Name and the Sales % per Quarter fields remain
Create a new field
Customer Index
INDEX()
Convert this field to discrete (right click on the field).
Add this field to the Filter shelf and select 1. Adjust the table calculation so that it is computing using Customer Name only and sorted by SUM of Sales
Re-edit the filter and reselect 1 (making changes to the table calculation resets the filter values).
If everything has been set properly then you should end up with 1 row per quarter. Move the Customer Name pill from Rows to Detail.
We’ve now got the core fields we need to build the viz.
Building the Viz
Once again start by duplicating this sheet. Move Order Date (Quarters) to Columns and change to be continuous (green pill), then move Sales % per Quarter to Rows.
Add another instance of Order Date (Quarters) to the path shelf and change to be an attribute. You should now have a line chart. Increase the size of the line a little.
Add another instance of Sales % per Quarter to Rows, making sure the table calculation is set exactly as the existing version. Change the mark type of the 2nd marks card to Area.
Make the chart dual axes and synchronise axes .
Finally, tidy up the chart by
Adjusting the Tooltip
Removing gridlines, zero lines and row/column dividers
Hide the right hand axis
Fix the left hand axis to end at 1 (so the axis goes to 100%)
Edit the left hand axis title
Update the sheet title to reference the pTop parameter
Then add the viz to a dashboard. My published viz is here.
It’s back to the EFL this week for my #WOW challenge. Once again I’ve tried to provide a multi-level challenge – a version that uses some core skills and features, and a version that just pushes the display a bit further, just for fun. I’ll start with the core build and then use that as the base for the bonus challenge.
Setting up the parameters
The main crux of this challenge is measure swapping, so for this we need a parameter
pMeasure
integer parameter listing values 1 to 4 which are aliased as per the screen shot below. Default value is 1 (Points).
Note – you can use a string parameter with the actual words. I just chose to use this method to demonstrate the aliasing ability. Also when referencing this parameter in a CASE statement (which we’ll do shortly), using integer values for comparisons is slightly more efficient than string comparisons.
We also need the user to have the ability to select the team they want to track
pSelectedTeam
string parameter defaulted to your preferred team (I chose Chelsea). This is a List parameter that is populated from the Team field via the Add values from button
Building the calculations
We need to determine the measure to display based on the parameter selection
Measure to Display
CASE [pMeasure] WHEN 1 THEN SUM([Cumulative Points]) WHEN 2 THEN SUM([Cumulative Goal Difference]) WHEn 3 THEN SUM([Cumulative Goals For]) WHEN 4 THEN SUM([Cumulative Goals Against]) END
We also will need to colour the bars based on the selected team
Is Selected Team
[Team]=[pSelectedTeam]
and we need to sort the data based on best to worst, but need to consider that for Goals Conceded, the higher the value the worse the team is.
Sort Measure
IF [pMeasure] = 4 THEN [Measure to Display]*-1 ELSE [Measure to Display] END
We only want to show teams once they start participating in a Season. For this, we need to identify the team’s 1st season in the EFL
1st Season per Team
{FIXED [Team]: MIN(IF [Cumulative Points] > 0 THEN [Season End Year] END)}
and then we can create a field we can filter on, based on this
Show Team
[Season End Year]>=[1st Season per Team]
We only want to show a label against the 1st team and the selected team, so create
Label to display
IF MIN([Is Selected Team]) OR FIRST()=0 THEN [Measure to Display] END
and finally, we need to display the value of the current season’s measure on the tooltip, as well as the cumulative value, so we need another case statement
Tootltip Measure
CASE [pMeasure] WHEN 1 THEN SUM([Points]) WHEN 2 THEN SUM([Goal Difference]) WHEn 3 THEN SUM([Goals For]) WHEN 4 THEN SUM([Goals Against]) END
Building the core bar chart
On a new sheet, add Team to Rows and Measure to Display to Columns. Add Season End Year to Filter and select 1993. Add Show Team to Filter and select True. Apply a sort to the Team field to sort by the field Sort Measure descending.
Add Is Selected Team to Colour and adjust accordingly. Add Label to display to Label. Add Season and Tooltip Measure to Tooltip and update accordingly,
Show the pMeasure and pSelectedTeam parameters and the Season End Year Filter. Adjust the Season End Year filter control so that the All option isn’t available and it displays as a single value slider control.
Move the Season End Year filter control on by one value and notice how the chart transitions. Adjust the Animations settings (Format menu > Animations) to be sequential and slow
Finally tidy up the display by
hiding the Measure to Display axis (right click > uncheck show header)
hiding the Team row label (right click > hide field labels for rows)
widen each row a bit
hide gridlines, zero lines, axis rulers and axis ticks
add pale grey row dividers
set the background colour of the worksheet
adjust the font of the row labels – I used Tableau Book 8pt in colour #3e1756 (dark purple)
Name the sheet Core or similar and add to a dashboard setting it to Fit Entire View
Add a title to the dashboard that references the pSelectedTeam parameter.
We’re going to use a gantt bar to simulate a central line for each row. This bar needs to extend to the largest measure in the table for every row, so this is the point we’ll plt
Max Measure
WINDOW_MAX([Measure to Display])
Add this to the Columns shelf, and on the Ma Measure markls card that gets added, remove the Is Selected Team from the Colour shelf, and change the mark type to gantt bar.
The bar needs to extend to the 0 or the minimum value in the window (if its less than 0), so we need a field to show the difference between the max and the minimum of 0 or the ‘window min’, but we need to multiple by -1 so the size extends in the right direction.
Max-Min Diff
([Max Measure] – MIN(WINDOW_MIN([Measure to Display]),0)) * -1
Add this to the Size shelf, and then update the size to be as small as possible. Remove Label to Display from the marks card and add a border the same colour as the background to the Gantt bar (via the Colour shelf) to make the line very narrow.
Add Team to the Label shelf and align centre left. Format the label text to be 8pt and dark purple. Then make the chart dual axis and synchronise the axis.
Right click the top axis and select move marks to back. Then hide both axis (right click, uncheck show header) and hide the Team pill on Rows too (again uncheck show header)
Verify the Tooltip on the gantt bar displays the same as on the main bar. If not add the relevant fields and adjust to match.
Tidy up the formatting by removing the row and column dividers. If need be adjust the colour of the Gantt bar to be a paler grey.
Change the measure value to Goal Difference and adjust the year to 2024 and check the display looks as expected, especially at the bottom – the gap between the label of the team at the bottom and the bar is minimal.
Add the chart to a dashboard – the simplest way is to duplicate the core dashboard and then use the swap sheets feature to quickly swap the main vizzes.
For Community Month at #WOW2025 towers, Lorna presented a challenge one of her colleagues had brought to her which they solved together. The need is to identify the top X customers in each year (which may not contain the same set of customers each year), and then present the sales contribution, either as a group or individually compared to the rest. Lorna gave a hint in the challenge that sets would help : “Your job is to figure out the best way to SET this up with the last 3 years dynamically”.
It took me a bit of a while to figure out how to make this work, and at the point of writing, haven’t looked at the solution to know if there was a better way. I ultimately ended up creating 3 sets to fulfil this challenge.
Setting up the parameters
This challenge requires 3 parameters
pTop
This identifies how many ‘top’ customers we want to consider. Defined as an integer from 10 to 100, defaulted to 20, that increments every 10 units
pShowCustomers
Determine whether the top customers’ contributions are displayed individually or as a group. Defined as a boolean, defaulted to False, and aliased to Yes or No
pPercentofTotal
Indicate whether the information is displayed as a % of total sales for that year, or as absolute sales values. Defined as a boolean, defaulted to True, and aliased to Yes or No.
Defining the core calculations
The requirement states to be able to determine the last 3 years ‘dynamically’. For this I created
Max Date
{FIXED: MAX([Order Date])}
to return the maximum Order Date in the whole data set.
We want to be able to restrict the data to the last 3 years, so create
Records to Show
DATEDIFF(‘year’, [Order Date], [Max Date]) <=2
I need to create a set for each of the 3 cohorts – the top customers for the latest year, the top for the previous year and the top for the year before that. For this I first need to determine the Sales for each of those timeframes.
The sales for the current year
Sales – CY
IF YEAR([Order Date]) = YEAR([Max Date]) THEN [Sales] END
The sales for the previous year
Sales – PY
IF YEAR([Order Date]) = YEAR([Max Date])-1 THEN [Sales] END
and the sales for the previous previous year
Sales – PPY
IF YEAR([Order Date]) = YEAR([Max Date])-2 THEN [Sales] END
I can then create the sets of customer I need (right click on Customer ID > Create > Set)
Customer ID Set – CY
get the Top number of records using the pTop parameter, and based on the sum of the Sales – CY field
Repeat the same process to create
Customer ID Set – PY
get the Top number of records using the pTop parameter, and based on the sum of the Sales – PY field
and
Customer ID Set – PPY
get the Top number of records using the pTop parameter, and based on the sum of the Sales – PPY field
To verify/understand what we’ve created, on a new sheet
Add Customer ID to Rows
Add Order Date to Columns at the Year level as a discrete (blue) pill
Add Records to Show to Filter and set to True.
Add Sales to Text.
Sort by the 2024 Sales value descending.
Add Customer ID Set – CY to Rows.
You should see the first 20 rows (assuming you haven’t changed the pTop value, display as In
If you now change the sort to sort by 2023 Sales descending, and swap the Customer ID Set – CY with the Customer ID Set – PY, you’ll get the same
So now that’s understood, we want to tag each of our customers based on the year of the order, whether they’re in the top n or not, and whether we want to display the customers individually or not
Group – Detail
IF (YEAR([Order Date]) = YEAR([Max Date]) AND [Customer ID Set – CY]) THEN IF [pShowCustomers] THEN [Customer ID] ELSE ‘Top N’ END ELSEIF (YEAR([Order Date]) = YEAR([Max Date])-1 AND [Customer ID Set – PY]) THEN IF [pShowCustomers] THEN [Customer ID] ELSE ‘Top N’ END ELSEIF (YEAR([Order Date]) = YEAR([Max Date])-2 AND [Customer ID Set – PPY]) THEN IF [pShowCustomers] THEN [Customer ID] ELSE ‘Top N’ END ELSE ‘Other’ END
We’re also going to want to count customers, so need
Count Customers
COUNTD([Customer ID])
On a new sheet add Order Date at the year level as a discrete (blue) to Rows and add Group Detail to Rows too. Add Records to Display to Filter and set to True. Add Sales and Count Customers into the table. Show the pTop and pShowCustomers parameters
When pShowCustomers is set to No, you should just see 2 groupings per year
When set to Yes, you’ll get the Customer IDs listed
Note – the Sales numbers should reconcile to the solution – the count might not, which I believe is due to the solution counting distinct Customer Names rather than Customer ID.
To finalise the core calculations we need to build the initial viz, we have a different display depending whether we’re displaying the absolute or % Sales values.
Create
Sales % Total
SUM([Sales]) / TOTAL(SUM([Sales]))
format to decimal to 2 dp and add into table, adjusting the table calculation so it is computing by the Group – Detail only, so the percentage per year is being displayed.
Then we need
Measure to Plot
IF [pPercentofTotal] THEN [Sales % Total] ELSE SUM([Sales]) END
format this to a number to 2 dp (just so you can see it has a value) and add to the table, applying the same table calculation settings. Display the pPercentofTotal parameter and flip between to see the column change.
Building the Viz
On a new sheet, add Records to Show to filter and set to True. Add Order Date at the year level as a discrete (blue) pill to Rows. Add Group – Detail to Detail. Change the mark type to bar. Add Measure to Plot to Columns and adjust the table calculation, so it’s computing just by Group-Detail.
Ste the sheet to fit width and show the 3 parameters.
Create a new field
Group – Top N
IF (YEAR([Order Date]) = YEAR([Max Date]) AND [Customer ID Set – CY]) THEN ‘Top N’ ELSEIF (YEAR([Order Date]) = YEAR([Max Date])-1 AND [Customer ID Set – PY]) THEN ‘Top N’ ELSEIF (YEAR([Order Date]) = YEAR([Max Date])-2 AND [Customer ID Set – PPY]) THEN ‘Top N’ ELSE ‘Other’ END
and add to Colour, adjusting the colours to suit. You’ll then need to update the table calculation of the Measure to Plot field to ensure Group – Top N is also checked.
We need to display labels, but these need to differ based what measure we’re showing, and the format is different, so create
Label – % Total
IF [pPercentofTotal] THEN [Sales % Total] END
format this to % with 1 dp and
Label – Sales
IF NOT([pPercentofTotal]) THEN [Sales] END
format this $ K to 1 dp.
Add both of these to the Label shelf and ensure they are listed directly side by side. Only 1 will ever actually display.
Change the pShowCustomers to Yes, and then add a white border via the Colour shelf. Add a Sort to the Group – Detail pill to sort by Sales ascending.
Add Sales, Sales % Total and Count Customers to the Tooltip shelf. additionally create
Tooltip – Customer
IF [pShowCustomers] AND [Group – Detail] <> ‘Other’ THEN [Customer Name] END
and add this to Tooltip too. Adjust the Tooltip to suit (make sure Sales % Total)is computing by both Group – Top N and Group – Detail so has the correct numbers.
Finally, hide the axis (uncheck show header on the Measure to Plot pill) and hide the Order Date label (right click and hide field label for columns).
Then add the sheet to a dashboard, and arrange the parameters suitably.
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.
Donna + DataViz 