Candra McRae was back to set the challenge this week. I found it relatively straightforward, so if you’re relatively new to Tableau, this is quite a good challenge to start with. In this we’ll cover
Grouping the states
Applying the sort
Adding the total value
Adding the interactivity
Grouping the states
The states need to be grouped based on the initial letter. Candra stated she wasn’t expecting a large IF STARTWITH… type formula. I did it by making use of the fact characters can be converted into ASCII which provides a numerical representation of a letter, which we can then utilise. So we need
State Initial ASCII
ASCII(UPPER(LEFT([State],1)))
This takes the 1st letter of the State, ensures it is uppercase, and converts to ASCII. You can see below what this looks does
With this knowledge, we can then create
State Group
IF [State Initial ASCII] <=77 THEN ‘A-M’ ELSE ‘N-Z’ END
and you can now easily create the stacked bar chart (note, I’ve already removed the various gridlines etc)
Applying the Sort
The ultimate intention is to capture the Category a user clicks on into a parameter, so we need to define that parameter
pSelectedCategory
A string parameter defaulted to Office Supplies
Show this parameter on your sheet, so you can manually test how the sort will work by manually changing the values.
Create a new calculated field to define the sort
Sort
IF [pSelectedCategory]=[Category] THEN 0 ELSE 1 END
Then edit the sort property of the Category field that’s on the Colour shelf as below
Now change the value in the parameter box to Technology and see how the chart changes.
Adding the total value
Create a new field to store the total values
Total Sales by State Group
{FIXED [State Group]: SUM([Sales])}
Add this onto the Detail shelf, then add a reference line per line as below
Adding the interactivity
Once you’ve added the chart onto a dashboard, you need to add a parameter action which will set the pSelectedCategory parameter with the value from the Category field on select.
To prevent the selected category from ‘remaining selected’ on click, I applied the ‘true=false’ trick I use a lot.
Create a field True = true and a field False = false, then add both to the Detail shelf of the chart viz. On the dashboard add a new filter action which on select passes selected fields only setting true = false. As this condition can never be true, the filter doesn’t apply and this clears the highlight action.
And that is it for this week – short & sweet, but covers a handful of bite-size concepts. My published viz is here.
Continuing with ‘Community Challenge’ month, it was the turn of Will Perkins to set the challenge for this week; a challenge inspired by Google’s stock tracker.
By interacting with the published solution and reading the requirements, I deduced that I was likely to need 3 sheets – 1 for the Region headings & KPIs, 1 for the trend line chart, and 1 to drive the timeframe selections. The trend line chart looked like it was going to involve a dual axis combining a line and and area chart, along with ‘filled’ reference bands, although exactly how it would work I wasn’t entirely sure initially. Finally, there was going to be some ‘parameter actions’ action along with the ‘true = false’ trick to ensure selected marks didn’t remain highlighted.
But before we can tackle the actual chart build, we need to nail some of the calculations involved.
Identifying the date range to highlight
The intention of the chart is that on initial load, it has highlighted the timeframe for the last 14 days up to ‘today’. As this chart is being built with a static data set, which only has data up to the end of 2021, I chose to ‘hardcode’ my ‘today’ value into a parameter. This is so that in a year’s time when I might look at this again, I won’t be presented with a broken looking viz.
pToday
Date parameter defaulted to 20 Sept 2021
The user also has the ability to highlight/select dates on the chart itself, which will define a start and end date range. So we also need some additional parameters to capture this information.
pStartRange
Date parameter defaulted to 01 Jan 1900
Similarly you’ll need a pEndRange parameter too, also defaulted to 01 Jan 1900.
Later on we’ll define parameter actions which will ‘set’ these values based on user interaction.
With these fields, we can then define calculated fields to store the start and end dates depending on whether we’re using the defaults due to initial load (ie 14 days to today), or a user selected range.
Selected Range Start Date
IF [pStartRange] = #1900-01-01# THEN DATE(DATEADD(‘day’,-14,[pToday])) ELSE DATE([pStartRange]) END
Selected Range End Date
IF [pEndRange] = #1900-01-01# THEN DATE([pToday]) ELSE DATE([pEndRange]) END
We’re going to be plotting Order Date on our axis at the day level, and so to simplify things IMO, I created
Order Date Day
DATE(DATETRUNC(‘day’,[Order Date]))
which I then reference in the following calculated field, which is just to capture all the days within the range selected
Selected Dates To Plot
IF [Order Date Day]>= [Selected Range Start Date] AND [Order Date Day]<=[Selected Range End Date] THEN [Order Date Day] END
We can now start to build out the basic chart
Plotting Order Date Day and Selected Dates to Plot side by side you can see the date axis differ, with only the dates from 06 Sep – 20 Sep 21 displaying on the right hand side. The marks type for Selected Date to Plot is set to Area, and to get the marks to join up, you need to turn Stack Marks Off (Analysis -> Stack Marks -> Off menu).
Defining the Timeframe to Display
We’re going to use another parameter to store the timeframe value
pTimeframe
String parameter defaulted to 6 MONTHS (note the case – it’s simpler to match it to the display format that’s going to be used)
We then need a calculated field to tell us what to do with this value
Timeframe to Display
CASE [pTimeframe] WHEN ‘1 MONTH’ THEN [Order Date Day]>=DATEADD(‘month’,-1,[pToday]) AND [Order Date Day]<= [pToday]
WHEN ‘6 MONTHS’ THEN [Order Date Day]>=DATEADD(‘month’, -6, [pToday]) AND [Order Date Day]<= [pToday]
WHEN ‘YTD’ THEN [Order Date Day]>=DATETRUNC(‘year’,[pToday]) AND [Order Date Day]<= [pToday] WHEN ‘1 YEAR’ THEN [Order Date Day]>= DATEADD(‘year’,-1,[pToday]) AND [Order Date Day]<= [pToday] ELSE [Order Date Day] <= [pToday] END
This field will return true for all the dates that fall within each statement and false otherwise.
Add this field to the Filter shelf and select True.
You can test how the left hand side of the chart is affected by manually typing the different values into the parameter
Colouring the chart
The line and area charts are coloured based on whether dates fall in the selected range and whether the difference between the sales values at the start and end of the selected range is positive or not. We need several more calculated fields to work this out.
We firstly need to capture the min and max dates of the selected area for each region. Now, you initially might think that the Selected Range Start Date and Selected Range End Date fields already have these values. However there isn’t always a sale in every region for these dates. You could argue, that in that case, the sales value for that date should be 0 (ie there were no sales on that day), but to match the solution (and it was easier), we just get the min and max dates within the selected range that have a sales value for each region.
Min Selected Date Per Region
{FIXED [Region]: MIN([Selected Dates to Plot])}
Max Selected Date Per Region
{FIXED [Region]: MAX([Selected Dates to Plot])}
Pop these out into a quick view, and you can see how the dates differ per region compared to the default start & end date values
Now we want to work out the sales value on these dates
Min Date Sales
{FIXED [Region]: SUM(IF [Order Date Day]=[Min Selected Date Per Region] THEN [Sales] END)}
Max Date Sales
{FIXED [Region]: SUM(IF [Order Date Day]=[Max Selected Date Per Region] THEN [Sales] END)}
and then we can work out the difference and the % difference
Range Sales Diff
SUM([Max Date Sales])-SUM([Min Date Sales])
custom formatted to +”$”#,##0.00;-“$”#,##0.00 to show a ‘+’ prefix for positive values
Range Sales % Diff
[Range Sales Diff]/SUM([Min Date Sales])
custom formatted to ▲0.0%;▼0.0%
Now we can compute a field to use to colour the line/area chart
Colour – Trend
IF MIN([Order Date Day]) >= [Selected Range Start Date] AND MIN([Order Date Day])<= [Selected Range End Date] THEN
IF [Range Sales Diff]>= 0 THEN 1 ELSE -1 END ELSE 0 END
If we’re within the selected date range, then test to see if the value is positive (set to 1) or negative (set to -1), otherwise we’re outside the selected date range, so set to 0
Go back to the trend chart and add this field to the Colour shelf of the All Marks card (so it gets added to both sets of marks). Change it to be a discrete (blue) pill and the adjust the colours accordingly. At this point you may want to change the background colour if you’re using a white line. I’m just setting it to a light grey at this point, but eventually it’ll get set to black.
Adding the highlight band
This took a lot of thinking. I knew I’d need a reference band, but it took some time to figure out how to get the backgrounds coloured differently, since you only have the option to fill between the band with one colour.
The trick is to make use of the two date axes we have and to apply a band per pane.
But we need some more fields to make this happen.
Ref Line Start Date -ve
IF [Range Sales Diff]<0 THEN [Selected Range Start Date] END
Ref Line End Date -ve
IF [Range Sales Diff]<0 THEN [Selected Range End Date] END
Add these fields to the Detail shelf of the Order Date Day card and set to be continuous (green). Then add a reference band to this axis, applying the settings as below (note, the Line is a white dotted line, so isn’t showing up in the field setting, though you can see it on the viz).
Because the reference band has been set at the pane level, and the reference line dates are only relevant if the difference is negative, then the band is just showing on one row.
We then do something very similar, but this time we get some dates only if the difference is positive.
Ref Line Start Date +ve
IF [Range Sales Diff]>=0 THEN [Selected Range Start Date] END
Ref Line End Date +ve
IF [Range Sales Diff]>=0 THEN [Selected Range End Date] END
Add these as continuous pills on the Detail shelf of the Selected Dates to Plot card, and add another reference band to this axis instead.
Now you can set the chart to be a dual axis, synchronising the axes, and removing the Measure Names field from the All Marks card which will have automatically been added
This is the core viz, that will need further formatting before its ready to put on the dashboard – remove gridlines, borders etc, set background, remove headers. NOTE– You’ll need to manually re-sort the Regions before the field is hidden.
The KPI table
We need to build a ‘fake table’ for this, by putting Region on Rows and typing MIN(0) on Columns, then adding the Range Sales Diff and Range Sales % Diff fields to the Text shelf. We need an additional field to colour the text though.
Colour – KPI
[Range Sales Diff]>=0
Finally, I capitalised the Region values by using Aliases. This is a quick method when there aren’t many values, but otherwise I would usually create a field with UPPER([Region}).
Once again don’t forget to sort the Regions, and the apply relevant formatting.
The Timeframe Selector
Will states you can use a separate data source for this, so create the list in Excel
and then copy and paste (via the Data > Paste) menu into your workbook
On a new sheet add Date Range to Columns and Date Range to Text. The size and colour of the text differs based on which one has been selected. So create a field
Timeframe Selected
[Date Range] = [pTimeframe]
and add this field to both the Size and Colour shelves. You’ll need to adjust the settings, and hide headers, remove gridlines etc. Try to avoid touching the Text formatting directly, as you might find the Size then doesn’t adjust.
Adding the interactivity
You’ll need to use layout containers to organise all the objects on the dashboard. Then you can add the various dashboard parameter actions needed
Selecting the timeframe
Add a parameter action that on select passes the Date Range field into the pTimeframe parameter
Selecting the date range to highlight
You’ll need 2 parameter actions for this, one that passes the minimum Order Date Day selected into the pStartRange parameter, and the other that passes the maximum Order Date Day selected into the pEndRange parameter.
Deselecting the highlighted marks
By default when you click on a mark/select marks in Tableau, they are highlighted/selected and the other marks are faded, until you ‘click’ again. To stop this from happening I use a ‘true = false’ trick, that has become very common in #WOW challenges, and I’ve blogged many times before.
Create calculated fields
True
True
and
False
False
and add these to the Detail shelf of the All Marks card on the trend line chart.
Then on the dashboard add a Filter action that on select targets the sheet directly, mapping the true field to the false field. As this can never be ‘true’ the filter doesn’t apply, and the marks become unselected.
Repeat the same on the Timeframe Selector sheet.
Hopefully that’s covered all the core points. My published viz is here.
When I was approached by the #WOW crew to provide a guest challenge, I was a little unsure as to what I could do. I primarily work as a Tableau Server admin, so rarely have a need to build dashboards (which is why I like to do the weekly #WOW challenges, to keep up my Desktop skills). Then the next day I was looking at a dashboard I’d built to monitor extracts on our Tableau Servers, and I thought it would be an ideal candidate for a challenge. I also thought it would provide any users of Tableau Server with the opportunity to implement this dashboard in their own organisation if they wished, by sharing with their Server Admins.
As a Tableau Server Admin, you get access to a set of ‘out of the box’ Admin views, one of which is called ‘Background Tasks for Extracts’ which gives you a view of when extracted data sources and workbooks run on the server. However while the provided view is fine if you want to quickly see what’s going on now, it’s not ideal if you want to see how things ran over a longer timeframe – it involves a lot of horizontal scrolling.
Many server admins will have ‘opened’ up access to the Tableau repository, the PostgreSQL database which stores a rich array of data, all about your Tableau Server [see here for further info], and enables admins to extend their analysis beyond the provided Admin views. This site even provides a set of pre-curated data sources to help you get started! These aren’t formally supported by Tableau, but is the brain-child of Matt Coles, a server admin at Tableau (no relation to me though!).
My dashboard doesn’t actually use one of these data sources though. For the challenge, I’ve just created some sample, anonymised data in the required structure. I’ll explain later at the end of the post how to go about converting this to use ‘real’ server data, if you do want to plug it into your own server environment.
Understanding the data
When using Tableau Server, published data sources and workbooks can connect to their underlying data source (eg a SQL Server database, an Excel file etc) directly (ie via a live connection) or via an extract. An extract means that a copy of the data is pulled from the underlying data source and stored on Tableau Server utilising Tableau’s ‘in memory’ engine when the data is then queried. An extract gets added to a schedule which dictates the time when the extract will get refreshed; this may be weekly, daily, hourly etc. Every time the extract runs, a background task is created which provides all the relevant details about that task. The data for this challenge provides 1 row for each extract task that was created between Monday 11th Jan 2021 and Friday 5th Feb 2021. The key fields of note are:
Id – uniquely identifies a task
Created At – when the task was created
Started At – when the task actually started running (if too many tasks are set to run at the same time, they will queue until the server resources are available to execute them).
Completed At – when the task finished, will be NULL if task hasn’t finished.
Finish Code – indicates the completion status of the job (0=success, 1=failed, 2= cancelled)
Progress – supposed to define the % complete, but has been observed to only ever contain 0 or 100, where 100 is complete.
Title – the name of the extract
Site – the name of the site on the server the extract is associated to
Based on the Finish Code and Progress, I have derived a calculated field to determine the state of the extract (to be honest, I think this is a definition I have inherited from closer analysis of the Background Tasks for Extracts Server Admin view, so am trusting Tableau with the logic).
Extract Status
IF [Finish Code] = 1 AND [Progress] <> 100 THEN ‘In Progress’ ELSEIF [Finish Code] = 0 AND NOT [Progress] = 1 THEN ‘Success’ ELSE ‘Failed’ END
Building the required calculated fields
The intention when being used ‘in real life’, is to have visibility of what’s going on ‘Now’ as well as how extracts over the previous few days have performed. As we’re working with static data, we need to hardcode what ‘Now’ is. I’ll use a parameter for this, so that in the event you do choose to plug this into your own server, you only have to replace any reference to the Now parameter with the function NOW().
Now
Datetime parameter defaulted to 05 Feb 2021 16:30
The chart we are going to build is a Gantt chart, with 1 bar related to the waiting time of the task, and 1 bar related to the running time of the task. We only have the dates, so need to work out the duration of both of these. These need to be calculated as a proportion of 1 day, since that is what the timeframe is displayed over.
Waiting Time
(DATEDIFF(‘second’, [Created At], IF ISNULL(Started At]) THEN [Now] ELSE [Started At ] END))/86400
Find the difference in seconds, between the create time and start time (or Now, if the task hasn’t yet started), and divide by 86400 which is the number of seconds in a day.
We repeat this for the processing/running time, but this time comparing the start time with the completed time.
Processing Time
(DATEDIFF(‘second’, [Started At], IF ISNULL([Completed At]) THEN [Now] ELSE [Completed At] END))/86400
As mentioned the timeframe we’re displaying over is a 24 hr period, and we want to display the different days over multiple rows, rather than on a single continuous time axis spanning several days.
To achieve this, we need to ‘baseline’ or ‘normalise’ the Created At field to be the exact same day for all the rows of data, but the time of day needs to reflect the actual Created At time . This technique to shift the dates to the same date, is described in this Tableau Knowledge Base article.
Putting this out into a table, you can see what this data all looks like (note, I’m just choosing a arbitrary date of 01 Jan 2021, so my baseline dates are all on this date:
Building the Gantt chart
We’re going to build a dual axis Gantt chart for this.
Add Site to Rows
Add Title to Rows
Add Created At to Rows. Set it to the day/month/year level and set to be discrete (ie blue pill). Format the field to custom format of ddd dd mmm yyyy so it displays like Mon 11 Jan 2021 etc
Add Created At Baseline to Columns, set to exact date
Add Waiting Time (Avg) to Size and adjust to be thin
This will automatically create a Gantt chart view
Next
Add Started At Baseline to Columns, set to exact date, and move so the pill is now placed to the right of the Created At Baseline pill
On the Started At Baseline marks card, remove Waiting Time and add Processing Time (Avg) to the Size shelf instead. Adjust so the size is thicker.
Set the chart to be dual axis and synchronise the axes
The thicker bars based on the Started At / Processing Time need to be coloured based on Extract Status. Add this field to the Colour shelf of the Started At Baseline marks card and adjust accordingly.
The thinner bars based on the Created At / Waiting Time need to be coloured based on how long the wait time is (over 10 mins or not).
Over Wait Time Threshold
[Waiting Time] > 0.007
0.007 represents 10 mins as a proportion of the number of minutes in a day (10 / (60*24) ).
Add this field to the Colour shelf of the Created At Baseline marks card and adjust accordingly (I chose to set to the same red/grey values used to colour the other bars, but set the transparency of these bars to 50%).
Formatting the Tooltips
The tooltip for the Waiting Time bar displays
The Created At Baseline and Started At Baseline should both be added to the Tooltip shelf and then custom formatted to h:mm am/pm
The Waiting Time needs to be custom formatted to hh:mm:ss
The tooltip for the Processing Time bar is similar but there are small differences in the display,
Formatting the axes
The dates on the axes are displayed as time in am/pm format.
To set this, the Created At Baseline / Started AtBaseline pills on the Columns shelf need to be formatted to h:mm am/pm
Adding the reference band
The reference band is used to highlight core working hours between 8am and 5pm. Right click on the Created AtBaseline axis and Add Reference Line. Create a reference band using constants, and set the fill colour accordingly.
Apply further formatting to suit – adjust sizes of fonts, add vertical gridlines, hide column/axes titles.
Filtering the dates displayed
As discussed above, when using this chart in my day to day job, I’d be looking at the data ‘Now’. As a consequence I can simply use a relative date quick filter on the Started At field, which I default to Last 7 days.
However, as this challenge is based on static data, we need to craft this functionality slightly differently.
We’re only going to show up to 10 days worth of data, and will drive this using a parameter.
pDaysToShow
An integer parameter, ranging from 1 to 10, defaulted to 7, and formatted to display with a suffix of ‘ days’.
We then need a calculated field to use to filter the dates
Additionally, the chart can be filtered by Site, so add this to the Filter shelf too.
Building the Key legend
Some people may build this by adding a separate data source, but I’m just going to work with the data we have. This technique is reliant on knowing your data well and whether it will always exist.
On a new sheet, add Site to the Filter shelf and filter to sites 7 and 9.
Create a new field
Key Label
If [Site] = ‘Site 9’ THEN ‘Waiting’ ELSE ‘Processing’ END
and add this to the Columns shelf and sort the field descending, so Waiting is listed before Processing.
Alongside this field, type directly into the Columns shelf MIN(1).
Edit the axes to be fixed to from 0 to 1. Then add the Site field to the Colour shelf and also to the Size shelf and adjust accordingly (you may need to reverse the sizes). I lightened the colour by changing the opacity to 50%.
Now hide the axes, remove row & column borders, hide the column title and turn off tooltips.
The information can all now be added to a dashboard.
Using your own data
To use this chart with your own Tableau Server instance, you need to create a data source against the Tableau postgres repository that connects to the _background_tasks (bgt) table with an inner join to the _sites (s) table on bgt.site_id = s.Id. Rename the name field from the _sites table to Site. If you don’t use multiple sites on your Tableau Server instance, then the join is not required. The sole purpose of the join is to get the actual name of the site to use in the display/filter.
You should then be able to repoint the data source from the Excel sheet to the postgres connection. You may find you need to readjust some of the colours though.
When I run this, I’m using a live connection so I can see what is happening at the point of viewing, rather than using a scheduled extract. To help with this, I add a data source filter to limit the days of data to return from the query (eg Created at <=10 days), which significantly reduces the data volume returned with a live connection.
Hopefully you enjoyed this ‘real world’ challenge, and your server admins are singing your praises over the brilliance of this dashboard 🙂
A relatively straightforward challenge was set by Luke this week, to visualise the difference in Sales between 2020 and 2021 in a slightly different format than what you might usually think of.
Start by filtering the data to just the years 2020 and 2021 (add Order Date to the Filter shelf and select specific years, or add a data source filter to limit the whole data set).
Add Sub-Category to Rows, and Sales to Columns, then add Order Date to Colour which by default will display as YEAR(Order Date). Colour the years appropriately.
Now unstack the marks (Analysis menu -> Stack Marks -> Off), and re-order the colour legend, so 2021 is listed first (this makes the 2021 bars sit ‘on top’ of 2020).
Adjust the size to make the bars thinner.
Now add another instance of Sales to the Columns shelf, and make the chart dual axis (synchronising the axis). Reset the mark type of the original SUM(Sales) marks card back to bar.
We need the circle mark for the 2021 Sales to be blue. To do this, duplicate the Order Date field, then add Order Date (copy) to the Colour shelf of the SUM(Sales)(2) marks card. This will show another colour legend, and you can set the colours accordingly. Add a white border around the circle marks.
To work out the % difference to display on the label, we need the following fields
2021 Sales
{FIXED [Sub-Category]: SUM(IF YEAR([Order Date])=2021 Then [Sales] END)}
This returns the value of the 2021 sales for each Sub-Category against all the years in the data set. Similarly we need
2020 Sales
{FIXED [Sub-Category]: SUM(IF YEAR([Order Date])=2020 Then [Sales] END)}
Another table calculation related challenge this week, set by Luke, visualising cumulative starts for NFL Quarterbacks per team from 2006.
Luke provided the data within a Tableau workbook template on Tableau Public, so I started by downloading the workbook and understanding the data structure.
The challenge talks about teams playing over 17 weeks, but the data showed some data associated to weeks 28-32. So I excluded these weeks by filtering them out.
I then started to build out the data in tabular form, so I could start to build up what was required. I added Team, Season, Week and Player ID to Rows, and just to reduce the amount of data I was working with while I built up the calcs, chose to filter to the Teams ARZ, ATL & BLT.
What we’re looking to do is examine each Player ID and work out whether it is the first record for the Team or whether it differs from the previous row’s data. If so then we’re at the ‘start’ of a run, so we record a ‘counter’ value of 1. If not, the values match, so we need to increment the counter.
We’ll do this in stages.
Firstly, let’s get the previous Player ID value.
Prev Player ID
LOOKUP(MIN([Player Id]),-1)
This ‘looks up’ the record in the previous (-1) row. Change this field to be discrete and add to the Rows. Set the table calculation to compute by all fields except Team.
Each Prev Player ID value matches the Player ID from the row before, unless its the first row for a new Team in which case the value is Null.
Then we can create a field to check if the values match
Match Prev Player ID
MIN([Player Id])=[Prev Player ID]
Add this to the view and set the table calc as above, and the data shows True, False or NULL
Now we can work out the consecutive streak values
Consecutive Streak
IF (NOT([Match Prev Player ID])) OR ISNULL([Match Prev Player ID]) THEN 1 ELSE 1+PREVIOUS_VALUE(-1) END
If we don’t match the previous value or we’re at the start of a new team (as value is NULL), then start the streak by setting the counter to 1, otherwise increment the counter. Add this to the view and set the table calc for both the nested calculations as per the settings described above.
Next we need to identify the last value of the Consecutive Streak for each Team.
Current Streak
WINDOW_MAX(IF LAST()=0 THEN [Consecutive Streak] END)
The inner IF statement, will return the value of Consecutive Streak stored against the last row for the Team. All other rows will be Null/blank. The WINDOW_MAX() statement then ‘spreads’ this value across all the rows for the Team.
Add this onto the view, and set the table calc for all the nested calcs.
Finally, we need one more bit of data. The chart essentially plots values from 2006 week 1 through to 2020 week 17. We need to ‘index’ these values, so we have a continuous week number value from the 1st week. We can use the Index table calculation for this
Index
INDEX()
Add this field to the view, set it to be discrete (blue pill) and position after the Week field on the Rows. Set the table calc as usual, so the Index restarts at each Team.
Now we’ve got all the data points we need, we can build the viz. I did this by duplicating the tabular view and then
Remove Prev Player ID and Match Prev Player ID
Move Season, Week and Player ID from Rows to Detail
Move Current Streak from Text to Rows and change to be discrete (blue)
Move Index from Rows to Columns and change to be continuous (green)
Move Consecutive Streak from Text to Rows
Change mark type to bar, and set to fit width to expand the view.
Change Size to be Fixed, width size 1 and aligned right
Set the border on the Colour shelf to be None.
Remove the Team filter and adjust the row height
All that’s left now is to set the tooltip (add Player to the Tooltip shelf to help this), and then apply the formatting. You can use workbook formatting (Format -> Workbook menu) to set all the font to Times New Roman.
Hopefully this is enough to get you to the end 🙂 My published viz is here.
Following the #WOW survey where practice in table calculations was the most requested feature, Lorna continues with the theme in this challenge, where the focus is on the moving average table calculation, plus a couple of extra features thrown in.
Moving average
This is based on the values of data points before and after the ‘current’ point, as defied by the parameters which will need to be created.
pPrior
Integer parameter ranging from 1 to 6 and defaulted to 3. You need to explicitly set the Step size to 1 to ensure the step control slider appears when you add the parameter to the dashboard. This will be used to define the number of data points prior to the current to use in the calculation.
Create an identical parameter pPost to define the number of data points to use after the current one.
With these parameters, we can now create the core calculation
As the requirement states that the ‘prior’ parameter needs to include the ‘current’ value, then we need to adjust the calculation – ie if the parameter is 3, we actually only want to include 2 prior data points, as the 3rd will be the current point itself. This is what the +1 is doing in the 2nd argument of the function.
Lorna has stated that 3 Sub-Categories are grouped to form a Misc category, so we need to create a group off of Sub-Category (right click Sub-Category -> Create -> Group).
Multi-select the 4 options that need to be grouped (hold down Ctrl as you select), and then group, and rename the group Misc.
Now we can check what the calculation is doing. If you add the fields onto the view as below, and set the Moving Avg table calculation to compute using Month of Order Date only (see further below), you should be able to see that each month’s moving avg value is calculated based on the sales value of the set of previous & post months as defined by your parameters. In the image below the Moving Avg for Accessories in June 2018, is the average of the Sales values from April 2018 – Sept 2018.
With this you can start the beginnings of the viz – don’t forget to set the table calc as above.
Colouring the lines
This will be managed by using a set.
Right click on the Sub-Category (group) field -> Create -> Set. Initially select all values. Add this field to the Colour shelf. Additionally, click the Detail symbol (…) to the left of the Sub-Category (group), and select the Colour symbol, so this field is also added to the Colour shelf.
The resulting colour legend will look something like thisEdit the colour legend, then choose Hue Circle and select Assign Palette to randomly assign colours to all the options
To show the set values, click on the context menu of the Sub-Category (Group) field on the Colour shelf, and Show Set.
This will add the list of options for selection
Uncheck All so none are selected, which will change the colour legend to read ‘Out, xxx’. Edit the colour legend again, and control-click to multi select all options, then set to a single grey
Now if you select a few options, the ones selected will be coloured, while the others remain grey
Additionally add the set field onto the Size shelf and make the In option bigger than the Out.
Shading the background
For this we need to create an unstacked area chart with one measure representing the maximum moving average value for the month, and the other representing the minimum moving average value for the month. We’ll need new calculated fields for this:
Window Max Avg
WINDOW_MAX([Moving Avg])
Window Min Avg
WINDOW_MIN([Moving Avg])
If you’ve still got your data sheet available, then move Sub-Category (Group) onto Rows, then add the two newly created fields.
In this case there are ‘nested’ table calcs. You need to ensure the setting related to the Moving Avg is computing by Month Order Date only, but the setting related to the Window Max Avg (or Window Min Avg)is computing by Sub-Category (Group).
If set properly, you should see that for each month the max / min values are displayed against every row.
Back to your chart viz sheet, and add Window Max Avg to Rows. Set the table calc settings as described above, then remove the Sub-Category (group) Set field from the Colour shelf of this measure, and change the Sub-Category (group) to be on the Detail rather than Colour shelf.
Change the mark type to Area, set the Opacity of the colour to 100% and set stack Marks to be Off (Analysis Menu -> Stack Marks -> Off).
Now drag Window Min Avg onto the Window Max Avg axis and drop it when the ‘2 columns’ image appears.
This will change the view so Measure Values is now on the Rows shelf and Window Min Avg is now displayed in the Measure Values section on the left hand side.
Adjust the table calc setting of Window Min Avg to be similar to how we set the Max field. And now drag the fields so Window Min Avg is listed before Window Max Avg. Measure Names will now be on the Colour shelf of this marks card, so adjust so Window Min Avg is white and Window Max Avg is pale grey.
Now make the chart dual axes, synchronise the axes, and set the Measure Values axis to the ‘back’.
Everything else is now just formatting and adding onto a dashboard. My published viz is here.
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.
It was Candra’s turn to ‘set’ the #WOW2021 challenge this week providing a hint in the challenge description that the solution would involve sets.
As with many challenges, I built the data out in tabular format to start with to verify I had all the components and calculations correct. The areas of focus are
Identify number of distinct customers per product
Identify overall average number of distinct customers per product
Identify if product above or below average distinct customers
Identify Top 50 products by Sales
Identify Unprofitable Products
Identify products that are both in the top 50 AND unprofitable
Building the viz
Identify number of distinct customers per product
To start off, add Product Name, Sub-Category, Category to the Rows shelf to begin building out a table. Add Sales (formatted to $k 0dp) and Profit (formatted to $k 0dp with negative values as () ) to Text and sort by Sales descending.
To identify the distinct customers per product, we can create
Customer Count per Product
{FIXED [Product Name] : COUNTD([Customer ID])}
Add this to the view.
Identify overall average number of distinct customers per product
What we’re looking for here is the average of all the values we’ve got listed in the Customer Count per Product column. Ie we want to sum up those values displayed and divide by the number of rows.
The number of rows is equivalent to the number of products, which we can get from
Count Products
{FIXED : COUNTD([Product Name])}
And so to get the overall average we calculate
Avg Overall Customer Count
{FIXED: SUM([Customer Count Per Product])} / [Count Products]
Add these fields to the view as well, so you can see how the values work per row. The last two calculations give you the same value across all rows.
Identify if product above or below average distinct customers
Given the above display, this is just a case of comparing values in 2 columns
Higher than Avg Customer Count
AVG([Customer Count Per Product]) > SUM([Avg Overall Customer Count])
this returns true or false – add this to the view too.
Identify Top 50 products by Sales
We can create a set for this. Right click on Product Name > Create > Set. Name the set something suitable eg Top 50 Products, and on the Top tab, state the number (50) and the field (Sales) and the aggregation (Sum)
Add this to the view, and if you’ve sorted by the sales, you should find the top 50 rows are all In the set, and the rest are Out.
Identify Unprofitable Products
We can use another set for this. Again create a set off of Product Name, call it Unprofitable Products, and on the Condition tab, set the condition so that the Sum of Profit is less than 0
Add this onto the view too.
Identify products that are both in the top 50 AND unprofitable
For this, we’re explicitly looking for the rows that are both In the Top 50 Products set and In the Unprofitable Products set.
We can use the Combined Set functionality to do this.
In the left hand data pane, select both the Top 50 Products and the Unprofitable Products sets (hold down ctrl to multi select), then right click and Create Combined Set. I called the set Products to Include, and select to combine the sets by including Shared members in both sets
If you then add this field to the Filter shelf, you will be left with just the 13 Products that match
This is the single filter field you can use as per Candra’s requirements.
Building the viz
To get the text to display to the left of the bar, you actually need to create a ‘fake’ bar chart.
Add Products to Include to Filter
Add Product Name to Rows
On the Columns shelf, double click and type in MIN(1)
Add Sales to Columns to the right of MIN(1)
Sort by Sales descending
Against the MIN(1) marks card
Change the Size to small
Set the Opacity of the Colour to 0% and the border to None
Add Product Name, Sub-Category and Category to the Label shelf and adjust accordingly, aligning left
Increase the height of each row to make the text visible
On the Sales marks card
Add Higher than Avg Customer to the Colour shelf and adjust
Show mark labels
Create a new field Profit Ratio : SUM([Profit])/SUM([Sales]) Format to % with 0dp and add to Tooltip
Add Profit, and Customer Count by Product to Tooltip and adjust accordingly
Finally, uncheck Show Header against Product Name and MIN(1) and Sales and format the borders/gridlines etc. Add the title, then add to the dashboard.
It was Sean Miller’s turn to set the challenge this week, where the primary focus was to find the highest number of consecutive months where the monthly sales value was higher than the previous month.
This was a table calculations based challenge, and I always tackle these by building out the data required in a tabular format. The challenge was also reminiscent of a previous challenge Sean has set, which I’ve blogged about here, and admit I used as a reference myself.
So let’s get started.
To start with, we need the month date, the Sub-Category, the Sales value and the difference in Sales from the previous month. For the month date, I like to define this explicitly
Order Date Month
DATE(DATETRUNC(‘month’,[Order Date]))
This aligns all Order Dates to the 1st of the relevant month.
Add Sales Category, Order Date Month (set to discrete exact date blue pill), and Sales into a view, then set a Quick Table Calculation of Difference on the Sales pill
Edit the table calculation to compute by Order Date Month only, so the previous calculation restarts at each Sub-Category.
Then drag this pill from the marks card into the left hand data pane to ‘bake’ the calculated field into the data model. Name the field Sales Diff. The re-add Sales back into the view too, so you can double check the figures.
Identify whether there is an increase with the field
Diff is +ve
IF [Sales Diff]>0 THEN 1 ELSE 0 END
Add this into the view too, and verify the calculation is computing by Order Date Month only again.
Now we need to work out if the row matches the previous value
Match Prev Value
LOOKUP([Diff Is +ve],-1) = [Diff Is +ve]
The LOOKUP is looking at the previous row (identified by the -1) and comparing to the current. If they match then it returns True else False.
Again add into the view, and again double check the table calc settings. In this case there is nested calculations so you need to double check the settings against each calc referenced in the drop down
Now we need to work out when there are consecutive increases, and how many of them there are
Increase Streak
IF (NOT([Match Prev Value])) AND ([Diff Is +ve] = 1) THEN 1 ELSEIF [Diff Is +ve] = 1 THEN ([Diff Is +ve]+PREVIOUS_VALUE([Diff Is +ve]))
END
If the current row has a +ve difference and the previous row wasn’t +ve, then we’re at the start of an increase streak, so set to 1. Else, if the current row has a +ve difference then we must be on a consecutive increase, so add to the previous row, and this becomes a recursive calculation, so builds up the values..
Add this onto the view, set the table calc settings, and you can see how this is working…
So now we’ve identified the streaks in each Sub-Category, we just want the maximum value.
Longest Streak
WINDOW_MAX([Increase Streak])
Add this and set the table calc setting again. You’ll see the max value is spread across every row per Sub-Category.
Finally we need to identify Sales values in the months when the streak is at its highest.
Sales of Month with Longest Streak
IF [Longest Streak]=[Increase Streak] THEN SUM([Sales]) END
Add this into the view again (don’t forget those table calc settings), and you’ll notice that for some Sub-Categorys there are multiple points with the same max streak
With all this we can now build the viz, which is relatively straight forward….
Add Order Date Month (exact date, continuous green pill) to Columns, Sub-Category to Rows and Sales to Rows. Edit the Sales axis to be independent, then change the line type of the Path to stepped
Add Sales of Month with Longest Streak to Rows and set to dual axis, and synchronise. Make sure the mark type of the 2nd axis is set to circle, and remove Measure Names from the colour shelf of both marks.
Manually set the colour of the line chart to grey. Add Longest Streak to the Colour shelf of the circle marks card. Adjust the colour to use the green palette, set to stepped of 5 value and ensure the range starts at 0 and ends at 5 (don’t forget to edit the table calc settings!).
Now add Longest Streak as a discrete blue pill to the view too.
This is all the core components. The last thing we need to do is sort the list. I wasn’t entirely sure how it had been sorted, apart from the largest Longest Streak at the top. I created a new field for this
Sort
[Longest Streak]*-1
and added this as a blue discrete pill in front of Sub-Category….
…, then hid the column.
Then just apply the tooltip and relevant formatting on the chart.
For the legend, I created a new field
Legend
CASE [Sub-Category] WHEN ‘Art’ THEN 0 WHEN ‘Chairs’ THEN 1 WHEN ‘Labels’ THEN 2 WHEN ‘Paper’ THEN 3 WHEN ‘Phones’ THEN 4 ELSE 5 END
and added this into a new sheet as below
The components then just need to be added to the dashboard. My published version is here.
This week’s #WOW challenge was a joint one with the #PreppinData crew, with the intention to use the #PreppinData challenge to create the data set needed for the Tableau challenge. I completed the Prep challenge, but decided to use the output provided by the #PreppinData crew as the input to this challenge (just in case I had inadvertently ended up with discrepancies).
Sport Selector
Adjusting the time
The Schedule Viz
Event Counts in Tooltip
Event listing Viz in Tooltip
Other Sports bar chart Viz in Tooltip
Dashboard interactivity
Sport Selector
This is a simple chart that lists the Sport Groups. I chose to build a bar chart using MIN(1) on the Columns and Sport Group on Rows. The axis was then fixed from 0-1.
Create a set based on Sport Group and select a few values to be ‘in’ the set (eg Boxing, Gymnastics, Martial Arts).
Add the Sport Group Set to the Colour shelf to identify the selected sports. Adjust colours accordingly.
Adjusting the Time
Create a parameter pTimeAdjust which is an integer paramater, defaulted to 0 and ranges from -12 to +12. Set the step value to 1 as this will ensure when you add the parameter to the dashboard, the prev/next buttons can be displayed alongside the slider.
Create a calculated field to store the time of the event based on the ‘timezone’ selected via the above parameter
Date Time Adjust
DATEADD(‘hour’, [pTimeAdjust], [UK Date Time])
This field will be used to display the full event date & time on the event listing viz in tooltip, along with building the schedule viz itself.
Additionally, create a field based on the above, which just stores the day of the adjusted datetime field above
Day of Adjusted Date
DATE(DATETRUNC(‘day’,[Date Time Adjust]))
This field is needed to help with the filtering required for the viz in tooltips to display.
The Schedule Viz
Add Date Time Adjust set to the Month datepart (blue pill) to the Columns shelf, and alongside it add the same field set to the Day datepart (blue pill). On the Rows, add Sport Group and Sport. Add the Sport Group Set to the Filter shelf. This will give you the ‘bones’ of the schedule
In viewing the provided solution, there was a bit of a discrepancy between when a ‘medal’ icon should show or not, compared to the Medal Ceremony? field provided in the data. It transpired Lorna had made an adjustment, as there were some events that had a ‘final’, but did not include a gold medal or ceremony event.
So to try to match up with Lorna’s output, I too made adjustments, but I can’t guarantee it matches any published solution.
First up I identify the Victory Ceremony events
Is Victory Ceremony?
CONTAINS([Event],’Victory Ceremony’)
I chose to exclude all these events from the schedule, so this field is added to the Filter shelf and set to False.
I also identify the events which appear to be a ‘final’
Is Final?
CONTAINS([Event],’Gold Medal’) OR CONTAINS([Event],’ Final’)
This field will separate the events into two types. Change the MarkType to Shape, then add this field onto that shelf. Set the shapes accordingly. Note – to add the medal shape, save the image Lorna provided to your machine, then follow these instructions so it’s available for selection.
I chose to add the Is Final? to the Size shelf too, so the shapes can be adjusted to something more suitable.
If you add the rows and columns dividers, you’ll notice the single circles aren’t centred. To resolve this, we’re going to need some axis.
Add MIN(1) to the Rows shelf (y-axis). This will give us some vertical headspace.
Now we need to manage the horizontal space, and ensure the marks don’t overlap each other. When there’s no finals, we want the circle to be plotted in the middle. When there’s both non-final and final ‘events’ we want the two marks to be off-centre, one to the left and one to the right.
We need some calculations to help with this.
#Events by Sport Per Day
{FIXED [Day of Adjusted Date], [Is Victory Ceremony?],[Sport]: COUNT([Event Schedule])}
This helps us count the number of of events per day for a specific sport
#Event Finals By Sport By Day
{FIXED [Day of Adjusted Date], [Is Victory Ceremony?],[Sport]: SUM(IIF([Is Final?],1,0))}
This basically helps us count the number of finals for each sport on a day.
With this we can build
X-Axis
IF [#Event Finals by Sport Per Day] =0 THEN 5 ELSEIF [#Event Finals by Sport Per Day]-[#Events by Sport Per Day] =0 THEN 5 ELSEIF [Is Final?] THEN 7 ELSE 3 END
If there’s no final, plot at 5, if there’s only a final, plot at 5 otherwise plot a final at 7 and a non-final at 3.
Add this to the Columns shelf (set to be a dimension ie not SUM), and edit the axis to be fixed from 0-10.
Events Count in Tooltip
I was also a bit puzzled by some of the numbers being displayed in the tooltip, so chose to compute and show the following 3 measures
Number of Events for Sport on that day (this is the #Events by Sport per Day already calculated)
Number of Event Finals for Sport on that day (this is the #Event Finals by Sport by Day already calculated)
Total Number of events for all other sports on that day (ie the selected sport is excluded from the count).
SUM([# Events Per Day]) – SUM([#Events by Sport Per Day])
Pop all these on the Tooltip shelf and format appropriately.
You’ll also need to add the Day of Adjusted Date to the Tooltip. This should be set to exact date and discrete (blue pill).
Event Listing in Tooltip
Build out a data listing view of Sport Group, Sport, Date Time Adjust and add Event (I renamed the Event Split field) to the Text shelf. Add Day of Adjusted Date to the Detail shelf. Hide Sport Group and format.
On the schedule viz, add this worksheet to the tooltip, passing Sport and Day of Adjusted Date as filters on the string
Other Sports bar chart Viz in Tooltip
Once again this is a relatively simple chart to build out, with the Day of Adjusted Date field hidden in the display (but necessary for the VIT to filter properly).
However, this will display all sports, and we need this chart to not show the sport that has been initially selected (hovered).
Create a parameter pSportToExclude which is a string parameter. For the purpose of demonstration, enter the text Football.
Create a field
Excluded Sport?
[Sport]=[pSportToExclude]
Add this field to the Filter shelf and set to False, and the sport will disappear from the list
Add a reference to this sheet from the tooltip of the schedule viz, this time passing just Day of Adjusted Date as the filter.
Dashboard Interactivity
Hide / Show Sport Selector
When adding the Sport Selector sheet and the Schedule viz to the dashboard, you need to make sure they exist side by side in the same horizontal container.
Then, providing you are using v2021.2, you can set the Sport Selector object to Hide/Show. See this video for help.
Add remove sports
You will need 2 dashboard set actions for this. They should run on ‘menu’, and one will add items to the set, and the other remove
Set the selected sport to exclude
We’ll use a parameter action for this to run on hover and set the pSportToExclude parameter
Stop Sport Selector highlighting
Create a new field Dummy containing the text “Dummy” and add this to the Detail shelf of the Sport Selector viz.
The add a highlight action against this sheet only
Hopefully I’ve ticked off all the core elements here. There was a fair bit going on, and I’m conscious I’ve drafted this blog fairly quickly in comparison. My published viz is here .
Note, there are a couple of elements in my viz that I added which weren’t on the original solution. I’ve chosen not to include in the blog as the images/characters I chose to use didn’t render on Tableau Public. If you download the workbook, you’ll be able to see what my intention was.
I did also create an alternative view ‘heatmap’ style view as well which you can see here.
Donna + DataViz 