Kyle set this challenge while I was on my holidays. And because I was away and short on time, I’m cheating a bit this week. I’m not going to provide a full solution guide, but am writing this blog to maintain consistency 🙂
The challenge was very specific to just the new dynamic spatial parameters feature, which is well documented. I used these two resources to familiarise myself with the feature and to build the challenge
As the Paris 2024 Olympic Games continues, then #WOW continues with another Olympic themed challenge, this time set by Kyle.
Defining the core calculations
There’s a lot of LoD calcs used in this, so let’s set this up to start with.
Avg Age Per Sport
INT({FIXED [Sport]: AVG([Age])})
Avg Age Per Sport– Gold
INT({FIXED [Sport]: AVG(IF [Medal]=’Gold’ THEN [Age] END)})
Avg Age Per Sport– Silver
INT({FIXED [Sport]: AVG(IF [Medal]=’Silver’ THEN [Age] END)})
Avg Age Per Sport– Bronze
INT({FIXED [Sport]: AVG(IF [Medal]=’Bronze’ THEN [Age] END)})
Oldest Age Per Sport
INT({FIXED [Sport]: MAX([Age])})
Youngest Age Per Sport
INT({FIXED [Sport]: MIN([Age])})
Format all these fields to use Number Standard format so just whole numbers are displayed.
Let’s put all these out in a table – add Sport to Rows and then add all these measures
Kyle hinted that while the display looks like a trellis chart, it’s been built with multiple sheets – 1 per row. So we need to identify which row each set of sports is associated to, given there are 5 sports per row.
Row
INT((INDEX()-1)/5)
Make this discrete, and add this to Rows, and then adjust the table calculation so that it is explicitly computing by Sport, and we can see how each set of sports is ‘grouped’ on the same row.
Building the core viz
The viz is essentially a stacked bar chart of number of medallists by Age. But the bars are segmented by each individual medallist, and uniquely identified based on Name, Medal, Sport, Event, Year.
Add Sport to Columns, Age to Columns (as a continuous dimension – green pill) and Summer Olympic Medallists (Count) to Rows.
Change the mark type to Bar and reduce the Size a bit. Add Year, Event and Name to the Detail shelf, then add Medal to the Colour shelf and adjust accordingly. Re-order the colour legend, so it’s listed Bronze, Silver , Gold.
For the Tooltip we need to colour the type of medal, so need to create
Tooltip: Bronze
UPPER(IF [Medal] = ‘Bronze’ THEN [Medal] END)
Tooltip: Silver
UPPER(IF [Medal] = ‘Silver’ THEN [Medal] END)
Tooltip: Gold
UPPER(IF [Medal] = ‘Gold’ THEN [Medal] END)
Add all these to the Tooltip shelf, then adjust the Tooltip accordingly, referencing the 3 fields above and colouring the text.
The summary information for each sport displayed above each bar chart, is simply utilising the ‘header’ section of the display. We need to craft dedicated fields to display the text we need (note the spaces)
Heading – Medal Age
“Avg Medalling Age: ” + STR([Avg Age Per Sport])
Heading: Age per Medal
“Gold: ” + STR(IFNULL([Avg Age Per Sport – Gold],0)) + ” Silver: ” + STR(IFNULL([Avg Age Per Sport – Silver],0)) + ” Bronze: ” + STR(IFNULL([Avg Age Per Sport – Bronze],0))
Heading: Young | Old
“Youngest: ” + STR([Youngest Age Per Sport]) + ” Oldest: ” + STR([Oldest Age Per Sport])
Add each of these to Rows in the relevant order. Widen each column and reduce the height of each header row if need be
Adjust the font style of all the 4 fields in the header. I used
Sport – Tableau Medium 14pt, black, bold
Heading – Medal Age – Tableau Book 12pt, black, bold
Heading: Age per Medal – Tableau Book 10pt, black bold
Heading: Young | Old – Tableau Book 9pt, black
The title needs to reflect the number of medallists above a user defined age. To capture the age we need a parameter
pAge
integer parameter defaulted to 42
Then we can create
Total Athletes at Age+
{FIXED:COUNTD( IF [Age]>= [pAge] THEN [Name] END)}
Add this to the Detail shelf, then update the title of the viz to reference this field. Note the spacing to incorporate the positioning of the parameter later.
The chart also needs to show background banding based on the selected age. Add a reference line to the Age axis (right click axis > add reference line), which references the pAge parameter, and fills above the point with a grey colour
Ultimately this viz is going to be broken up into separate rows, but we need the Age axis to cover the same range. To ensure this happens, we need
Ref Line – Oldest Age
{FIXED: MAX([Oldest Age Per Sport])}
Add this to the Detail shelf, then add a reference line which references this field, but doesn’t actually ‘display’ anything (it’s invisible).
Tidy up the visual – remove gridlines, zero lines, row & column dividers. Hide the y-axis (uncheck show header). Remove the x-axis title. Hide the heading label (right click > hide field labels for columns). Hide the null indicator.
Filtering per row
Add the Row field to the Filter shelf and select the only option displayed (0). Then edit the table calculation and select all the fields to compute by, but set the level to Sport.
Show the Row filter and select the 0 option only. – only the first 5 sports are now displayed.
Name this sheet Row1. Then duplicate the sheet, set the Row filter to 1 and name the sheet Row2. Repeat so you have 7 sheets.
Building the dashboard
Use vertical containers to help position each of the 7 rows on the dashboard. For 6 of the sheets, the title needs to be hidden. I used a vertical container nested within another vertical container to contain the 6 sheets with no title. I then fixed the height of this ‘nested’ container and ‘distributed” the contents evenly. But I did have to do some maths based on the space the other objects (title & footer) used up on my dashboard, to work out what the height should be.
You’ll then need to float the pAge parameter onto your layout, and may find that after publishing to Tableau Public, you’ll need to edit online to tweak the positioning further.
Week 32 of #WOW2022 and Kyle set this challenge to build a dumbbell chart (also sometimes referred to as a barbell chart, a gap chart, a connected dot plot, a DNA chart). I first built one of these a long time ago, and have built many since, so chose not to refer to Ryan Sleeper’s blog Kyle so generously provided. I figured I’d have a go myself from memory. Looking back now, I actually started by going down the same route a Ryan, but then switched my approach in order to handle the tooltip requirements.
So here’s what I did.
Defining the calculations required
I’m going to start with these, as it’s then easier just to explain how everything then gets built. In reality, some of these calculations came along mid-build.
I decided to create explicit fields to store the Ownership values for the 2015 & 2019 years ie
Ownership 2015
IF [Year]=2015 THEN [Ownership] END
Ownership 2019
IF [Year]=2019 THEN [Ownership] END
Both these fields I formatted to % with 0dp.
Given these, I can then work out the difference
Difference
SUM([Ownership 2019])-SUM([Ownership 2015])
also formatted to % with 0dp
and finally, I can work out whether the change exceeds 20% or not
Difference>0.2
[Difference]>0.2
These are all the fields required 🙂
Building the Dot Plot
Add Age to Columns as a discrete dimension (blue pill) and Ownership 2015 to Rows. This will create a bar chart.
Then drag Ownership 2019 onto the canvas, and release your mouse at the point it is over the Ownership 2015 axis and the 2 green columns symbol appears.
This will have the effect of adding Measure Names and Measure Values to the view.
Change the mark type to Circle and move Measure Names from Columns onto Colour. Adjust colours accordingly.
Show mark labels and adjust so they are formatted middle centre, and are bold. Increase the size of the circles so the label text is completely within the circle, and the font colour should automatically adjust to white on the darker circles and black on the lighter ones.
Add Ownership 2015, Ownership 2019 and Difference onto the Tooltip shelf, and adjust the tooltip to suit. Note – I chose to adjust the wording of the tooltip to not assume there was always an increase. I used the word ‘change’ instead and as a result applied the custom formatting of â–²0%;â–¼0% to the Difference field that was on the Tooltip shelf.
So my tooltip was
which generated a tooltip that looked like
Connecting the dots
Add another instance of Measure Values to Rows alongside the existing one. This will generate a 2nd Measure Values marks card.
On that marks card, remove Measure Names from Colour and add Difference>0.2 to the Colour shelf instead, and adjust the colours.
Change the mark type to Line and add Measure Names to the Path shelf.
Move the Difference pill from the Tooltip shelf to the Label shelf. Edit the label controls, so the label only displays at the start of the line, which should make the label only show at the bottom. Adjust the label alignment so it is bottom centre.
Delete all the text from the Tooltip of the ‘line’ marks card.
Now make the chart dual axis and synchronise the axis. You’ll notice the bars sit on top of the circles, and the difference text is not displaying under the circles.
To remedy this, first, right click on the right hand axis, and select move marks to back. The lines should now be behind the circles.
Then, on the lines marks card, edit the Label text and just add a single carriage return in the dialog box, so the text is shifted down.
Adding Age to be displayed at the top
The quickest way to do this is to double click in the space on the Columns shelf, next to the Age pill, and type in ‘Dummy’ or just ” if you prefer. This has the affect of adding another ‘dimension’ into the view and as a result the Age values immediately get moved to the top.
Now just remove all axis, all gridlines, all row & column dividers. Then uncheck show header against the Dummy pill and hide field labels for columns for the Age header (right click on the Age label in the view itself to get this option, not the pill). Then format the Age text to be a bit bigger and bolder, and also increase the Size of the lines mark type. And that’s your viz 🙂
Additional Note
If you had followed Ryan’s blog post, then you would still have needed to create separate calculated fields to store the ownership values for 2015 and 2019, but these would have had to been LOD fields eg
Ownership 2015
{FIXED [Age]:SUM(IF [Year]=2015 THEN [Ownership] END)}
and a similar one for Ownership 2019. The other 2 fields related to the difference would then need to reference these instead.
This week, Ann Jackson set a table calculations based challenge, using the responses from a recent survey on #WorkoutWednesday, as the most requested topic was for table calcs!
There’s a lot of visuals going on in this challenge, and I’m shortly off on my holibobs (so will be playing catch up in a couple of weeks), so I’m going to try to pare down this write up and attempt just to focus on key points for each chart.
Donut Chart
By default when you connect, Respondent is likely to be listed in the ‘Measures’ part of the data pane – towards the bottom. This needs to be dragged into the top half to turn it into a dimension. You can then create
# of Respondents
COUNTD([Respondent])
which is the key field measures are based on throughout this dashboard.
When building donuts, we need to get a handle on the % of total respondents for each track, along with the inverse – the % of total non-respondents for each track. To do this I created fields
Total # of Respondents
TOTAL(COUNTD([Respondent]))
and then
Track – % of Total
[# of Respondents]/([Total # of Respondents])
along with the ‘inverse’ of
Non Track – % of Total
1-[Track – % of Total]
To then build the donut, we ultimately need to create a dual axis chart, with Which track do you participate in? on Columns and a MIN(1) field on Rows. Manually reorder the entries so the tracks are listed in the relevant order.
On the first MIN(1) axis/marks card, build a pie chart. Add Measure Names to the filter shelf and filter to the Track & Non Track % of Total fields. Set Mark Type to Pie Chart and add Measure Values to the angle shelf. Add both Which track do you participate in? and Measure Names to the Colour shelf. Set a white border on the Colour shelf. Reorder the entries in the colour legend, and set the colours appropriately.
The create another MIN(1) field next to the existing one on the Rows shelf
Set this marks type to circle, and remove all the fields from the colour & detail shelves. Set the colour to white. Add Which track do you participate in? and Track – % of Total to the Label shelf and format. Reduce the Size. Make dual axis, and synchronise. Further adjust sizes to suit.
Participation Bar Chart
Plot How often do you participate? against # of Respondents, and then add a Quick Table Calculation to the measure using Percent of Total. Manually re-sort the order of the entries, show mark labels and Colour the bars light grey. Apply relevant formatting.
Diverging Bar Chart
In this bar chart, the percentage of ‘agree’ responses are plotted to the right on the +ve scale and the percentage of the ‘disagree’ responses are plotted to the left on the -ve scale. The percentage of the ‘inbetweeners’ (neither agree nor disagree) is halved, and displayed on both sides. To address this, I created the following:
# of Respondents – Diverging +ve
CASE ATTR([Answer]) WHEN ‘Agree’ THEN [# of Respondents] WHEN ‘Strongly Agree’ THEN [# of Respondents] WHEN ‘Neither Agree nor Disagree’ THEN [# of Respondents]/2 END / [Total # of Respondents]
This is the % of total respondents for the ‘agree’ responses and half of the ‘inbetweeners’.
Similarly I then have
# of Respondents – Diverging -ve
(CASE ATTR([Answer]) WHEN ‘Disagree’ THEN -1*[# of Respondents] WHEN ‘Strongly Disagree’ THEN -1 *[# of Respondents] WHEN ‘Neither Agree nor Disagree’ THEN ([# of Respondents]/2) * -1 END) / [Total # of Respondents]
which is doing similar for the ‘disagree’ responses, except all results are multiple by -1 to make it negative.
The Question field is added to the Filter shelf and the relevant 5 questions are selected. Answer is also on the Filter shelf with the N/A answer excluded.
Add Question to Rows (and manually sort the entries), then add # of Respondents – Diverging -ve and # of Respondents – Diverging +ve to Columns and add Answer to the Colour shelf. Manually resort the entries in the colour legend and adjust the colours accordingly.
Make the chart dual axis, and synchronise the axis. Change the mark type back to bar and remove Measure Names from the colour shelf if it was added. Edit the bottom axis to fix the range from -0.99 to 0.99 and amend the title. Format the axis to display as percentage to 0 dp. Hide the top axis.
Additionally format both the measures to be percentage 0dp, but for the # of Respondents – Diverging -ve custom format, so the negative value is displayed as positive on the tooltip.
Adjust formatting to set row banding, remove gridlines etc and set tooltips.
Vertical bar chart
The best way to start building this chart is to duplicate the diverging one. Then remove both measures from the Columns shelf and add Answer to Columns. Manually re-sort the answers. Add # of Respondents to Rows and add a Quick Table Calculation of percent of total.
Show the marks label, and align bottom centre, and match mark colour. Hide the axis from displaying, and also hide the Question field (uncheck show header). Update the Tooltip.
Heatmap
Right click on the Question field > Aliases and set the alias for the relevant questions
Also add Question to Filter and select relevant values. Add Answer to Filter too and exclude NULL.
Add Question to Columns and add Answer to the Text shelf. Add # of Respondents to the Text shelf, and set to Percent of Total quick table calculation. Edit the table calculation to compute using the Answer field only
We need to get each of these columns ‘sorted’ from high to low – we want to rank them. To do this, add # of Respondents to Rows, then change it to be a blue discrete pill. Add a Rank quick table calculation and once again set to compute by Answer only. Also set the rank to be Unique
Now change the mark type to square, and then add the # of Respondents percent of total field onto Colour as well as Text (the easiest way to do this to retain all the table calc settings, is to hold down Ctrl then click and drag the pill from the Text shelf onto Colour. This should duplicate the pill.
Format the % of total displayed to be 0dp, and adjust the label. Change the Colour to use the purple range and set a white border too. Hide the ‘rank’ field from displaying and hide field labels for columns too.
The dashboard
I used a vertical container then added the objects as required, using nested horizontal containers to organise the side by side charts.
To make the diverging bar and vertical bar charts look like they are one chart, adjust the padding of diverging bar chart object to have 0 to the right, and similarly, adjust the padding of the vertical bar to have 0 padding to the left.
I found it a bit fiddly to get the charts to line up exactly. Both charts were set to fit entire view. The diverging bar chart displays it’s title. I also displayed a title on the vertical bar chart, but made the text white so it’s invisible.
Dashboard filter actions are set against the donut and the participation bar charts.
The filter uses selected fields, which for the donut chart references the Which track do youpartcipate in? field. A similar dashboard action needs creating for the participation chart as the source and references the How often do you partcipate? field.
A highlight dashboard action is required for the diverging and vertical bar charts. They only impact each other and should be set up as below on hover.
Hopefully I’ve covered everything… my published version is here.
This viz is essentially equivalent to a small multiple display where the charts for a specific dimension (in this case State) get displayed across numerous rows and columns. The difference here, is the row and column for the State to be displayed in, is specifically defined, rather than just sequentially based on how the data is being sorted.
Luke very kindly provided the logic to determine the rows and columns.
Building out the data
Once again, I’m going to start by putting all my data into a table so I can check my calculations, especially since this challenge does involve table calculations (there’s a hint on the Latest Challenges page)
Data Source Filter
Although not explicitly mentioned in the requirements, the information we need to present is based on the dates from 1st March 2020 to 31st July 2020. I messaged Luke to check this, before I then realised it was stated in the title of the viz – doh!
To make things easier, I therefore added a data source filter to remove all the other dates, setting the Report Date to range from 01 March 2020 to 31 July 2020 (right click on the data source -> add data source filter
I then added the basic fields I needed to a table
Province State Name to Rows
Report Date discrete, exact date (blue pill) to Rows, custom formatted to mmmm, dd
People Positive New Cases to Text
I excluded the fields where Province State Name = Null
We need to calculate the 7 day rolling average per Province State Name which we can do by using the UI to create a Moving Average quick table calculation against the People PositiveNew Cases pill, and then editing to compute over the previous 6 records (+ the current record makes 7 days). But I want to be able to reference this field, so I’m going to ‘bake it’ into the data model by creating a specific calculated field
7 day moving Avg
WINDOW_AVG(SUM([People Positive New Cases Count]), -6, 0)
Add this into the table, and edit the table calculation to compute by Report Date only and set the Null if not enough values checkbox
Do a basic sense check that the averages are correct by summing up 7 sequential rows and working out the average by dividing by 7.
So it looks like we’ve got the core measure to be plotted, but we’re going to need some additional fields in the presentation.
Again it’s not explicitly stated in the requirements, but it is in the title, that the values plotted need to be normalised. This is to ensure the data for each state is visible; if a state with a relatively low number of cases is positioned in the same row as one with a very high number of cases, it will be hard to see the data for the state with the low cases, because while the axis can be set to be independent, this will only work against a row and not an individual instance of a chart.
To normalise, we need to understand the maximum 7 day rolling average for each state.
Max Avg Per State
WINDOW_MAX([7 day moving Avg])
Add this to the table, setting both the nested table calcs to compute by Report Date.
In normalising, what we’re essentially going to do is determine the 7 day moving Avg as a proportion of the Max Avg Per State, so every value to plot will be on a range of 0-1.
Normalised Value
[7 day moving Avg]/[Max Avg Per State]
Format this to 2 dp, and add to chart, remembering to check the table calcs continue to compute by Report Date only.
Above you can see the max value for Alabama occurred on 19th July, so the Normalised Value to plot is 1
In the chart displayed, each State is titled by the name of the State. In a small multiple grid of rows & columns, we can’t use a dimension field for this, as it won’t appear where we want it. Instead we’re going to achieve this using dual axis, and plotting a mark at the centre point. For this we need to determine the centre date
This looks a bit complex, so I’ll break it down. What we’re doing is finding the number of days between the minimum date in the data set and the maximum date in the data set. This is
We’re then halving this value ( /2) and rounding it down to the nearest whole number (FLOOR).
We then add this number of whole days to the minimum date in the data set (DATEADD), to get our central date – 16 May 2020.
Now we need a point where we can plot a mark against that date. It needs to be above the maximum value in the main chart (which is 1). After a bit of trial and error, I decided 1.75 worked
Plot State
IF [Report Date] = [Centre Date] THEN 1.75 END
Finally we need to create our Rows and Columns fields which provides the co-ordinates to plot each state. The calculations for these were just lifted straight out of the requirements – thanks Luke!
Building the Viz
Start by adding the Rows and Columns fields to their respective shelf. Set them to be discrete dimensions (blue pills). You should immediately see a ‘map’ type layout of the US States.
Exclude the Null Rows value.
Now add Report Date as a continuous exact date (green pill) to Columns and Normalised Value to Rows, remembering to set the table calc to compute by Report Date only for all nested calculations. Change the mark type to Area.
Add 7 day moving Avg to Label and set the label to display the max value only and adjust the font size – I ended up at 7pt. Then add Province State Name & People Positive New Cases Count to Tooltip. Format the tooltip to match.
Remove all column/row lines and grid lines, zero lines etc.
There is a requirement to ‘add a line underneath each of the area trends’.
For this I added a 0 constant reference line formatted to be a solid black line.
But you’ll notice that for the charts that sit directly side by side, the line seems to be continuous, but I want to break it up. I re-added the column divider line to be a thick white line to get the desired effect.
Right, now lets get the State label added.
Add Plot State to Rows before Normalised Value and change the aggregation from SUM to MIN.
Change the Mark Type to Text and move the Province State Name field from Tooltip to the Text shelf. Adjust the text label to remove any other fields that are displaying, and resize the font – again I used 7. Clear the Tooltip for the this mark, so nothing displays on hover.
Make the chart dual axis and synchronise axis. Remove the Measure Names pill from the Colour shelf on both marks cards which will have automatically been added.
And now all you need to do is remove all the headers (uncheck Show Header) against Rows, Columns, Report Date & Plot Value, then right click on the >8k nulls label at the bottom right and select Hide Indicator.
You’re all done – you just need to add to a dashboard now. My published version is here.
I really enjoyed this challenge – a nice mix of calculations & format complexity but not overly cumbersome, which meant this blog didn’t take so many hours to write this week 🙂
Donna + DataViz 