Visualise Our Survey Data

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

How often is Sean listening to his favourite songs?

A “scatter plot with a twist” music based challenge this week was posed by Sean Miller, using data from his last.fm account. On the surface, a simple scatter plot of each song, comparing the number of days between first and last listen against total plays. But click on song, and you get to see a timeline depicting days since first play vs cumulative plays, on the same chart. Hmmm…

The data set provided consists of 1 row per song per date played since 2017. For the scatter plot, we’re looking to summarise the data at a song level. For the timeline related to the selected song, we need to have the data at the song and date level. Ultimately we’re mixing levels of granularity within a single chart.

This certainly took some thinking. Creating the 2 charts independently was pretty straightforward, but trying to put them together took some thought. I knew I was going to want to use a set action to drive the interactivity and apply different logic based on whether a song was selected or not, but it took a bit of trial and error to get a solution.

First up, the data provided contained some timestamp date fields, but these were string data types. I chose to use the Timestamp UTC field to get a ‘proper’ date. I actually did this by duplicating the field, renaming it to Date Played and changing the datatype to a date. This generated a calculation which is below… I’d have never of typed this myself 🙂

Date Played

DATE(IF NOT ISNULL( DATEPARSE ( “MM/dd/yyyy HH:mm”, [TimeStamp UTC] ) ) THEN DATEPARSE ( “MM/dd/yyyy HH:mm”, [TimeStamp UTC] ) ELSEIF NOT ISNULL ( DATEPARSE ( “MM/dd/yyyy hh:mm:ss a”, [TimeStamp UTC] ) ) THEN DATEPARSE ( “MM/dd/yyyy hh:mm:ss a”, [TimeStamp UTC] ) END)

Now we have that, we can work out the first date a song was played

Min Date Per Song

{FIXED [Song ID]:MIN([Date Played])}

and the latest dates

Max Date Per Song

{FIXED [Song ID]:MAX([Date Played])}

and then we can derive the days between

Days since first listen

DATEDIFF(‘day’, [Min Date per Song], [Max Date per Song])

We can also get the total plays per song using

Total Plays per Song

{FIXED [Song ID]: [Total Plays]}

and with these calculated fields, we can build the basic scatter, using Total Plays per Song as a range filter, and setting both the axes not to start at zero.

So far so good. Now let’s think about the timeline. We need to identify a ‘selected song’ to help build this, so let’s create a set by right clicking on Song ID > Create > Set, and selecting a single option

We need to capture the number of plays on a date

Total Plays

COUNT([2021_05_26_WW21_My Streaming Activity.csv])

This is the default count field generated (the equivalent of the Number of Records if you’re on older versions of Tableau).

And we also need to capture the number of days from first play date to the current date, as we need to plot on a consistent axis when putting the charts together (ie we can’t plot with date on the axis).

Days to Date

DATEDIFF(‘day’,[Min Date per Song],[Date Played])

Let’s put this out into a table so you can see what’s going on.

Add the Selected Song = True to the filter shelf

There are days when there were multiple plays, so the Days To Date field needs to be set to AVG rather than SUM, to get the correct figure.

For Total Plays we need to plot the cumulative value, so we can set a quick table calculation against the Total Plays field of Running Sum (right click on Total Plays field). We’re going to explicitly set the table calculation to compute by Date Played as when depicted on a viz, the default of table down, might not give the correct values.

So with these fields, we can build the timeline viz (duplicate the table sheet and move the fields around). Set the mark type to line, and change the setting on the Path to make a stepped chart.

Ok, so now we have the 2 charts and hopefully understand what we’re aiming for. But how do we now go about getting everything onto a single chart?

We’re going to need a dual axis chart, since we have different mark types in play. And we’re going to want to plot different measures depending on whether we’re working with the selected song or not.

We’re going to build the data up in a table to get the logic for the fields we need. To just test the concept, we’ll filter to just a few songs, including the one in the Selected Song set. Add Song ID to the filter shelf and filter to a few songs.

Then build out a table as below:

What we’re aiming for, is for the records where In/Out of the set is Out, we want to plot the information we’re getting from the 1st two columns, but for the records where In/Out the set is In, we want the information from the other columns.

So let’s build this out.

Days to Plot

IF ATTR([Selected Song]) THEN AVG([Days To Date]) ELSE SUM([Days since first listen]) END

Plays to Plot

IF ATTR([Selected Song]) THEN RUNNING_SUM([Total Plays]) ELSE SUM([Total Plays per Song]) END

Pop these in the table, making sure any fields which are table calculations are set to compute by Date Played.

If you scroll to find where the data changes from a song out of the set to the one in, you can see how the two new fields are working.

So lets try plotting the chart out using these fields instead.

  • Days to Plot on Columns
  • Total Plays Per Song on Rows
  • Song ID on Detail shelf
  • Plays to Plot on Columns
  • Date Played (set to exact date) on Detail of the All Marks card.
  • Set the table calculation of Plays to Plot to compute by Date Played only.
  • Change mark type of the Plays to Plot to Line and set the Path to stepped line
  • Add Total Plays Per Song to Filter shelf and set to range from 50 to 100 (just to make the chart less busy).

You should end up with the below

You’ll notice we have the line of circles in the top chart, which is plotting a mark per day for the Selected Song. If we remove the Date Played pill from the Detail shelf of the Total Plays Per Song marks card, we lose these marks including the mark for the Selected Song too, which we need.

To resolve this, we need another field.

Is Last?

LAST()=0

Last Plays to Plot

IF [Is Last?] THEN Sum([Total Plays per Song]) END

This is just saying give me the total plays for the last mark in the list. Add these to the table to see what’s going on, making sure to set the table calc to compute by Date Played only

Now if we replace Total Plays per Song with the Last Plays to Plot field, we get
Now make the chart dual axis (don’t forget to synchronise), and we can then sort the formatting.
  • Set the marks type on the Last Plays to Plot to circle
  • Remove Measure Names from the Colour shelf, and set the mark colour to #3dde3c. Reduce the opacity to about 90%. Add a dark grey border.
  • Add Selected Song to the Size shelf, and adjust so the selected song is larger than the others.
  • On the Plays to Plot marks card, again remove Measure Names from the Colour shelf, and set the colour to black.
  • Set the Path to stepped line.
  • Click on the right hand axis and select Move marks to back
  • Reduce the Size of the mark.
  • Add Selected Song to the Colour shelf, and adjust the colours so the line is black and the tiny dots for all the other marks that you can see in the circles, is set to #3dde3c

You’ve now got the core chart, which needs to be further tidied to remove grid lines, axes, add tooltip etc. Once done, you can add to a dashboard, where you can then set the interactivity.

Add a dashboard action to change set values that sets the Selected Song set on Select. Set to work on single-select only.

Finally, you’ll find that if you select a mark, while you’ll get the trend line, the other points will now ‘fade out’

Create a new field True = True, and add this to the Detail shelf of the All Marks card. Then on the dashboard, add a new dashboard highlight action, which is set to Target Highlighting to the True field only.

Now if you click on and off a mark, you should get the trendline show and disappear, and all marks remain at the same transparency throughout.

Hopefully you’ve got enough now to complete this challenge. My published viz is here.

Happy vizzin’! Stay Safe!

Donna

Can you build a Control Chart?

Lorna Brown returned this week to set another table calculation based challenge involving a line chart which ‘on click’ of a point, exposed the ability to ‘drill down’ to view a tabular view of the data ‘behind’ the point. This is classic Tableau in my opinion – show the summarised data (in the line chart) and with ‘drill down on demand’. Lorna added some additional features on the dashboard; hiding/showing filter controls to change how the data is displayed in the chart, and a back navigational button on the ‘detail’ list.

The areas I’m going to focus on in this blog are

  • Setting up the parameters
  • Defining the date to plot on the chart
  • Restricting the data to the relevant years
  • Defining the reference bands
  • Colouring the marks
  • Working out the date range for the tooltip
  • Building the table
  • Drill down from chart to table
  • Un-highlight selected marks
  • Hide/Show filter controls
  • Add navigation button

Setting up the parameters

This challenge requires 3 parameters.

Select a Date

a string parameter containing the 2 options available (Date Submitted & Date Selected) for selection, which when displayed on the dashboard will be set to a single value list control (ie radio buttons)

Latest X Years

an integer parameter, defaulted to 3, which allows a range of values from 1 to 5.

NOTE – Ensure the step size is set to 1, as this is what allows the Show buttons option to be enabled when customising the Slider parameter control type.

STD

another integer parameter, defaulted to 1, that allows a range of values from 1 to 3

Defining the date to plot on the chart

The Select a Date parameter is used to switch the view between different dates in the data set. This means you can’t plot your chart based on date field that already exists in the data set. We have to create a new field that determines which date field to select based on the parameter

Date to Plot

DATE(DATETRUNC(‘week’, IIF([Select a Date]=’Date Submitted’,[Date sent to company], [Date received])))

The nested IIF statement, is basically saying, if the parameter is ‘Date Submitted’ then use the Date sent to company field, else use the Date received field. This is all wrapped within a DATETRUNC statement to reset all the dates to the 1st day of the week (since the requirement is to report at a weekly level).

Note – there was some confusion which field the parameter option should map to. I have chosen the above, but you may see solutions with the opposite. Don’t get hung up on this, as the principal of how this all works is most important.

Restricting the data to the relevant years

The requirement is to show ‘x’ years worth of data, where 1 year’s worth of data is the data associated to the latest year in the data set (ie from 01 Jan to latest date, rather than 12 months worth of data). So to start with I calculated, rather than hardcoded, the maximum year in the data

Year of Latest Date

YEAR({MAX([Date to Plot])})

Then I could work out which dates I wanted via

Dates to Include

[Date to Plot]>= MAKEDATE([Year of Latest Date] – ([Latest X Years]-1),1,1)

In the MAKEDATE function, I’m building a date that is the 1st Jan of the relevant based on how many years we need to show.

So if Year of Latest Date is 2020 and Latest X Years =1 then Year of Latest Date – (Latest X Years -1) = 2020 – (1-1) = 2020 – 0 = 2020. So we’re looking for dates >= 01 Jan 2020.

So if Year of Latest Date is 2020 and Latest X Years =3 then Year of Latest Date – (Latest X Years -1) = 2020 – (3-1) = 2020 – 2 = 2018. So we’re looking for dates >= 01 Jan 2018.

This field is added to the Filter shelf and set to true.

So at this point, our basic chart can be built as

  • Year on Columns (where Year = YEAR([Date to Plot])), and allows the Year header to display at the top
  • Date to Plot on Columns, set to Week Number display via the pill dropdown option, and also set to be discrete (blue pill). This field is ultimately hidden on the display.
  • Number of Complaints on Rows (where Number of Complaints = COUNT([XXXX.csv], the auto generated field relating to the name of the datasource).

To get the line and the circles displayed, this needs to become a dual axis chart by duplicating the Number of Complaints measure on the Rows, synchronising the axis and setting one instance to be a line mark type, and the other a circle.

Defining the reference bands

The reference bands are based on the number of standard deviations away from the mean/ average value per year.

Avg Complaints Per Year

WINDOW_AVG([Number of Complaints])

Once we have the average, we need to define and upper and lower limit based on the standard deviations

Upper Limit

[Avg Complaints Per Year] + (WINDOW_STDEV([Number of Complaints]) * [STD])

Lower Limit

[Avg Complaints Per Year] + (WINDOW_STDEV([Number of Complaints]) * -1 * [STD])

Add both these fields to the Detail shelf of the chart viz (at the All marks card level) and set the table calculation of each field to Compute By Date to Plot

This ‘squashes’ everything up a bit, but we’ll deal with that later.

Add a Reference Band (right click on axis – > Add Reference Line) that ranges from the Lower Limit to Upper Limit.

If an Average Line also appears on the display, then remove it, by right clicking on the axis -> Remove Reference Line – > Average

Colouring the marks

I created a boolean field based on whether the Number of Complaints is within the Upper Limit and Lower Limit

Within STD Limits?

[Number of Complaints]<[Upper Limit] AND [Number of Complaints]>[Lower Limit]

Add this to the Colour shelf of the circle mark type, and set to Compute Using Date to Plot. The values will be True, False or Null. Right click on the Null option in the Colour Legend, and select Exclude. This will add the Within STD Limits? to the Filter shelf, and the chart will revert back to how it was. Adjust the colours accordingly.

The Tooltip doesn’t show true or false though, so I had another field to use on that

In or Out of Upper or Lower Limits?

If [Within STD Limits?] THEN ‘In’ ELSE ‘Out’ END

Working out the date range for the tooltip

The Tooltip shows the start and end of the dates within the week. I simply built 2 calculated fields to show this.

Date From

[Date to Plot]

This 2nd instance is required as Date to Plot is formatted to dd mmm yyyy format and also used in the Tooltip. Whereas Date From is displayed in a dd/mm/yyyy format.

Date To

DATE(DATEADD(‘day’, 6, [Date to Plot]))

Just add 6 days to the 1st day of the week.

Building the table

Create a new sheet and add all the relevant columns required to Rows in the required order. For the last column, Company response to consumer, add that to the Text shelf instead (to replace the usual ‘Abc’ text). The in the Columns shelf, double click and type in ‘Company response to consumer’ which creates a ‘fake’ column heading. Format all the text etc to make it all look the same.

Add the Dates to include = true filter.

Also add the WEEK(Date to Plot) field to the Rows shelf, as a blue discrete field (exactly the same format as on the line chart). But hide this field (uncheck Show Header). This is the key linking field from the chart to the detail.

Drill down from chart to table

Create one dashboard (Chart DB) that displays the chart viz. And another dashboard that displays the table detail (Table DB). On the Chart dashboard, add a Filter Dashboard Action (Dashboard menu -> Actions -> Add Action -> Filter), that starts from the Chart sheet, runs as a Menu option, and targets the Detail sheet on the Detail dashboard. Set the action to exclude all values when no selection has been made. Name the action Click to Show Details

On the line chart, if you now click a point on the chart, the tooltip will display, along with a link, which when clicked on, will then take you to the Detail dashboard and present you with the list of complaints. The number of rows displayed should match the number you clicked on

Un-highlight selected marks

What you might also notice, is when you click on a point on the chart, the other marks will all ‘fade’ out, leaving just the one you selected highlighted. It’s not always desirable for this to happen. To prevent this, create a new field called Dummy which just contains the text ‘Dummy’. Add this onto the Detail shelf of the All marks card on the chart viz.

Then on the chart dashboard, add another dashboard action, but this time choose a highlight action. Set the action to run on select and set the source & target sheets to be the same sheet on the same dashboard. But target highlighting to selected fields, and select the Dummy field only

Hide/Show filter controls

Check out this post by The Data School that explains very simply how to work with floating containers to show/hide buttons. When creating in Desktop, the ‘onclick’ interactivity won’t work, you’ll have to manually select to show and hide, but once published to Tableau Public, it’ll behave as desired.

You have options to customise what the button looks like when the container contents are hidden, and what it looks like when they’re shown, via the Button Appearance

Add Navigation Button

On the Detail dashboard, simply add a Navigation object to the dashboard

and edit the settings to navigate back to the chart dashboard, as well as customise the appearance

Hopefully I’ve covered all the key features of this challenge. My published viz is here.

Happy vizzin’! Stay safe!

Donna

Sales Performance Dashboard

For Lorna’s final #WorkoutWednesday challenge of 2019, she asked us to recreate a dashboard, where the focus on the challenge was branding, rather than anything too difficult.

That was a relief, as I needed to squeeze both completing the challenge and writing this blog into the limited free time I have in the run up to Christmas, where most of my time is focused on preparing for the big day, catching up with friends, and recovering from my work leaving do (having left my job of the last 20 years…).

This write up is therefore going to be brief, and I’ll just point out the bits I think will be of most use…

Plotting the circles on a horizontal line

The Year filters and the Sub-category by month circle charts are both arranged with a horizontal line running through the circles. This is achieved by plotting MIN(0) on the y-axis, and formatting the zero line to be a solid coloured line.

Aligning the charts on the dashboard

The dashboard consists of 3 charts displaying Sales by date; The Sales by Day area chart at the top, the Sales by Month bar chart above Sales by Month by Sub-Category circle chart.

All 3 charts stretch to the end of the space available width wise, even when filtered by year, and the bar chart and circle chart line up vertically, so the months are in line.

To get this to work, the date fields need to be discrete (blue) rather than continuous (green).

When continuous, additional ‘padding’ at the start and end of each axis is added. Also for a circle, the mark being plotted is the centre of the circle, but for a bar chart, the mark plotted is the top left corner of the bar, so the bar appears off centre, as demonstrated by the synchronised dual-axis view below.

This goes away when the date is made discrete (blue).

Padding

The Sales by Day area chart and the Sales Performance Title (text box) butt up against each other with no white-space between. This is achieved by reducing the outer padding on the objects to 0.

Highlighting

The 3 charts at the bottom of the screen; the Sales by Month bar chart, the Sales by Month & Sub-Category circle chart and the Sales by Sub-Category bar chart, all interact with each other on hover, regardless which chart you hover on.

This is managed via a Highlight dashboard action, where only the 3 charts of interest are selected as both the source and the target sheets

Branding & adjusting the colour palette

The colour scheme I chose to recreate this challenge in differed from Lorna’s solution. I selected a range of browns from the colour palettes I already have installed, but felt the darkest brown was just too strong. So I adjusted the end colour of the palette by clicking on the colour square displayed at the end of the range.

This opens the colour palette dialog, where I could select a lighter shade

which changes the palette being used to ‘Custom Sequential’

To ensure I always got the same range on each chart, I made a note of the colour hex number I selected : #83514a, which I could then just type in, each time I wanted to adjust the palette.

My published version of the challenge is here.

Hopefully you might find something helpful in this post 🙂

Merry Xmas y’all!

Happy vizzin’!

Donna