Yusuke set this week’s challenge focusing on stacked bar charts, and providing a different way of presenting them. This certainly took a bit of thought (and trial and error) and the hints were useful.
This is all built on one sheet!
Create field
Order Date Month
DATE(DATETRUNC(‘month’, [Order Date]))
Format to mmm yyyy and add to Columns as a discrete exact date (blue pill). Add Category to Filter and select Office Supplies. Format Sales to $ with 0 do and add to Rows. Change the mark type to bar. Add Sub-Category to Colour and adjust to suit. Widen each column. This is the basic stacked bar chart.
We need the bars sorted with the largest Sub-Category per month on the bottom of the stack. For this create a CombinedField by selecting both Order Date Month and Sub-Category in the Data pane, then right-clicking Create > Combined Field
This automatically creates a field called Order Date Month & Sub-Category. Add this field to the Detail shelf, then apply a sort to pill, to sort by sum of Sales ascending. Make sure the combined pill is displayed on the marks card above the Sub-Category field on Colour.
Add Sub-Category and Sales to the Label shelf (positioning both fields below the Sub-Category Colour pill). Align left. Then add a reference line to the Sales axis, that is set per cell to display the Sum of Sales.
Format the reference line label as required.
To build the 2nd half of the display, we need to have an axis on Columns. So double click into the Columns shelf and type MIN(0). Your display will immediately go a bit weird!
Set the view to fit height to give a bit more space. Then click on the Size shelf, and change the option from Manual to Fixed and ensure the alignment is to the left.
Edit the MIN(0) axis to be fixed from 0 to 1, and then click on the Size shelf again and add at width of 0.95 so the bars fill most of the horizontal space, but are not butted up against each other.
Now for the 2nd part of the chart. This part will require a dual axis – 1 axis to display the faded full width bars, and 1 axis to display the % of total bars.
Firstly, create an additional ‘row’ by double-clicking into the Rows and typing MIN(1). this immediately gives us the ‘background’ bars. Set the opacity on the Colour shelf to 20% and remove the 0.95 width from the Size shelf.
Create a new field
% of Total Sales
SUM([Sales]) / TOTAL(SUM([Sales]))
Format this to % with 1 dp and add to the Label shelf. Set the table calculation to be computing using Sub-Category only.
Adjust Label as required, check the box that allows labels to overlap other marks and align right. It’s all a bit squashed, so change the view from Fit Height to Standard and just manually widen the display.
Now add another instance of MIN(1) to the Rows shelf, to create another marks card. Add % Total Sales to the Size shelf on this card and remove all the fields on the Label shelf.
Make the to MIN(1) pills dual axis, and synchronise the axis. Remove Measure Names from the Colour shelf on both the MIN(1) marks cards.
Change the Category filter to both the other options and adjust the colours to suit. You will notice that the stacked marks in the bottom half will be wider with different categories. To resolve this for a cleaner display, edit the MIN(1) axis to be fixed from 0 to 10.
Finally tidy up by
hiding the right hand axis (un check show header)
Changing the title of the MIN(1) axis, and not displaying any tick marks on that axis.
Removing all gridlines & zero lines.
Adjusting font sizes of labels as required
Updating the Tooltip.
And that should be it. Just add to a dashboard and use containers to display the colour legend and filter. My published viz is here.
There’s a lot to pack into this one, so let’s get cracking.
Modelling the data
To reduce the number of calculated fields needed, and to make the build slightly easier, the data needs to be pivoted, to have a row for the Sales and a row for Profit.To do this connect to the data source, then ‘ctl-click’ to select the Sales and Profit columns, and then select Pivot
Rename the fields: Pivot Field Names -> Metric and Pivot Field Values -> Metric Value.
Creating the parameter
The weekly date selector is driven by a parameter. For the purposes of this exercise, I just chose to hardcode the weekly values. If this was being done for a real world scenario, then you’d likely use a field within in the dataset to drive the population of the parameter.
pSelectedWeek
Date parameter containing the list of relevant dates, formatted as required and defaulted to 9th Aug 2026.
Creating the Calculated Fields
There’s a lot of fields required for this build. We’ll define the majority of the core ones here:
Capture the relevant Sales/Profit value for the selected week
(note the timeframe from the previous year needs to include the equivalent week of the one selected, whereas the ‘previous’ 13 week timeframe excludes it. Hence the DATEADD functions refer to 51 weeks rather than 52. I also chose to display as -13-51 rather than -64, so it was clear how I’d shifted the dates).
Format all these to a $ with 0 dp.
We then need the difference between these values and the selected week value.
Format these to a custom number format of +”$”#,##0;-“$”#,##0
We then need to determine the standard deviation of the weekly Sales/Profit values
SD – Last 13 Weeks
{FIXED [Metric]: STDEV(
{FIXED [Metric], DATETRUNC(‘week’, [Order Date]): SUM(IF ([Order Date]>= DATEADD(‘week’, -13, [pSelectedWeek]) AND [Order Date]<[pSelectedWeek]) THEN [Metric Value] END)}
)}
This is essentially getting the sum of the sales or profit per week within the relevant timeframe, and then calculating the standard deviation of that.
Similarly we need
SD – PY Last 13 Weeks
{FIXED [Metric]: STDEV(
{FIXED [Metric], DATETRUNC(‘week’, [Order Date]): SUM(IF ([Order Date]>= DATEADD(‘week’, -13-51, [pSelectedWeek]) AND [Order Date]<DATEADD(‘week’, -51,[pSelectedWeek])) THEN [Metric Value] END)}
)}
Format these to $ with 0 dp.
If you put these all out onto a sheet along with the parameter, you can test the values you get with the solution to sense check you’re on the right path.
There’s lots more fields required, but we’ll address as we build each sheet.
Building the RAG Indicator card
On a new sheet, add Metric to Columns and manually re-order, so Sales is listed first. Then add Value – Selected Week as a discrete field to Columns (blue pill). Double click into columns shelf and manually type MIN(1) to create an axis we can then ‘build’ our cards within.
Change the mark type to Bar, and edit the axis to be fixed from 0 to 1. We need to create a field for the Rows, so create
Timeframe
IF [Order Date]>= DATEADD(‘week’, -13, [pSelectedWeek]) AND [Order Date]<[pSelectedWeek] THEN ‘Better than usual?’
ELSEIF [Order Date]>= DATEADD(‘week’, -13-51, [pSelectedWeek]) AND [Order Date]<DATEADD(‘week’, -51,[pSelectedWeek]) THEN ‘Better than last year?’
END
Add this to Rows and exclude the Null option (this will automatically add Timeframe to Filter). Re-order the row values, increase the Size of the bars to maximum and add a white border via the Colour shelf.
To colour and label the sections, we need to firstly understand what ‘state’ each metric was in, and then from this, we can then make an appropriate label.
Metric State – Last 13 Weeks
IF SUM([Value – Selected Week ]) > SUM([Value – Last 13 Week Avg]) + SUM([SD – Last 13 Weeks]) THEN ‘Above range’
ELSEIF SUM([Value – Selected Week ]) >= SUM([Value – Last 13 Week Avg]) THEN ‘Above avg’
ELSEIF SUM([Value – Selected Week ]) < SUM([Value – Last 13 Week Avg]) – SUM([SD – Last 13 Weeks]) THEN ‘Below range’
ELSE ‘Below avg’
END
Metric State – PY Last 13 Weeks
IF SUM([Value – Selected Week ]) > SUM([Value – PY Last 13 Weeks Avg]) + SUM([SD – PY Last 13 Weeks]) THEN ‘Above range’
ELSEIF SUM([Value – Selected Week ]) >= SUM([Value – PY Last 13 Weeks Avg]) THEN ‘Above avg’
ELSEIF SUM([Value – Selected Week ]) < SUM([Value – PY Last 13 Weeks Avg]) – SUM([SD – PY Last 13 Weeks]) THEN ‘Below range’
ELSE ‘Below avg’
END
We can then create
Display Response
IF MIN([Timeframe]) = ‘Better than usual?’ THEN
CASE [Metric State – Last 13 Weeks]
WHEN ‘Above range’ THEN ‘Yes ▲’
WHEN ‘Above avg’ THEN ‘Yes’
WHEN ‘Below avg’ THEN ‘No’
WHEN ‘Below range’ THEN ‘No ▼’
END
ELSEIF MIN([Timeframe]) = ‘Better than last year?’ THEN
CASE [Metric State – PY Last 13 Weeks]
WHEN ‘Above range’ THEN ‘Yes ▲’
WHEN ‘Above avg’ THEN ‘Yes’
WHEN ‘Below avg’ THEN ‘No’
WHEN ‘Below range’ THEN ‘No ▼’
END
END
Add this field to the Label and Colour shelf. Set the sheet to Fit Entire View, so you have space to see everything. The Label will need to be aligned middle centre, and the colours will need to be adjusted. You will need to select each date in turn to get all the four colour variations required.
Tidy this sheet up by removing tooltips, hiding the MIN(1) axis, removing row & column dividers, hiding row and column field labels, and formatting the Sales/Profit labels.
Building the Last 13 Week Comparison KPI card
On a new sheet, add Metric to Columns and re-order so Sales is listed first. Change the Mark type to shape and select a transparent shape.
Add Metric, Diff – to Last 13 Weeks, and Value – Last 13 Week Avg to Label. Set the sheet to Fit Entire View and align the text top left, then adjust the style and layout as required.
We need another field to show the difference from the range when the state is above or below range
Diff – to range Last 13 Weeks
IF [Metric State – Last 13 Weeks] = ‘Above range’ THEN SUM([Value – Selected Week ]) – (SUM([Value – Last 13 Week Avg]) + SUM([SD – Last 13 Weeks]))
ELSEIF [Metric State – Last 13 Weeks] = ‘Below range’ THEN SUM([Value – Selected Week ]) – (SUM([Value – Last 13 Week Avg]) – SUM([SD – Last 13 Weeks]))
ELSE NULL
END
Add this to Label and then format the field with custom formatting as follows :
“▲ Above range by $”#,##0;”▼ Below range by $”#,##0
Tidy up by hiding the header and then removing row/column dividers. Update the title.
Building the Previous Year Last 13 Week Comparison KPI card
Create a new field
Diff – to range PY Last 13 Weeks
IF [Metric State – PY Last 13 Weeks] = ‘Above range’ THEN SUM([Value – Selected Week ]) – (SUM([Value – PY Last 13 Weeks Avg]) + SUM([SD – PY Last 13 Weeks]))
ELSEIF [Metric State – PY Last 13 Weeks] = ‘Below range’ THEN SUM([Value – Selected Week ]) – (SUM([Value – PY Last 13 Weeks Avg]) – SUM([SD – PY Last 13 Weeks]))
ELSE NULL
END
Format this using the same custom formatting as described above.
Duplicate the sheet above. Replace the fields with the equivalent ‘PY’ versions, and update the title
Creating the Sales bar chart
On a new sheet, add Metric to Filter and select Sales. Add Order Date to Columns and set to the ‘week’ level as a discrete (blue) pill. Add Metric Value to Rows. Change the mark type to bar.
Create a new field
Filter – Timeframe – Bar
[Order Date]>= DATEADD(‘week’, -13, [pSelectedWeek]) AND [Order Date]<DATEADD(‘week’,1,[pSelectedWeek])
Add to the Filter shelf and select True.
We need to isolate the selected week from the other weeks, so create
Is Selected Week
WEEK([Order Date]) = WEEK([pSelectedWeek])
And add this to Columns before the Order Date field.
Create field
Colour – Last 13 Weeks
IF MIN([Is Selected Week]) THEN [Metric State – Last 13 Weeks] END
Add to Colour and adjust accordingly. Again, you’ll need to cycle through the date options to set the appropriate colours.
We want to create a average reference line, but only for the previous weeks
Bar – Ref Line
IF NOT([Is Selected Week]) THEN [Metric Value] END
Add this to the Detail shelf, then add a dotted reference line based off the average of this field
Add a further reference ‘line’, but select distribution and choose the standard deviation option. Set the colour of the band to pale grey.
Tidy up by hiding the axis, and the header pills, removing all gridlines and row/column dividers and not showing the tooltip.
Creating the Profit bar chart
Simply duplicate the above sheet and change the Metric filter from Sales to Profit.
Creating the Sales Line chart
On a new sheet, add Order Date at the week level as a discrete (blue) pill to Columns and Metric Value to Rows. Add Metric to Filter and select Sales.
Create a new field
Filter – Timeframe – Line
[Order Date]>= DATEADD(‘week’, -12, [pSelectedWeek]) AND [Order Date]<DATEADD(‘week’,1,[pSelectedWeek])
OR
[Order Date]>= DATEADD(‘week’, -13-51, [pSelectedWeek]) AND [Order Date]<DATEADD(‘week’, -51,[pSelectedWeek])
Add to the Filter shelf and select True. Add Order Date at the Year level to Colour and adjust to suit. Re-order the years so the latest year is on top.
Add Value – PY Last 13 Weeks Avg to Detail then add a reference line which uses this fiel d
to show a dashed line
Add a further reference line ‘band’ to display the Standard Deviation as before
Create a new field
This Week Value
IF DATETRUNC(‘week’, [Order Date]) = [pSelectedWeek] THEN ([Value – Selected Week ]) END
And add to Rows to create a second marks card. Change the Mark to Circle and adjust the size. Remove the fields from the marks shelf.
Crate a new field
Colour – PY Last 13 Weeks
IF MIN([Is Selected Week]) THEN [Metric State – PY Last 13 Weeks] END
Then add to the Colour shelf. Add a dark border to the mark. Once again you’ll need to change the date parameter to see all the various colour options that need to be set.
Change the aggregation of the This Week Value mark to AVG then make the chart dual axis and synchronise the axis. Hide the null indicator.
Tidy up by hiding all axis, removing all gridlines, row & column dividers and hiding the tooltip.
Create the Profit line chart by duplicating this sheet and changing the Metricfilter to Profit.
Creating the Dashboard
You need to use containers to arrange the items on the dashboard. I started with a floating vertical container positioned at 0,0 and height and width as per the requirements, then added ‘tiled’ (aka non floating) objects within. Any instances of an actual Tiled container automatically added, I removed from the dashboard. This technique gives much more control over object placement. I used horizontal containers when I needed to display the bar and line charts side by side. I just used a text field to write the chart legends, and a blank object with a coloured background to create the line dividers. Padding is your friend in this case. My object hierarchy is below
Kyle provided this week’s #WOW2026 challenge demonstrating the concept of a portal to embed Tableau dashboards. He curated his own data set for this.
Building the chart selector
After downloading and connecting to the dataset, add Tag, and Viz Title to Rows. Change the mark type to Gantt. The double click into the space beneath the marks card and manually type MIN(1), then add this to the Size shelf. Adjust the chart area to make it wider and taller
Drag the slider on the Size shelf to maximum, then add Tag to Colour and adjust accordingly. Add a white border to the marks (via the Colour shelf). Add Viz Title to Label and align centrally and colour the text white bold.
Hide the Viz Title pill in the Rows (uncheck show header). Right click on the the text ‘Bars’ in the Tag row header, and select Rotate Label.
Right-Click on the word Tag in the viz, and select Hide field labels for rows. Then right click on the word ‘Bars’ again and select format. Adjust the header shading and font style (white).
Hide the Tooltip.
Create fields
True
TRUE
and
False
FALSE
And add to the Detail shelf, along with the Viz Link field.
Building the dashboard
On a dashboard, add the Selector sheet and then place a Web Page object next to it. When the ‘Add URL# box appears, just close it.
Then add a Go to URL dashboard action
Set URL
On select of the Selector sheet, target the Web Page Object, setting the URL to use the Viz Link field (add via the Insert option) and appending &:embed=yes&:showVizHome=no to the string
Then click on one of the links in the Selector viz and the dashboard should display.
To stop the selected item from being ‘highlighted’ in the list, add a Filter dashboard action
Deselect Marks
On select of the Selector viz on the dashboard, target the Selector sheet directly using the selected fields of true = false
Finalise the dashboard by removing unnecessary content, adding a title and formatting/styling as required.
The main focus of the challenge I set this week, was to provide a reference example for the undocumented custom number format option of ‘scale’. which automagically determines whether the number should be formatted to millions (M) or thousands (k) etc. This revelation was shared via a blog post by Roxanne Agerone
To make the challenge a little more interesting, I included some expand/collapse capability. This is driven by a parameter
pSegment
string parameter, defaulted to <empty string>
The display shows a + or – aganst the Segment depending whether it is collapsed or expanded. I created a new field for this
The Sub-Category should only display for the selected Segment; to handle this create
Display
IF [Segment] = [pSegment] THEN [Sub-Category]ELSE ” END
On a new sheet, add Segment Display and Display to Rows and Sales to Columns. Show the pSegment parameter.
type in ‘Corporate’ into the pSegment field and that will expand, but notice, we’ve lost the ‘total’ for the Corporate value
Let’s start to resolve this. Firstly add Segment to Colour and adjust to suit. The apply a Sort to the Segment Display field, so it sorts by Sales descending
To get the Corporate Total to display, select Analysis > Totals > Add all Subtotals from the menu, and then from the same menu, select Column Totals to Top
While this has given what we need for Corporate, we now have duplicate rows for the unexpanded Segments. One of these rows has the word ‘Total’ in the Display column, while the other is blank. The trick here is just to ‘hide’ the rows where Display = blank. Simply click on the cell with the blank, right click and select hide.
This will remove these fields from the display (to get them back select Analysis > Reveal Hidden Data from the menu).
Now finalise by, applying a sort to the Display field to also sort by Sales descending.
Remove the Total label, by right clicking on the cell > format and deleting the text from the Totals > Label field
Make the rows a little wider, then add Sales to Label. Apply a custom number format to the field of “$”0.0[scale] which is the secret sauce to this challenge!
Apply formatting as required – I added row dividers, removed gridlines, adjusted the colour of text etc, and removed axis and column headers. I also adjusted the Tooltip text.
Finally, to ensure the dashboard interactivity behaved as I wanted without fields being highlighted on click, I created the following feilds
False
FALSE
Segment for Param
IIF([pSegment]=[Segment],”,[Segment])
to allow the display to reset.
Add both of these to the Detail shelf
Add the sheet to a dashboard, then create the following dashboard actions
Set Segment
Change Parameter dashboard action that on Select of the viz, sets the pSegment parameter with the value from the Segment for Param field.
Highlight
Highlight dashboard action, that on select of the viz, targets itself, highlighting just the False field (as this exists on every mark, everything is highlighted, which means it doesn’t look highlighted 🙂 )
And apart from any other styling you might apply, that should be the crux of the challenge. My published viz is here.
For #WOW2026 Week36, Sean challenged us to use map layers to recreate the famous John Snow cholera map from 1854. He provided 3 csv data sets that I downloaded.
Despite there being 3 data sets, and only 1 sheet to build, this challenge did not require any data modelling to combine the data sets in any way. I just added each csv as it’s own data source, so my workbook had 3 data sources
Deaths by Building
Pump Locations
Sewergrate Locations
The story is driven by a parameter
pScroll
integer parameter with a list of values from 1 to 4, defaulted to 1 (Homes and Buildings), where each number has a text display
Each data set has a Latitude and Longitude field, which is key to creating the points we want to display on the map.
Building the 1st story point/map layer
In the Deaths by Building data source, create a new field
Double click on this field, and a map with the building points will automatically be created. Change the mark type to Circle, add ID to Detail, change the colour of the circle as desired, adding a border. Increase the mark size. Adjust Tooltip as required.
To add more detail to the map background, click Map > Background Layers from the menu and choose Style = Light, and then remove all options except Base and Streets, Highways/Motorways
Building the 2nd story point/map layer
If it’s not already visible, show the pScroll parameter.
Still in the Deaths by Building data source, create another field
Change the pScroll parameter to 2-Deaths – you’ll momentarily lose the map details.
Drag Building Location (Deaths) onto the canvas and drop it when the Add a Marks Layer option appears.
Your map will reappear as a second marks layer.
Change the mark type to Circle, add ID to Detail. Add Deaths to Size, then adjust the size as required. Add Deaths to Colour and adjust the colour palette as required. Add a darker border to the marks. Adjust Tooltip.
If you change the pScroll parameter to the 1st option, you should now see the original set of marks built as the 1st map layer. Change to any other option and you’ll see the above set of marks.
Building the 3rd story point/map layer
Set the pScroll parameter to option 3 (Sewerage vents).
In the Sewergate locations data source, create the field
and then drag onto the canvas. Add ID to Detail, change the mark type to circle, adjust the colour and size and add a dark border to the mark. Then move the Sewer Location marks card so it sits between the Building Location and Building Location (Deaths) marks card. This places the sewer marks beneath the Building Deaths marks. Adjust Tooltip.
Building the 4th story point/map layer
Set the pScroll parameter to option 4 (Pumps).
In the Pump locations data source, create the field
and then drag onto the canvas. Add ID and Name to Detail, change the mark type to shape. Create a new field
Is Broad Street Pump
[Name] = ‘Broad St Pump’
and add this to the Shape shelf, and then adjust the shapes. Add the same field to the Colour shelf too, and adjust. Increase the size. Adjust Tooltip.
Managing the story text
Initially I used a calcluated field to define the text based on an IF statement related to the pScroll parameter, and then added this field to the view, so I could make it the title of the sheet. But the spacing was just completely off. So instead, I add the map to a dashboard along with 4 text fields. Each text field had the relevant story point text within it. I then used dynamic zone visibility to control which text box was displayed when. For this I created 4 calculated fields
Step 1
[pScroll] = 1
Step 2
[pScroll] = 2
Step 3
[pScroll] = 3
Step 4
[pScroll] = 4
and then referred to these in the control visibility using value option on the dashboard layout section
For this week’s challenge, Lorna wanted to continue showcasing the ability to now use published data sources in Tableau Public, so this week was all about online authoring, something I rarely (if ever) do.
Connecting to the data source
From the link in the challenge, you need to select the Historical Tropical Cyclones data source, and then I selected Create Viz to start the build
Creating the Calculated field
We need to categorise the storms using the info in the challenge requirements, so create
Tropical Storm System
CASE [Usa Sshs] WHEN -5 THEN ‘Unknown’ WHEN -4 THEN ‘Post-tropical’ WHEN -3 THEN ‘Miscellaneous disturbances’ WHEN -2 THEN ‘Subtropicakl’ WHEN -1 THEN ‘Tropical depression’ WHEN 0 THEN ‘Tropical storm’ WHEN 1 THEN ‘Category 1’ WHEN 2 THEN ‘Category 2’ WHEN 3 THEN ‘Category 3’ WHEN 4 THEN ‘Category 4’ WHEN 5 THEN ‘Category 5’ END
We also need to identify the start date of each storm. For this I first converted the Iso Time field into a datetime
ISO Datetime
DATETIME([Iso Time])
and then used a FIXED LOD to determine the minimum date per storm and season
I then used this field to capture just the month per date, which is needed for the filter control.
Month, Year
DATE(DATETRUNC(‘month’,[Start Date per Storm]))
To build the map, I decided to use map layers, so created a loation point for the latitude and longitude fields provided
Point
MAKEPOINT([LAT],[LON])
Finally for the bar chart, we need a count of storms per category within the selected timeframe
#No of Storms
COUNTD([ISO Datetime])
Building the bar chart
Set Season (Year) to be a discrete dimension, then on a new sheet, add Season(Year) to filter and select 2026 from the list presented.
Then add Month,Year to Filter, select the ‘blue’ Month / Year option (middle of the list displayed)…
.. then select Select from list from the List dropdown, and select just June 2026 and July 2026
Once both fields are on the Filter shelf, set them so they both apply to all worksheets using the data source, as the same filters will be needed when the map is built.
Add Tropical Storm System to Rows and #No of Storms to Columns. Sort Tropical Storm System by the minimum of the Usa Sshs field ascending
Change the mark type to a bar and add Tropical Storm System to Colour, adjusting colour as required.
Double click into Columns and manually type MIN(1) to create a secondary axis. Move Tropical Storm System from the Colour shelf to the Label shelf on this marks cark. Change the mark type to Gantt Bar, reduce the Size and set the opacity on the colour shelf to 0.
Edit the Min(1) axis to start at 0 and end at 1. Then set the chart to dual axis (but DO NOT synchronise the axis). Set the chart to fit entire view. Hide both axis and the header column (uncheck show header)
Adjust Tooltip. The format via the Format > Worksheet menu option. Set background of worksheet to black, remove row & column dividers, turn off all gridlines, zero lines
Building the Map
On a new sheet, double click Point which should automatically generate a map, with the filters already applied.
Change the mark type to circle and add Name and ISO Datetime as a discrete exact date (blue pill) to Detail. Add Usa Wind (Kts) to Size and Tropical Storm System to Colour. Apply a sort to the Tropical Storm System field, this time to sort by Usa Sshs field descending. Adjust Tooltip to suit.
Set the background colour of the map to dark (Map > backgroundmaps menu option). Remove the halo from the marks (via the Colour shelf).
Now drag another instance of Point towards the top of the canvas, and drop when the add a marks layer option appears,to create a 2nd marks card (Point 2). Change this mark type to line and move the marks card to be beneath the one labelled Point.
Add Name to Detail and ISO Datetime as a continuous exact date (green pill) to Path. Duplicate the Usa Wind (Kts) pill to create a ‘copy’ version, and add this to Size. Having a second instance, allows the size on the 2 marks cards to vary. Adjust the size to suit so it is larger than the circle marks, and change the Colour to white. Add Tropical Storm System to Tooltip then adjust tooltip.
Add SID to the Detail shelf on both marks cards too. Format this worksheet too, to remove any row/column dividers etc.
Add both the sheets to a dashboard, using layout containers to arrange the objects. Format the dashboard to have a dark background too.
Kyle set this week’s challenge focusing on visualising the effectiveness of a product launch. This type of chart I sometimes refer to as a ‘rocket chart’. We’ll build this as two separate charts.
Build the current time view
Build the basic view by adding Date to Columns, as a continuous (green) pill adjusted to the Week Number level, and Daily Orders to Rows. If need be, adjust the Date Property of the data source so the week starts on a Sunday (right click data source > Date Properties). Doing this will ensure the data aligns with the solution. Add Product to Colour (and adjust) and add Product to Label, setting the label to match mark colour.
Kyle wasn’t explicit about the current timeframe, but it seems essentially the concept is to show the last year / last 52 weeks. Add Date to Filter as a relative date filter and set to last 52 weeks, and check the Anchor relative to box, and enter the date 15 Aug 2026 (this is necessary to ensure the viz is static – it’s essentially fixing ‘today’ to 15 Aug 2026).
Adjust the Tooltip to suit, remove the axis titles, and hide all the gridlines. Rename the sheet appropriately.
This gives us a normalised position for each product. Add Weeks Since Launch as a continuous dimension (green dis-aggregated pill) and add Daily Orders to Rows. Again add Product to Colour and Text.
We only want to show the 1st 52 weeks, so create
Filter Weeks
[Weeks Since Launch]<=52
and add to the Filter shelf and set to True.
Add Week of Date to Tooltip and adjust. Remove the y-axis title, hide the x-axis completely (uncheck show header), and hide all the gridlines. Rename the sheet appropriately.
Apply the sheet-swap
Create a parameter
pView
integer parameter displaying aliased values as below
Then create fields
Show Common Start
[pView] = 2
Show Current View
[pView] = 1
Add the 2 sheets to a dashboard. Click the ‘current view’ object, and on the Layout tab set the Control Visibility using value checkbox, and select the Show Current View field
Repeat, by clicking on the ‘Common Start’ object and selecting the Show Common Start option in the Control visibility option.
Tidy up the dashboard as desired and that should be it.
Lorna ‘set’ this week’s challenge based on sets … see what I did there … 🙂 Let’s jump straight it…
Create the sets
We’re going to use the ‘out of the box’ functionality for this (ie right click dimension -> Create -> Set) to create various sets.
Right click State/Province to create set
Top 5 States
Select the Top tab, and adjust fields as below
Repeat to create
Top 1 State
This time, change the number from 5 to 1
Repeat process to create
Bottom 5 States
In the create set dialog, select ‘Bottom’ from the drop down rather than Top, and ensure number is set to 5
Repeat to create
Bottom 1 State
setting the number to 1
Then do a similar process but right click on City to create sets Top 5 Cities and Bottom 5 Cities
Creating the calculations to style the marks
The marks on the map are coloured, shaped, and sized based on the logic associated to these sets, so let’s create the various calcs needed for this
Colour – State
IF [Top 1 State] THEN ‘dark blue’ ELSEIF [Top 5 States] THEN ‘blue’ ELSEIF [Bottom 1 State] THEN ‘dark orange’ ELSEIF [Bottom 5 States] THEN ‘orange’ ELSE ‘grey’ END
Colour – City
IF [Top 5 Cities] THEN ‘dark’ ELSEIF [Bottom 5 Cities] THEN ‘mid’ ELSE ‘light’ END
Size
IF [Top 5 Cities] THEN ‘big’ ELSEIF [Bottom 5 Cities] THEN ‘mid’ ELSE ‘small’ END
Shape
IF [Top 5 Cities] THEN ‘star’ ELSEIF [Bottom 5 Cities] THEN ‘cross’ ELSE ‘circle’ END
Creating the calculations for the Tooltip
The tooltip needs to show the counts of various city related measures, so to start I worked out the number of Cities
Count Cities
COUNTD([City])
then created
Top Cities in State
COUNTD(IIF([Top 5 Cities], [City],NULL))
and
Bottom Cities in State
COUNTD(IIF([Bottom 5 Cities], [City],NULL))
and with all these I could then create
Other Cities in State
[Count Cities] – ([Top Cities in State] + [Bottom Cities in State])
Now we have all this, we can build the viz
Building the Map
Double click State/Province and a map should automagically get created. Change the mark type to Map, then add Country/Region to Detail to make all the states filled.
Add Colour – State to the Colour shelf and adjust to suit. Add Sales, Top Cities in State, Bottom Cities in State and Other Cities in State to Tooltip and adjust as required.
Now drag City onto the canvas and drop when you see the Add a Marks Layer option
This will create another marks card.
Add State/Province and Country/Region to Detail and change the mark type to Shape.
Add Colour – City to the Colour shelf and Shape to the Shape shelf and adjust both as required.
The add Size to the Size shelf and adjust the size by setting it to reversed and adjusting the Size slider if need be.
Then add Sales to the Tooltip and then adjust the Tooltip as needed.
Finally, format the map
remove row/column dividers
Go to Map menu > background layers and then uncheck all options
Go to Map menu > map options and uncheck all options.
And that should be enough – just add the map to a dashboard and publish.
It was my turn to set the #WOW2026 Week 30 challenge, and I based it on a viz I started building some time ago, but never finalised. Given Wimbledon has recently finished, I thought it was the perfect opportunity to get this one over the line. There are multiple components to this, so I split the challenge into ‘Levels’ to help people focus on what to work on, if time was limited.
Defining the core calcs
The data provided contains 1 row per match Sinner has played in, and each match is associated to a tournament. While typically matches in a tournament are played over several days, in the dataset the Date field is the same for all matches in a single tournament, so can be used a a unique identifier for a tournament.
The ladder chart is essentially a bump chart where each round in a tournament is ‘ranked’ based on how far into the tournament the round is, where the Final is the highest rank ie 1. So the first step is to assign a ‘rank’/position to each round.
Round Position
CASE [Round] WHEN ‘F’ THEN 1 WHEN ‘SF’ THEN 2 WHEN ‘QF’ THEN 3 WHEN ‘R16’ THEN 4 WHEN ‘R32’ THEN 5 WHEN ‘R64’ THEN 6 WHEN ‘R128’ THEN 7 WHEN ‘Q3’ THEN 8 WHEN ‘Q2’ THEN 9 WHEN ‘Q1’ THEN 10 WHEN ‘RR’ THEN 11 END
We then need to be able to identify the best round achieved in the tournament
Best Round per Date
{FIXED [Date]: MIN([Round Position])}
We also need to identify the Grand Slam tournaments from other tournaments. I’ve used a set for this. Right click on Tournament > create > set
Is Grand Slam
select the 4 entries : Australian Open, Roland Garros, Wimbledon, US Open
I’ve also created a parameter
pGrandSlamsOnly
boolean parameter, defaulted to True, with the True option aliased as Grand Slams Only and False aliased as All Tournaments
I then created
Filter – Matches
([pGrandSlamsOnly] AND [Is Grand Slam]) OR NOT([pGrandSlamsOnly])
This will return true if either pGrandSlamsOnly = Grand Slams Only (ie is True) AND the tournament is a member of the Is Grand Slam set, or pGrandSlamsOnly = All Tournaments (ie is False)
To ensure only the best round per tournament is shown, I also created
Keep Best Round Only
[Round Position] = [Best Round per Date]
Finally, we need to identify if Sinner was the winner of the match or not. We can determine this by looking for the name Sinner in the text of the Match field which precedes the string ‘d. ‘ . (I assume d. stands for ‘defeated’).
Is Winner
CONTAINS(TRIM(SPLIT([Match], “d.”, 1 )),”Sinner”)
Building the ladder chart
On a new sheet, add Round to Filter and exclude RR. Add Keep Best Round Only to Filter and set to True. Add Filter – Matches to Filter and set to True. Then finally add Surface to Filter and select all options.
Add Date to Columns as a continuous (greed) exact date, and add Round Position as a continuous dimension (unaggregated green pill) to Rows. Edit the y-axis and reverse the axis. Change the mark type to a line and set the path to be a stepped line.
Add Tournament, Rank, Date, Surface and Round to the Tooltip shelf. Adjust the number format of the Rank field so it is a number with 0dp but prefixed with #. Adjust the format of the Date field on the Tooltip to be mmmm yyyy (ie August 2019 format).
Additionally, create a field
Label:Unranked
IIF(ISNULL([Rank]),’Unranked’,NULL)
and add this to Tooltip too, the adjust tooltip as required. Reduce the Size of the line and adjust the colour (I used #7ed0d9). Set the background colour of the worksheet too (I used #f7fcf5)
Add another instance of Round Position to the Rows to make a 2nd marks card. Set the mark type to Shape and add Is Winner to Shape. Increase the mark size. Set shapes accordingly. Add Surface to Colour and adjust to suit (I used colours from the hue circle palette, reduced to 60% opacity). Then make the chart dual axis and synchronise axis.
Add Is Winner to the Size shelf of the 2nd marks card, and adjust sizes to suit too, so the Star is at least as big as the other mark.
Remove all row/column dividers, gridlines, zero lines, axis lines etc.
To make the labelled ‘ladder rungs’, we’re simply adding multiple constant reference lines. Right click the left hand y-axis > add reference line. Add a constant at 10, labelled Q1 with a blue dotted line.
Then format the label so it is aligned in the middle of the line (I reduced font to 8pt) and removed any shading
Then repeat 9 more times for each ‘rung’ 🙂
At the end, hide both the axis (uncheck show header) and remove the x-axis title. Fix the x-axis from 01 Aug 2017 to 01 Jun 2027. The test the filters work as expected.
Building the Viz in Tooltip
On a new sheet, add Date as a discrete exact date (blue pill), Round, Match to Rows. Go back to the Ladder chart and set the Round, Filter – Matches and Surface filters to apply to this worksheet too. Apply a sort to the Round pill, to sort by the Round Position field.
Add Score to the Text shelf. Then double click into Columns and type the text ‘Result’, then right click the ‘Result’ text and hide labels for columns.
Go back to the Ladder chart and update the Tooltip – insert the VIT sheet, and adjust the size /width as required and apply filters to Date & Tournament.
On hover, the list of matches should now display
Building the KPIs
We need a few fields for this
Tournaments
COUNTD([Date])
Matches
COUNT([Sinner])
Note – this is just referring to the automatic sheet row count field
Wins
IIF([Is Winner?],1,0)
Titles
IIF([Is Winner?] AND [Keep Best Round Only],1,0)
Grand Slams
IIF([Is Grand Slam] AND [Is Winner?] AND [Keep Best Round Only],1,0)
On a new sheet, Add Measure Names to Columns and Measure Values to Rows. Remove the measures that aren’t required. Apply the same ‘shared’ filters to this sheet.
Change the mark type to a shape and use a transparent shape. Add Measure Names to Label and arrange labels middle centre, and format as required. Set the worksheet background to match the ladder viz.
Building the strip plot
Create a new field
Within Top 10
[Rank]<=10
On a new sheet, add this to Filter and set to True. Then apply the other 3 shared filters to this sheet.
Add Date to Columns as a continuous exact date (green pill). Fix the axis to the same dates as before 01 Aug 2017 to 01 Jun 2027. Set the Mark type to Circle and add Rank to colour, reversing the range as required so the lower number is darker. Set the circle to have a border and 80% opacity. Add Date, Surface, Tournament and Rank to Tooltip and adjust as required. Remove all gridlines, row/column dividers, gridlines etc and set background colour as before.
The double click into Rows and type MIN(0) to create a y-axis. Then add a 0 constant reference line (as before) but label this Within Top 10. The hide both the x and y-axes.
Building the dashboard
Now you have all the components, they just need to be added to a dashboard. Use layout containers to help with this. To ensure the ladder chart & the strip plot align, arrange these in a vertical container. Both objects should be set to fit entire view and any padding to the left or right of the objects should be identical.
This week’s challenge was a guest post by Lisa Hitch to generate a pareto chart, but adding visual clues via background colours and lines to highlight the key areas of focus.
Ultimately, this was a relatively simple table calculation challenge, requiring just 2 calculations.
Just to see what these are doing, add Customer ID into Rows and Sales onto Text. Sort by Sales descending (this is a key feature in getting the pareto chape we need).
Create another field
cust count
COUNTD([Customer ID])
and add this into the table (we ultimately don’t need this field, but just creating so it’s clear what’s happening.
Add a Running Total quick table calculation to the cust count field – the field will now show a cumulative total down the table
Also create a field
total cust
TOTAL(COUNTD([Customer ID]))
Add this to the table too – every row reports the same – the total number of distinct customers int he data set.
Now adding Running Sum % of Customers into the table, you can see that the result of this field is a calculation of cust count and total cust. We could have broken it down like this, but I chose to just use a single calculation.
We could apply similar principals to the Sales field so you can see how this is derived, but hopefully you get the gist. Ultimatel, the data we need is below
On a new sheet, add Running Sum % of Sales to Rows, Running Sum % of Customers to Columns and Customer ID to Detail. Adjust both the table calculation to be explicitly computing by Customer Id and apply a sort on the Customer ID pill, to be sorted by Sales descending.
Change the mark type to Area and adjust the colour and set opacity to 100%.
To set the background colours, we’ll used fixed reference bands.
Right click the X-axis and Add Reference Line. Select Band and then set a constant from 0 to 0.1 and colour as required.
Repeat the process 4 more times, creating bands for 01-0.2, 0.2-0.8, 0.8-0.9 and 0.9-1.
The add reference lines to provide the 20% & 80% intersection lines. This time, select the Line option and again use constants, but rather than fill, select a thick white line
Again repeat to create a line at 0.8 on the X-axis. Then add reference lines to the Y-axis at the same intervals.
Add an annotation to the point where the X-axis = 20%. Use the tooltip to find the exact point where % of customers = 20% (this can be a bit fiddly), then right click and Annotate > Mark. Edit the annotation as required, then format the resulting text box as required. Repeat for the point at which the Y-axis = 80%.
Finish off by hiding all tooltips, removing all gridlines & row/column dividers, and fixing both axes from 0-1. Then add to a dashboard and you’re done.
