Tag: Heatmap

Can you recreate this marginal histogram?

Donna ColesLeave a comment

For this week’s challenge, Lorna visualised the output of a crazy golf game she had with the latest Data School cohort, and thought it would be a fun challenge to share.

While the data set is very small, there’s several core calculations required, which we’ll start off creating.

Defining the core calculations

After connecting to the data, I initially dragged the Hole field from Measures to Dimensions (dragged it to be above the line on the data pane).

We need to know the average score per person (across all 11 holes)

Overall Average Score

{FIXED [Person]: AVG([Score])}

format this to 1dp

We also need the average score per hole

Avg Score Per Hole

{FIXED [Hole]: AVG([Score])}

format this to 1 dp

We need each person’s score up to 9 holes

Score up to 9

{FIXED [Person]:SUM( IF [Hole]<=9 THEN [Score] END)}

We need the tie break score for the 10th & 11th holes

Tie Break

{FIXED [Person]:SUM( IF [Hole]>9 THEN [Score] ELSE 0 END)}

We need the average score across 9 holes

Avg Score

[Score up to 9]/9

format to 1 dp

We need to the lowest score per person

Lowest

{FIXED [Person]: MIN([Score])}

With this information, we can then define a field we can use for sorting which is a numeric field that is a combination of multiple fields as stated in the requirements

Sort

(SUM([Score up to 9])*10000) +
(SUM([Tie Break]) * 1000) +
(SUM([Avg Score]) * 100)+
SUM([Lowest])

Building the Heat Map

On a new sheet add Score up to 9 to Rows and change it to be discrete. Repeat the same for Tie Break, Avg Score and Lowest. Then add Person to Rows and Hole to Columns.

Double click into Columns and manually type MIN(1.0) to create a ‘fake axis’. Change the mark type to bar and increase the Size to the maximum. Edit the axis to be fixed from 0 to 1 and the hide the axis. Manually increase the width of each row and set the chart to fit width.

Add Score to Colour and choose a diverging colour palette and then manually change the start & end colours to match the solution (or define your own colour style). I used #62a14b (green) and #13316d (dark blue). Check the box to use full colour range

Update the Tooltip as required.

We then need to include some indicators on the heat map. To determine the arrow indicator we first need

Previous Score

LOOKUP(SUM([Score]),-1)

and then create

Change Indicator

IF SUM([Score]) > [Previous Score] THEN ‘↗’
ELSEIF SUM([Score]) < [Previous Score] THEN ‘↘’
ELSEIF SUM([Score]) = [Previous Score] THEN ‘→’
END

(I sourced the arrow characters from https://jrgraphix.net/r/Unicode/2190-21FF)

Add Change Indicator to the Label shelf and then adjust the table calculation so it is computing by the Hole field only

We also need

Hole in 1 indicator

IF SUM([Score]) = 1 AND MIN([Hole])<> 9 THEN ‘●’ END

and

Max Shots Indicator

IF SUM([Score]) = 10 THEN ‘🙁’ END

(I used this site https://unicode-explorer.com/emoji/ for the emoji icon)

Add these to Label too and adjust label so it is aligned bottom centre, and coloured white. You may need to adjust size of font for each label item, and increase the height of each row to get the information to display.

Finalise the formatting by

  • Set the background colour of the whole sheet to #7299aa
  • Set the font of all the column headings and label headings to Tableau Medium, 9pt, white
  • Hide the Hole label heading (right click the label and hide field labels for columns)
  • Add a white border to the bars via the Colour shelf
  • Remove all column dividers
  • Remove row dividers from the pane only, ensure the row dividers for the headers remain.
  • Remove all gridlines , zero lines, axis rulers & ticks

Name the sheet HeatMap or similar.

Building the Average Score Per Person bar chart

On a new sheet, add Person to Rows and Overall Avg Score to Columns. Add a Sort to the Person pill that references the Sort field ascending

Add Overall Avg Score to the Colour shelf, and then adjust the colour scale so it uses a diverging colour scale which is then edited to use the same colour range as before and spans the same range, which means explicitly stating the start (-1) , middle (0) and end (10) values

We want to display a * against the winner

Winner Icon

IF SUM([Overall Avg Score]) = WINDOW_MIN(SUM([Overall Avg Score])) THEN ‘★’ ELSE ” END

Add this to Rows and adjust the table calculation so it is explicitly computing by Person only.

Create

Winner Label

IF SUM([Overall Avg Score]) = WINDOW_MIN(SUM([Overall Avg Score])) THEN ‘Coach Always Wins!’ END

and add this to Label along with Overall Avg Score. Increase the width of the bars to see the text and align middle left and change the font to white. Adjust the Tooltip.

Format the sheet

  • Set the worksheet background colour
  • Hide the Person column (uncheck Show Header)
  • Hide the Overall Avg Score axis (uncheck Show Header)
  • Remove all column/row dividers, gridlines, axis lines, zero lines
  • Add a white border around the bars (via the Colour shelf)
  • Increase the Size of the bars so there is a small gap between
  • Format the * to be white font
  • Hide the Winner Icon label (right click > hide field labels for rows)
  • Make the Winner Icon column as narrow as possible

Name the Sheet Avg Per Person or similar.

Building the Average Score Per Hole bar chart

On a new sheet, add Hole to Columns and Avg Score Per Hole to Rows. Add Avg Score Per Hole to Colour and adjust the scale as we did above so it ranges from green to blue and -1 to 10.

Show mark labels, adjust the tooltip. Set the background colour of the worksheet and remove all gridlines, zero lines, row/column dividers etc. Hide the Avg Score Per Hole axis and the hole labels (uncheck show header on the Hole pill). Adjust the font of the labels to be Tableau Medium and white. Add a white border around the bars. Increase the Size to leave a small gap.

Name the sheet Avg Per Hole or similar.

Building the dashboard

Set the background of the dashboard to the same colour of the worksheets.

Using containers, add a horizontal container and add a text object (for the title), the Avg Per Hole sheet and then a blank object. Remove the container that gets added with the colour legend.

Add another Horiztonal container beneath and add the Heatmap sheet and the Avg Per Person sheet.

Remove all padding. Remove all titles from the sheets. Set all the charts to fit entire view. Manually line up everything, but you’ll find you have an issue getting your horizontal bar chart to align to the player rows due to the header in the heatmap

(Note – you may notice your labels on the heat map aren’t displaying due to the space available). You can continue to adjust on Desktop so they do appear (make the heatmap have more vertical space), or wait until you’ve published to Tableau Public and see if you get the desired result… although at the point of writing , Tableau Public is having issues with the table calcs and causing odd behaviour with the display).

To fix this, go back to the Avg Per Person sheet and double click into column and manually type “” to create a dummy header row with no text. Hide the “” label (right click > hide field labels for columns). You can then adjust the height of this header label section to help get the alignment right.

Then make any final adjustments required – add the title, and any imagery etc. My published viz is here.

Happy vizzin’!

Donna

Visualise Our Survey Data

Donna Coles1 Comment

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 you partcipate 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.

Happy vizzin’! Stay Safe!

Donna