For Luke’s first challenge of 2023, he asked us to recreate this visualisation which showed multiple chart types (donut charts and bar charts) within a tabular layout, all built on a single sheet.
As with most challenges, I’m going to first work on building out the calculations needed to provide the data being presented.
Building the calculations
Firstly we need to identify the number of orders, based on unique Order IDs
Total Orders
COUNTD([Order ID])
and then to get the average order value we need
Avg Order Size
SUM([Sales]) / [Total Orders]
format this to $ with 0 dp.
In order to build the donut chart, we will need two measures for each region; one which shows the % number of orders for that region, and one which shows the % number of orders across the remaining regions.
% Orders for Region
[Total Orders]/SUM({FIXED:COUNTD([Order ID])})
Format this to % with 0 dp
and then
% Orders for not Region
1 – [% Orders For Region]
format this to % with 0 dp.
Pop all these out in a table along with Sales formatted to $ with 0 dp, and Sort the Region field based on Total Orders descending.
Building the table view
Add Region to Rows and sort by Total Orders descending.
All the columns are going to be managed by what I refer to as a ‘fake axis’. Double click into the Columns shelf and type in MIN(0). This will create an axis.
Change the mark type to Text and add Total Orders to the Text shelf. Remove all the text from the Tooltip dialog box.
This is our 1st ‘column’ in the table.
Create another column by adding another instance of MIN(0) to Columns.
Change the mark type of this 2nd MIN(0) marks card to Pie Chart and remove Total Orders from the text shelf.
Add Measure Names to the Filters shelf, and ensure only % Orders for Region and % Orders for not Region are selected.
Add Measure Values to the Angle shelf of the pie chart, and add Measure Names to Colour. Adjust the colours to suit and ensure a white border is added (via the Colour shelf options). Again remove all the text in the Tooltip dialog.
Now add another instance of MIN(0) to Columns.
Change the mark type of this marks card to Circle and set the colour to white. Add % Orders for Region to the Label and align centre.
Now make this Min(0) pill to be dual axis and synchronise the axis. Reduce the size of the circle mark type marks card, so some of the pie chart is visible. Remove Tooltip text again.
Add another instance of MIN(0) for our ‘3rd’ column. Set the mark type to Text and add Sales to the Text shelf, and remove the tooltip text.
For the final column, add Avg Order Size to Columns. Change the mark type to bar. Reduce the size and adjust the Colour to suit. Add Avg Order Size to the Label and remove all tooltip text.
Remove all gridlines zero lines and column dividers. Add row dividers at the highest level so you have a divider per row. Uncheck Show Header on the first MIN(0) pill to hide the axes, and right click the Region column label and Hide field labels for rows.
This gives you the viz you need to add to a dashboard. Use a horizontal container with a blank and 4 text boxes positioned above the viz to provide the label heading to the columns.
OR…
you can do what I did, and having not noticed the comment about the textboxes, used further instances of MIN(0) to make every ‘column’ a dual axis so I could use the axis label for the secondary axis displayed at the top to provide the required labels.
I set the mark type of these additional MIN(0) axis to polygon, and ensured no other pills existed on the marks shelf. The tick marks on all the top and bottom axes had to be set to none, and the title on all the bottom axis had to be deleted.
The benefit of using the text boxes option is that you aren’t left with a ‘blank’ space underneath the chart. However wide your axis is at the top to display the labels, will be repeated as ‘blank space’ underneath the chart too (vote up this idea to prevent this).
New year means only one thing – a change to the WorkoutWednesday hashtag! We’re now #WOW2023, and it was Lorna’s turn to kick the next year of challenges off with a focus on the latest Tableau features. This challenge uses the image role function introduced in 2022.4 and the dynamic role visibility feature introduced in 2022.3 (so you need to make sure you’re using at least 2022.4 to complete this).
Setting up the data source
Lorna provided an excel based data sheet which I downloaded. It contains several tabs, but the two you need to relate are Player Stats and Player Positions, relating as Lorna describes on both the Game ID and the Player Name
The data needs to be filtered to just the men’s competition, so I did this by adding a data source filter (right click on the data source -> Edit Data Source Filter) where Comp = Rugby League World Cup. Doing this meant I didn’t have to worry about adding fields to the Filter shelf at all.
Building the scatter plot
For the basic chart, simply add All Run Metres to Columns and Passes to Rows, then add Game ID and Name to the Detail shelf.
We need to identify which player and game is selected when a user clicks, so we need parameters to capture these.
pSelectedGameID
integer parameter defaulted to 0
pSelectedPlayer
string parameter defaulted to nothing/empty string/ ”
Show these parameters on the canvas, and then manually enter Brandon Smith and 21.
We now need calculated fields to reference the parameter values
Is Selected Player
[Name] = [pSelectedPlayer]
Is Selected Game
[Game ID] = [pSelectedGameID]
Both return a boolean True / False value, and we can then determine which combination of player/game each mark represents, which we need in order to colour the marks.
Colour : Scatter
IF [Is Selected Game] AND [Is Selected Player] THEN ‘Both’ ELSEIF [Is Selected Player] THEN ‘Player’ ELSEIF [Is Selected Game] THEN ‘Game’ ELSE ‘Neither’ END
Add this field to the Colour shelf, change the mark type to Circle and adjust the colours accordingly. Manually sort the order of the values so that the light grey ‘Neither’ option is listed last (which means it will always be underneath any other mark).
While not stated in the requirements, the marks for the selected player are larger than the others, so add Is Selected Player to the Size shelf, and adjust the size range to suit.
Adjust the tooltip and name the sheet Scatter.
Building the Bar Chart
On a new sheet, add Player Image and Game ID to Rows and All Run Metres and Passes to Columns. Add Is Selected Player to Filter and set to True. Edit the title of the sheet so it references the pSelectedPlayer parameter, and align centre.
Set the Player Image field to use the actual image of the player stored at the URL, by clicking on the ABC datatype icon to the left of the field in the data pane, and selecting Image Role -> URL
This will change the data type icon and update the view to show the actual player image
Add Is Selected Game to the Colour shelf on the All marks card and adjust accordingly. Remove the tooltips.
We need to show the axis titles at the top, rather than bottom. To do this add another instance of the All Run Metres field to the right of the existing one. Make dual axis and synchronise axis. Repeat with the Passes field by adding another instance to the right of the existing one and making dual axis again.
Set the mark type on the All marks card back to bar.
Right click on the All Run Metres bottom axis, remove the title, and set the tick marks to none. Repeat for the Passes bottom axis.
Right click on the All Run Metres top axis, and just set the tick marks to none. Again repeat for the Passes top axis.
Adjust the font of both axis title (right click axis -> format; I just set to Tableau Book) and then manually decrease the height of the axis.
Finally click the Label button on the All marks card and check the Show mark labels check box.
Then remove all column and row divider lines.
Adding the interactivity
On a dashboard, add a Vertical Layout Container, then add the bar chart into it and then add the scatter plot underneath. Remove the container that contains all the legends and parameters – we don’t need to show any of these. Hide the title of the scatter plot.
Add a dashboard parameter action to set the pSelectedPlayer parameter on select of a circle on the scatter plot. Set this parameter to pass the Name field into the parameter, and when clearing the selection, set the value to empty string/ nothing / ”
Select Player
Add another similar dashboard parameter action, which sets the pSelectedGame parameter in a similar way. This time, the Game ID field is passed into the parameter which is then set to 0 when the selection is cleared.
Select Game
If you test clicking on the scatter plot, you should now see the bar chart data disappear (although some white space about the height of the title remains) and you’ll also see that the circle selected is ‘highlighted’ compared to all the others.
To resolve the highlighting issue, navigate back to the scatter plot sheet, create a new calculated field called Dummy which just contains the string ‘Dummy’. Add this field to the Detail shelf.
Navigate back to the dashboard, and add a dashboard highlight action, that on select on the scatter plot, targets the same sheet on the same dashboard, but only focus on the Dummy field.
Deselect Marks
To make the whole section where the player bar chart is displayed, completely collapse, we need another parameter
pShowPlayerDetail
boolean parameter defaulted to false
We also need a boolean calculated field called
Show Player Detail
TRUE
Add this to the Detail shelf of the Scatter plot sheet.
Then navigate back to the dashboard, and create another parameter dashboard action, which on select of a circle on the scatter plot, sets the pShowPlayerDetail parameter based on the value from the Show Player Detail field. When the selection is cleared, reset the parameter to False.
Show Player Detail
Finally select the bar chart on the dashboard, then click the Layout tab on the left and side, and tick the Control visibility using value check box, and in the drop down select Parameters – > pShowPlayerDetail.
Now when a circle is clicked on, the bar chart section will completely disappear and the scatter plot will fill up the whole space.
Finalise the dashboard by adding the title and legend. My published viz is here.
Kyle Yetter ended the #WOW2022 year with this challenge, to create a waffle chart. I haven’t built one for a while, so needed a quick google to refresh my memory.
You’ll need a ‘template’ data source which defines the grid pattern layout, and what each square represents. Every square represents a percentage point from 1% to 100%, and is positioned in a row numbered from 1 to 10 and a column numbered from 1 to 10. This information is stored in the ‘template’. You can build your own, or you can use the waffle_template.xlsx file I created that is stored here.
Connect to the Waffle_Template excel file and make Row and Column discrete dimensions (just drag them to above the line on the left hand data pane). Then add Row to Rows and Column to Columns and Percent to Text.
Building the waffle
Now connect to the Orders data from the SuperStoreSales data source. In that data source, create a new field
Consumer %
ROUND(SUM(IF [Segment]=’Consumer’ THEN [Sales] END) / SUM({FIXED:SUM([Sales])}),2)
If the Segment is Consumer, get the sum of Sales as a proportion of the total Sales. Round the result to 2 decimal places.
Create similar fields for the other segments
Corporate %
ROUND(SUM(IF [Segment]=’Corporate’ THEN [Sales] END) / SUM({FIXED:SUM([Sales])}),2)
Home Office %
ROUND(SUM(IF [Segment]=’Home Office’ THEN [Sales] END) / SUM({FIXED:SUM([Sales])}),2)
Then in the Waffle_Template datasource, create the following field
Add Colour : Consumer to the Colour shelf, and adjust colours. If you get any warning messages about blending, just acknowledge.
To make the blocks, double click in the Columns shelf and type in MIN(1). Change the mark type to bar. Edit the MIN(1) axis, and fix it from 0 to 1. Remove the Percent field from the Text shelf.
Set the row and column dividers to be thick white lines, and hide the column and row headings and axis (uncheck Show Header). Uncheck Show Tooltip on the Tooltip shelf.
Name this sheet Consumer.
Then duplicate this sheet, and replace the Colour : Consumer pill on the Colour shelf with the Colour : Corporate pill. Adjust colours to suit. Name the sheet Corporate.
Then duplicate again, and repeat the process for the Colour : Home Office pill, naming the sheet Home Office.
Building the KPIs
On a new sheet, using the Orders – SuperStoreSales data source add Segment to Columns.
Create a new field
Percent of Total
ROUND(SUM([Sales]) / TOTAL(SUM([Sales])),2)
format this to percent with 0 dp and add this to the Text shelf.
Create a new field
Segment UPPER
UPPER([Segment])
and add this to the Text shelf.
Format the text and align centrally
Remove the row dividers, the Segment column labels, and uncheck Show Tooltips from the Tooltip shelf. Name the sheet BANs.
Creating the dashboard
Use a horizontal container to position the three waffle charts in. Ensure each chart is set to fit entire view. Hide the titles, and from the context menu on the container, distribute the contents evenly. Use padding on the objects to create more white space.
Add the BANs object underneath, remove the title and set to fit entire view. Adjust the heights and widths as required to ensure the waffles are spaced evenly and are square, and the BANs line up underneath.
It was Sean’s final #WOW2022 challenge of the year, and he set this task to provide alternative options for visualising time series across dimensions with high cardinality.
I had a play with Sean’s viz before I started tackling the challenge and noticed the following behaviour in addition to the requirements listed, which may or may not have been intentional.
When the cardinality of the dimension to display was more than 1 higher than the top n parameter, and the Show Others? option was set to Group By, the sales value to display was based on average sales rather than sum (as indicated in the requirements), and all the values not in the top n, were grouped under an ‘Other (Avg)’ label. But if the cardinality of the dimension was only 1 more than the top n parameter (so there was essentially only 1 value within ‘other’), then this value would display as itself (ie not labelled ‘other’) and the sales values would be summed rather than averaged (eg if the lines were to be split based on Ship Mode, and the top n was set to top 3 and Show Others? set to Group By, all four ship modes would display with the sum of sales rather than average).
This observation meant some of the calculations were slightly more complex than what I thought they would need to be initially, as I had to build in logic based on the number of values within a dimension.
So with that understood, let’s build the calcs…
Defining the calculations
Firstly we need some parameters
pDimensionToDisplay
String parameter defaulted to Subcategory, with the list of possible dimensions to split the chart by
pTop
integer parameter defaulted to 5
pShowOthers
I used an integer parameter with values 0 and 1 which I ‘aliased’ to the relevant values, defaulted to ‘Group Others’
I then needed to determine which dimension to be used based on the selected from the pDimensionToDisplay parameter.
DimensionSelected
CASE [pDimensionToDisplay] WHEN ‘Subcategory’ THEN [Sub-Category] WHEN ‘State’ THEN [State/Province] WHEN ‘Ship Mode’ THEN [Ship Mode] WHEN ‘Segment’ THEN [Segment] ELSE ‘All’ END
From this, I could then use a Set to determine which of the values would be in the top n. Create a set of off DimensionSelected (right click -> Create -> Set)
Dimension Selected Set
select the Top tab, and create set based on the pTop parameter of Sales
To check this is working as expected, on a sheet, add DimensionSelected and Dimension Selected Set to Rows, add Sales to Text and sort by Sales descending. Show the pTop and pDimensionToDisplay parameters. Change the parameters and observe the results behave as expected.
Now we need to determine how many values are not in the set, ie, how many of the DimensionSelected values display as ‘Out’ in the above image.
Count Non-Set Items
{FIXED:COUNTD(IF NOT([Dimension Selected Set]) THEN [DimensionSelected] END)}
If the entry is not in the set, then return the entry, and the count the number of distinct entries we have. Using the FIXED level of detail calculation, wraps the value across every row.
Now this is understood, we need to work out whether we want to group the ‘non-set’ values under ‘other’
Dimension To Display
IF [pShowOthers]=0 AND NOT([Dimension Selected Set]) AND [Count Non-Set Items]>1 THEN ‘Other (Avg)’ ELSE [DimensionSelected] END
If we’re opting to ‘group’ the values, and the entry isn’t in the set, and we’ve got more than 1 entry that isn’t in the set, then we can label as ‘Other (Avg)’, otherwise, we just want the dimension value.
We use similar logic to determine whether to display the SUM or AVG Sales.
Sales to Display
IF [pShowOthers]=0 AND SUM([Count Non-Set Items]) > 1 THEN AVG([Sales]) ELSE SUM([Sales]) END
Format this to $ with 0 dp.
We can then remove the DimensionSelected from our view, and test the behaviour, switching the pShowOthers parameter
Building the core viz
On a new sheet, add Order Date set to continuous quarters (green pill) to Columns and Sales To Display to Rows and Dimension To Display to Label. Show all the parameters.
Create a new field
Colour
[pShowOthers]=0 AND NOT([Dimension Selected Set]) AND [Count Non-Set Items]>1
add add to Colour shelf, and set colours accordingly.
Add Sub-Category, Ship Mode, State and Segment to the Filter shelf. Show them all, and then test the behaviour is as expected.
Extending the date axis
The date axis in the solution goes beyond 2022 Q4, and means the labels have a bit more ‘breathing space’. To extend the axis, click on Order Date pill in the Columns, and select Extend Date Range -> 6 months.
Then double click into the space next to the Sales To Display pill on the Rows and type MIN(0). This will create a second axis, and the date axis should now display to 2023 Q2
On the MIN(0) marks card, remove the Colour and Dimension To Display pills. Reduce the Size to as small as possible, and set the Colour opacity to 0%. Set the chart to dual axis, and synchronise the axis.
Finally tidy up the chart – hide the right hand axis, remove the title from the date axis, change the title on the left axis. Remove all row and column divider lines, but ensure the axis rulers are displayed. Title the viz referencing the pDimensionToDisplay parameter.
Building the dashboard
When putting the dashboard together, you need to ensure that the filters, parameters and main viz are all contained within a vertical container to ensure the viz ‘fills up’ the space when the controls section is collapsed.
The controls section itself is also a vertical container, which consists of 2 horizontal containers, one of which contains all the filters, and one which contains the parameters.
The layout tab shows how I managed this (I also like to rename the objects to keep better control).
The green Base vertical container is the first container. This essentially contains 3 ‘rows’ – the title, the User Controls vertical container and the viz itself.
The User Controls vertical container, then contains 2 rows itself – the Filters horizontal container (which has a pale grey background colour) and the Fine Tune horizontal container (which has a slightly darker grey background colour).
The User Controls vertical container is then set to ‘hide/show’ by selecting the whole container (click on the object in the item hierarchy on the left), and then selecting Add Show/Hide Button from the context menu. Adjust the button settings as required (I just altered the text displayed on hover) and then position accordingly.
It can take a bit of trial and error to get this right, and to get your containers working as expected. My published viz is here.
For her final challenge of #WOW2022, Erica set this interesting challenge; to show how the sales of the top n Products within each Sub-Category compared against the total sales. An added twist was to flip the display between actual sales, and % of total.
It did take me a short while to get my starting point. When I’ve worked with the concept of proportional brushing before, it’s typically involved set actions and one viz driving the interactivity on another viz via a dashboard action. I didn’t have this. I’ve obviously come up with a solution, but I’m not sure if it’s overly complicated… it doesn’t seem it, but for some reason I feel it could have been simpler than I’ve made it. Only time will tell as I check out other solutions.
My solution involves table calcs, so as I do with many challenges, I’ll start by building out all the data I need in tabular form.
Defining the calculated fields
Start off by adding Sub-Category to Rows, Product Name to Rows and Sales into Text and sort descending. We want to identify the Product Names in the top n, so lets first rank the products per sub-category. Create a calculated field
Sales Rank
RANK_UNIQUE(SUM([Sales]))
Make it a discrete field, then add it to Rows between Sub-Category and Product Name. Set the table calculation to compute using Product Name.
The rows should be numbered from 1 upwards, and restart at 1 when the Sub-Category changes
We want to get the sum of the sales for all those products in the top n. We’ll use a parameter to identify the n
pTop
Integer parameter defaulted to 10
Then we’ll use another table calculation to get the sum of the sales in that top n
Sales in Top n
WINDOW_SUM(IF [Sales Rank]<=[pTop] THEN SUM([Sales]) END)
If the Sales Rank is less than or equal to the top parameter, then get the Sales for that row, then sum all the sales values up together.
Show the parameter on screen, and add Sales in Top n into the table, setting the table calc to once again compute using by Product Name.
You should see that the sum of the sales for the rows marked 1-10 should equate to the value in the Sales in Top n column for the Sub-Category
We also need to get the total sales for each Sub-Category, which we can do with
Sub Cat Sales
{FIXED [Sub-Category]: SUM([Sales])}
Format to $ with 0 dp and displayed in k.
With this , we can also determine the proportion of sales
Top n % of Total
[Sales in Top n]/SUM([Sub Cat Sales])
Format this to a % with 0 dp.
Add both these fields to the table, making sure the table calcs are set to compute using Product Name.
So now we’ve got the base fields that form the building blocks for the viz. But we have this added functionality where we want to show either actual values (ie Sales in Topn and Sub Cat Sales), or we want to show the Top n % of Total compared to the ‘whole’ ie 1.
To manage this, we first need the parameter to control the decision
pView
integer list parameter set to 0 or 1, with appropriate display values. and defaulted to Sales Value
We can then build
Measure 1 to Display
CASE [pView] WHEN 0 THEN [Sub Cat Sales] ELSE 1 END
and
Measure 2 to Display
CASE [pView] WHEN 0 THEN [Sales in Top n] ELSE [Top n % of Total] END
Pop these into the table and show the pView parameter. Test changing the parameter and see the results. Don’t worry that the % shows 0.. you can format the Measure 2 to Display field to show 2 dp if you want to see the data really changing.
Building the bar chart
On a new sheet, add Sub-Category to Rows, Product Name to Detail and Measure 1 to Display to Columns.
Drag the Measure 2 to Display field onto the Measue 1 to Display axis, and release when the 2 green columns symbol appears
Set the Measure 2 to Display field to compute using Product Name, then move Measure Names from Rows to Colour, and adjust the colours. Ensure Measure 2 to Display is listed first in the colour legend, so the marks are on top. Set stack marks to off (Analysis menu -> Stack marks -> off) to make the bars a single block, rather than being split per Product.
Switch the view parameter to change the display and verify it behaves as expected
Add the Top n % of Total field to Rows and change to be discrete (blue). Ensure the field is set to compute by Product Name still.
Create a new field
Label to Display
CASE [pView] WHEN 0 THEN [Sub Cat Sales] ELSE NULL END
Format this to $ with 0 dp and displayed in k. Add this to the Label shelf
Add Sub Cat Sales to the Tooltip shelf and then adjust the tooltip to match.
The final step (other than formatting) is to apply the sorting. Create a new field
Sort
CASE [pView] WHEN 0 THEN SUM([Sub Cat Sales]) * -1
ELSE [Top n % of Total] *-1 END
Make this field discrete and then add to Rows in front of the Sub-Category pill. You should now find the bars are being sorted descended depending on the view being displayed
Finally, tidy everything up – Uncheck show header on both the Sort and Measure Values pills. Hide field labels for rows to remove the column headings. Change the font size/format of the first two columns, and adjust the colour of the bar label. Remove all gridlines, column banding etc.
Finally add to a dashboard. I used a horizontal container, text boxes and padding to build up the parameter selections at the top. My published viz is here.
In his final challenge for 2022, Luke set this challenge asking us to recreate this pie chart. Although not mentioned in the challenge text, there was a hint on the splash page that map layers would be required.
I’ve only really used map layers in other #WOW challenges, and they actually involve maps. This challenge was obviously a bit different – utilising a functionality built for one purpose in an entirely different way. I remembered when map layers were first released there was a big buzz about the potential possibilities, and had seen some examples, but I’d never gotten round to trying out for myself, so this was the perfect opportunity (and one of the many plus points as to why I love doing #WOW challenges).
So where to start… good question. If you read up on the official Tableau KLs relating to map layers, it’s all about geography, and while the data source does have geographic data (State, City etc), they aren’t relevant in this case. In my ‘googling’ I found the following resources of use
The first 2 blogs helped me understand the need for the use of the MAKEPOINT function, Sam Parson’s Pies & Doughnuts viz helped me understand the calculation I’d need for the MAKEPOINT function, and the final blog post really helped with putting in all together.
Feel free to ignore the rest of this blog and use the above to help you out 🙂
Building the first map layer
The first step is to create the geometry field we need to base this off of.
Zero
MAKEPOINT(0,0)
Double click this field, and it will automatically add the point centrally onto a map with Longitude and Latitude fields automatically generated too.
This is a key step in getting things started and enabling the use of map layers which we’re going to utilise.
Add Segment to Colour and adjust the colours. Change the mark type to pie chart and add Sales to angle. Increase the size to be as large as possible.
However the size isn’t as big as we need. To increase further use Ctrl-Shift-B (windows) or Cmd-Shift-B (mac) to increase the size further (Ctrl-B / Cmd-B) to reduce. This trick I found in the Interworks blog above. All Tableau key shortcuts are listed here.
Add Segment to the Label shelf. This completes our lowest map layer.
Building the second map layer
Drag Zero onto the map canvas, and drop it over the Add a Marks layer section that displays. This will add a second marks card called Zero(2).
On the marks card that is named Zero, rename it to Outer Pie.
On the marks card named Zero (2) rename to White Circle. Change the mark type to Circle, change the Colour to white and increase the size to leave a narrow border of the coloured pie underneath.
Building the third map layer
Drag another instance of Zero onto the canvas and add another marks layer. Rename Zero (3) to Inner Pie. Change the mark type to Pie chart and add Segment to Colour and Sales to angle. Increase the Size so it’s just smaller than the white circle. Change the opacity of the colour to 70% and add a white border (Colour shelf).
Adding the labels in the pie chart
The simplest way to do this is just to label the inner pie chart with the required fields and then manually move the labels from outside the pie to the desired location. However if your data changed in some way, eg the proportion of the slices changed, the labels may not be where you wanted without further tweaks.
So instead I’ve added a 4th map layer.
Add Zero once again to the sheet and add a marks layer. Rename this marks card to Labels-Inner Pie. Change mark type to Pie chart and add Segment to Detail, Sales to Angle and Sales to Label. Create a new calculated field
Pct
SUM([Sales]) / TOTAL(SUM([Sales]))
Format to % with 0 dp and and add to Label. Adjust the fonts of the labels so the Sales value is larger.
Increase the size of the pie chart so the labels are positioned ‘nicely’ within the segments of the Inner pie
Reduce the opacity of the ‘label’ pie chart to 0% and set the mark layer to be disabled
Finishing up
Adjust the tooltips to display as required (you’ll need to add Pct to the Tooltip shelf on both the Outer and Inner Pie mark cards).
Then remove the map background via the Map menu -> Background Maps -> None. Hide all axis and remove all gridlines/zero lines/row/columns dividers. You should now be left with a ‘clean’ pie chart which can be added to a dashboard.
For this week’s #WorkoutWednesday challenge, Lorna revisited a challenge set by Ann Jackson in 2019 which I completed and blogged about here. In that challenge we were using new navigational features introduced in v2018.3. In this recreation, we’re making use of the dynamic zone visibility feature introduced in v2022.3 (so you’ll need at least that version of Tableau to progress).
I used 5 worksheets to build this viz and, as required, just 1 dashboard. 4 of the worksheets relate to the KPI blocks, and the other for the bar chart.
Building the KPI blocks
I started by creating new measures
Count Customers
CountD([Customer Name])
Count Orders
COUNTD([Order ID])
Count Cities
COUNTD([City])
Count Products
COUNTD([Product Name])
On a new sheet, add Count Customers to Text. Then add Measure Names to Filter and filter to just show the Count Customers measure. Then add Measure Values to Text and Measure Names to Text. Remove the original Count Customers from Text. Set the view to Entire View and then centre align and format the text. Set the mark type to Square and increase the Size as large as possible. Set the Colour to the relevant colour and adjust the font to match mark colour. Remove any tooltips from showing by unchecking show tooltips on the Tooltip shelf. Name the sheet Customers.
Repeat the process for creating sheets for City, Products and Orders. You may find you need to format some of the measures to have no decimal places.
Finally add Aliases to Measure Names to change the display of the measure from being Count XXXX to just XXXX (right click on Measure Names in the left hand data pane – > Aliases
Note you could possibly have named the measures just Orders, Cities etc to start with, though I think a measure called Orders already existed, so I chose this way to be consistent.
Building the bar chart
We’re going to use Dimension swapping for this chart – that is build a single chart but use a parameter to determine which dimension needs to be displayed.
pDimension
String parameter which is hardcoded initially to the word Customers.
Create a new field which will determine which dimension to show
Dimension to Display
CASE [pDimension] WHEN ‘Orders’ THEN [Order ID] WHEN ‘Customers’ THEN [Customer Name] WHEN ‘Products’ THEN [Product Name] WHEN ‘Cities’ THEN [City] END
Note the names in the CASE statement to be stored in the parameter need to match the aliases we defined above.
On a new sheet, add Dimension to Display to Rows and Sales to Columns and sort by Sales descending. Add both Dimension to Display and Sales to Label. Format the label as required and align left. You may need to widen the rows to see the text.
Add pDimension to Colour and set the colour for the Customers dimension. Set the font to match mark colour.
Remove all axis and header columns (uncheck show header), all gridlines and row/column dividers. Add a title to the chart which references the pDimension field.
Now update the pDimension parameter to the word Orders, and set the colour. Repeat for Cities and Products.
Building the dashboard
Add the objects on to the dashaboard in such a way that you have a Vertical container that contains a Horizontal container with Customers and Orders side by side, then another Horizontal container underneath (still within the Vertical container), with City and Products side by side, and then finally add the Bar chart underneath. Set the 4 KPI block sheets to fit entire view and the bar chart to fit width. Try not to be tempted to adjust and heights of widths of objects manually on the dashboard, as that can affect things when we try to collapse later. You’ve probably got something like this:
Add a parameter action to drive the setting of the pDimension parameter
Select KPI
On select of any of the KPI block sheets, update the pDiemsion parameter passing through Measure Names. When selection is cleared, set the value to <empty string>/nothing
If you manually set the pDimension parameter to empty, your display should look like
Remove the titles from displaying from the KPI block sheets.
Hiding and showing the relevant sheets
To achieve this, we’re making use of Dynamic zone visibility functionality. For this we need some boolean fields to be created.
Crate a new calculated field
Dimension Selected
[pDimension]<>””
Similarly, create a new calculated field
Dimension Not Selected
[pDimension]=””
Back on the dashboard, select the Customers KPI block sheet (easiest way is to click on the object in the Item hierarchy pane at the bottom of the layout tab on the left hand side, as you may have trouble selecting on the dashboard itself due to the interactivity added). Once the sheet is selected it will have a grey border around it. On the layout tab on the left, select the Control visibility using value checkbox and select the Dimension Not Selected field.
Repeat this against the other 3 KPI block sheets.
Then for the bar sheet, do the same, except this time, select the Dimension Selected field instead. As soon as you do this, the bar should disappear, but reappear once you click on a KPI.
A colourful #WOW2022 challenge this week set by Kyle Yetter and using his favourite data – Baseball. Let’s jump straight in.
Building the required calculations
First up we need to calculate the core measure the viz is based on – % of wins
Win %
SUM([Wins])/SUM([Games])
I formatted this to 3 decimal places, then applied a custom number format to remove the leading 0 (custom number format looks like ,##.000;-#,##.000).
We also need to know the number of losses as this is part of the tooltip.
Losses
SUM([Games]) – SUM([Wins])
Let’s pop all these out into a table (I formatted all the whole numbers to display without any decimal places).
The viz however isn’t plotting the actual Win%, it’s plotting the difference from 50% (or 0.5), so values less than 50% are negative and those above are positive.
Plot Postion
[Win %] – 0.5
And we also need to know whether the Win% is above 50% or not
Above 50%
[Win %]>0.5
Pop these out onto the table too
The viz also displays the overall Win% for each team, and also uses this to sort the data. As it is used for sorting, we need to use an LoD calculation (rather than a table calculation).
for each team, get the total wins, and divide by the total games for the team. Format this to 3 dp with no leading 0 as before.
pop this into the view (you’ll see it’s the same value for each row for a single team), and then apply a Sort on the Team field to sort descending by the Overeall Win% LOD.
Now we have the data sorted, we can create the fields needed to build the trellis chart.
I have already blogged challenges relating to trellis charts / small multiples (see here) which in turn reference other blogs in the community, so I’m not going to go into all the details. We just need to build two calculated fields to identify which row and which column each Team will sit in. The table is fixed at 6 columns wide as the data wea re using is static. Some solutions work with a more dynamic layout depending on how many entities you need to display. We’re keeping things simpler.
Cols
FLOAT(INT((INDEX()-1)%6))
Rows
FLOAT(INT((INDEX()-1)/6))
Add both these fields to the table as discrete dimensions (blue pills), and as they are both table calculations, set them both to Compute Using – > Team.
Building the Core Viz
On a new sheet, add Cols to Columns as discrete dimension, Rows to Rows as discrete dimension and Team to Detail. Set both Rows and Cols to Compute Using Team.
Add Year as continuous (green) pill to Columns and Plot Position to Rows and change the mark type to Bar and reduce the size. Sort the Team field based on Overall Win% LOD descending.
Add Wins, Losses, and Win% to the Tooltip shelf and adjust the tooltip to display as required. Add Above 50% to the Colour shelf (you may need to readjust the size). Leave the colours as they are for now – we’ll deal with this later.
Adding the labels
Create a new calculated field
Dummy Plot
FLOAT(IF [Year]=2000 OR Year = 2020 THEN 0.35 END)
This is basically going to position a mark at height 0.35 but only if the year is either 2000 or 2020. These values were all just based on a bit of trial and error as to what worked to get the desired result.
Also create a field
LABEL:Team
IF [Year]=2000 THEN [Team] END
and
LABEL:Win%
IF [Year]=2020 THEN [Overall Win % LOD] END
format this to 3dp and exclude the leading 0.
Add DummyPlot onto Rows and change the mark type of this measure to circle. Amend the Tooltip of this marks card so it’s empty.
Add LABEL:TEAM and LABEL:Win% to the Label shelf, and adjust the label so both fields sit side by side (only 1 value will only ever actually display). Adjust the table calculation of both the Rows and Cols pills so they now compute using both the Team and the LABEL:Team fields.
Adjust the alignment of the labels so they are positioned bottom centre. Set the font colour to match mark colour and bold.
Then reduce the size of the circle mark to as small as possible, reduce the opacity of the mark colour to 0.
Now make the chart dual axis and synchronise the axis. Remove the Measure Names field that has automatically been added to the All marks card.
Hide all the headers and axis (uncheck Show Header), remove all grid lines, zero line, axis rulers.
Hide the null indicator (bottom right).
Colouring by Team
Copy the colour palette text Kyle provided into your preferences.tps file (usually located in the My Tableau Repository directory). For more information on working with custom colour palettes see this Tableau help article.
You’ll need to save your workbook and re-open for the new palette to be available for use.
In order to prevent having to manually set all the colours (and believe me you don’t want to do this!), perform the following steps in order
Add Team to also be on the Colour shelf. Click on the 3 dots (…) that are to the left of the Team pill on the All marks card, and change it to Colour. This means there are now 2 fields on colour. Move the Team field so it is listed above the Above 50% pill. This means your colour legend should be listed as <Team>, <True|False>
Adjust the Sort of the Above 50% pill, so it is manually sorted to list True before False.
Now change the Sort on the Team field so it is sorted alphabetically ascending instead. This will cause the viz to change its sort order, but don’t worry for now. It also changes the list on the colour legend, so ARI, True is listed first then ARI, False etc.
Now edit the Colour Legend and select the new MLB Team Colours palette we added. Click the Assign Palette button to automatically assign the colours. As we’ve made sure the entries listed are in the right order, they should get the correct colours.
Change the Sort on the Team field back to be based on Overall Win% LOD descending
And that should be it. You can now add the viz to a dashboard and publish. My published version is here.
In a break from the norm, this isn’t a #WorkoutWednesday solution guide, but it is a solution guide of sorts. This time I’m going to share how I put together the core section of my “My Tableau Journey” viz. This is a viz I put together to introduce myself at the virtual TUGs I’ve recently presented at. Rather than talk through a slide deck, I figured showcasing myself through a viz was much more appropriate.
The #datafam Tableau Community is all about sharing, supporting and inspiring others. Sarah Burnett’s CV viz heavily inspired my core layout, which I’ve attributed both on the viz itself (bottom left) and via the inspiration link on the Tableau Public page. Sarah even encouraged ‘stealing like an artist’, by providing a link to the Google Sheet she’d used to store the data for her viz. So that’s exactly what I did, I took her file from google, downloaded her dashboard, connected the two, then started modifying the data to meet my needs.
The data
Sarah’s Google Sheet contains multiple tabs, where each tab corresponds to a section of her viz. In my viz I made use of the following tabs only
Overview
Community
Roles
Contact
and I naturally changed to data to be relevant to me.
I also created a new sheet labelled Milestones which provides the data I need for the central part of my viz – the ‘dangling baubles’.
This contains the following fields
Milestone ID – just a unique identifier for each row
Start Date – the date the milestone started
End Date – the date the milestone ended, but in most cases this just the same as the start date
Type – just a classification I thought I might use, but didn’t
Milestone – short description of the milestone
Milestone Wrapped – To be displayed as the label on the viz, so the same text but just with some explicit carriage returns so the text wraps
Details – Further info that is displayed in a Viz in Tooltip. This also has explicit carriage returns in places so the text displays as I wanted.
URL – a link to a web page for further info, but again actually unused in the end
Random – used to determine where the ‘bauble’ will display. Nothing clever about this, simply a number entered via trial and error to provide a ‘nice’ looking display.
I’m not going to document the build of the whole viz. Instead I’m going to focus on the main section only – The ‘dangling baubles’.
This is actually split into 2 sheets – one to display the timeline of my job history, and the other to display the baubles.
Job History Timeline
This uses the Roles sheet as the data source.
Now, for the first 20 years of my career, I was at the same company, but Tableau only featured in the last 8 of it. So the Start Date I recorded in the data set against RM is based on the Tableau part of it. This meant that I didn’t have a long period of time with no milestone activity.
Add Start Date to Columns and set to the continuous Day level (green pill). This level of date granularity works for me as I set my milestone start dates to a day level which could be mid-month. If you set all your milestones to start on 1st of month, then a month level date granularity would probably work fine instead.
Add Role ID to Rows.
I created a parameter to use to control what date I want to use as the maximum end date in this viz. This is essentially a hardcoded value and as and when I add more milestones into the data, I’ll adjust the parameter. If I used TODAY() (or a calculation based off of that function) which I typically would do if I was developing a business dashboard, then this viz would eventually over time start to squash up and not look as I hoped.
pTimelineEnd
date parameter defaulted to 31 Dec 2022 (I chose this as the default based on the spread of the data I currently have in the data set I’m working with).
I’m obviously still at my current job, so I added a future end date into the source data set. I don’t want to use this value, so created
Revised End Date
IF [End Date]<= [pTimelineEnd] THEN [End Date] ELSE [pTimelineEnd] END
With this, I was then able to create
Duration
DATEDIFF(‘day’,[Start Date], [Revised End Date])
which I could then add to the Size shelf of my viz. I then manually changed the colour of the marks, set the border to none, and reduced the size to make the gantt bars thinner.
To add the circles, I added Revised End Date (set to the continuous Day level again) to Columns. Make the chart dual axis and synchronise the axis. Remove the Measure Names field from the All marks card, that was automatically added.
On the Revised End Date marks card, remove the Duration field from Size, and change the mark type to shape and manually increase the size of the mark. Add Current field to Shape.
While one of the default shape palettes does contain filled shapes, I also want to use an ‘open’ shape that doesn’t show the other mark underneath. As a result I ended up creating my own circle shapes and added as custom shapes to achieve this. The ‘open’ circle shape is actually a white filled circle with an aqua border.
Next I want to create a label for each row. Add Company to the Label shelf of the Start Date marks card. Expand the row height a bit to give a bit of room.
Now I want to show the label on top, and left aligned to the edge of the bar. Adjusting the label alignment doesn’t help. Left align and top puts it on the left hand side 😦 Left align also extends the left hand part of the time line to start from 2011.
I pondered about this for a bit. I was vaguely aware of ways I could achieve this, but then I just decided I could manually move the label. This isn’t something I do often, as usually I’m building a business dashboard where the data can change which in turn can change how the viz is displaying. In this instance, the data in this viz isn’t going to change without me controlling that change. So why spend the time building something complex when it isn’t needed….?
Make sure the label is right aligned (otherwise the left hand side of you date axis will extend further back than you might want), then just click on the label text you want to move and when the cursor changes to a crosshatch, click and drag the text to re-position (you may need to adjust the row height some more).
Hide the Row ID column and remove row & column dividers. Hide all tooltips. The final viz does make use of Viz in Tooltips but I won’t walkthrough that part at this point. I’m going to leave the axis on display for now. Name the sheet Jobs.
Milestones
This viz uses the Milestones sheet as its data source.
Add Start Date to Columns and set to the continuous Day level (green pill). Add Random to Rows. Change the mark type to Circle and change the colour and set the border to None..
Add another instance of Start Date to Columns and set to dual axis and synchronise the axis. Change the mark type to Bar, change the colour and set the border to None. Add Milestone ID to the Detail shelf of the All marks card to ensure we have 1 mark showing for every ‘row’ of data (some milestones happen on the same date).
Edit the Random y-xis, and check the Reversed checkbox to invert the axis.
The ‘dangling baubles’ are starting to come to life 🙂
Now when I add this to a dashboard, I will want the Jobs viz to display on top of this one, in such a way that the vertical lines look to connect to the horizontal lines on the jobs timeline. As a consequence, I need to add some extra ‘room’ at the top of this chart. so none of the baubles are too close to the 0 line.
Rather than adjust all the values in the data source, I created a parameter
pAdjustor
Integer parameter defaulted to 10
I then created
Random + Adjustor
[Random] + [pAdjustor]
and replaced the Random field on the viz with this new field. (I had to reinvert the axis again) By using the parameter I can easily move the marks down the viz.
Hide the Random + Adjustor field and remove all gridlines, zero lines, axis and row/column dividers. On the Circle marks card, add Milestone Wrapped to Label and align to the bottom. Remove all the tooltips for now.
It’s at this point where you may need to start fiddling with how the labels get wrapped, and what the value of the Random field should be for a particular entry. So this can all be a bit trial and error.
From both y-axis, remove the axis titles. We need the years on the top axis to show, and can’t hide the bottom axis without removing the top one too. Instead, edit the bottom axis and set tick marks to None
On the top axis, we also only want to show years every 5 years, so edit the axis, and set the tick marks to be fixed to start on 01 Jan 2012 and increment every 5 years.
Adjust the height of the axis to be as narrow as possible while displaying the years. Name the sheet milestones.
Putting the vizzes together
On the dashboard, add the Milestones sheet. Make sure it fits the entire view, the title is removed, and any left/right, top/bottom padding is applied to match the rest of your dashboard. In my case I set the top & bottom outer padding to 0 and the left & right padding to 10.
Now click the Floating button and then add the Jobs sheet onto the viz. Remove the title, set the view to fit entire view and manually set the width of the viz to be width of dashboard – (left padding + right padding of milestones viz). In my case the dashboard width is 900, and the padding totalled 20, so I set the width to 880. Set the x position to be the same as the left padding (in my case 10). Find the shape legend tile that has been added and remove if from the dashboard.
Ok it’s getting there, but still a bit to be done. One of the things you may notice is the timeline scales aren’t lining up. between the two vizzes, and one is scaling much more into the future than the other.
We ultimately don’t want to show any axis for the Jobs viz, but we do want to make sure it tallies closely with the Milestones one. I found the easiest way to do this was to ensure all the axis were set to be exactly the same, and had a fixed start & end date. Again this is something I can adjust as and when additional data gets added into the viz.
So first, go to the Milestones viz and fix the top axis to start 01 Oct 2011 and end on 01 March 2023.
Then, go to the Jobs sheet and
Remove the title from the bottom axis
Set the axis tick marks to None
Remove the title from the top axis
Fix the axis to start 01 Oct 2011 and end on 01 March 2023
Set the axis tick marks to start on 01 Jan 2012 and increment every 5 years
Looking back at the dashboard, we can see things are much more closely aligned. It’s still not perfect, but to be honest I’m not sure where the mis-alignment is now coming from. For the purposes of my viz, this is ‘good enough’.
We can now hide both the axis on the Jobs sheet. We also want to set the background on the worksheet from white to None, which makes this viz transparent.
Back on the dashboard, you should now see the Milestone viz showing through the Jobs viz. Make further adjustments to the Jobs object – reduce the height a bit, shift it up a bit (use the y position on the layout tab to help if need be).
Once you’re happy with the positioning of the objects, and the size of all the marks, then we want to hide the vertical ‘threads’ from the milestone chart that are appearing above the horizontal gantt bars on the job timeline.
I simply use carefully placed floating blank objects to do this. Again, another example of a technique I wouldn’t typically use if I was building a business dashboard. Place the blank objects, set the background to white, then change the floating order and ‘send backwards’ so they are behind the Jobs viz but in front of the Milestones viz.
Finally, I wanted to show that my time at RM extended before 2012, so I added a floating text box just containing … onto the viz.
And that’s it. With the exception of a couple of custom shapes which needed to be built in another tool (I tend to use drawing shapes in Powerpoint), the viz has all been built in Tableau, and there’s nothing overly complex in getting the desired display. I’m using several instances of ‘hard coding’ (fixing the axis, manually positioning labels, using floating blanks to hide stuff), that I wouldn’t advocate if I was building a business dashboard where the data could update without the viz being edited too. But for the purposes of a mainly ‘static’ viz, I see no need to complicate things.
Feel free to have a go yourself using your own data, but please remember to give me credit if you share and publish.
I referenced this Tableau blog post and downloaded the HexmapPlots excel file included. I then used a relationship to ‘join’ the Sample – Superstore excel file I was using with the HexmapPlots file, joining on State/Province= State
Since I had the other blog post already open, I then followed the steps included to start building the map.
Building the hex map
Add Column to Columns and aggregate to AVG, and add Row to Rows and also aggregate to AVG. Add State/Province to Detail. Edit the Rows axis and set to be Reversed.
Note – it’s possible you may have extra States showing. As I’m writing I’ve realised I’m rebuilding against an extracted data source that has a filter I originally applied as a global filter, which has now been included in the extract. So you may need to add State/Province to the Filter shelf, and set to exclude NULL and District of Colombia. This filter will need to be applied to all sheets you build.
Change the mark type to Shape and select a Hex shape. I already have a palette full of Hex shapes, but the blog post provides a shape to use and add as a custom shape if you haven’t got one. Increase the size of the marks.
Create a new field
Profit Ratio
SUM([Profit]) / Sum([Sales])
format this to % with 1 dp, and then add to the Colour shelf.
Now add a second instance of Row to the Rows shelf and set to AVG again. Set the mark type of this marks card to Map. Remove the Profit Ratio field from Colour on this card too. Assuming your Map location is set to USA, you should have State outlines depicted (Map -> Edit Location).
Set the chart to be dual axis and synchronise the axis. The State shape axis should now be inverted. Independently adjust the sizes of the Hex shape and the state shapes, so the states sit inside the hexagons.
Right click on the right axis and move marks to back. The adjust the Colour of the hex shapes so its around 85% transparent.
Now adjust the Tooltip on the hex marks to match the requirement.
To fill-out the available area, I also chose to fix both the axis (right click axis -> edit axis). The Column x-axis I set to range from 1 – 12, and the Row y-axis, I set to range from -0.9 – 9. Then hide all axis, and remove all row/column gridlines, divider lines, axis lines and zero lines. Hide the 10 unknown indicator, and set the background colour of the whole worksheet to a pale grey.
Building the Line Chart
This is super simple, Tableau 101 🙂
Add Order Date to Columns and set to be a continuous month (green pill showing month-year). Add Sales to Rows. Change the colour of the line to grey. Hide the x-axis and remove all gridlines, dividers, axis lines and zero lines. Set the background colour of the worksheet to None (ie transparent). Update the tooltip.
Building the Scatter Plot
Add Sales to Columns, and Profit to Rows. Add State/Province to Detail shelf and add Profit Ratio to Colour. Change the mark type to circle. Remove all gridlines, row column dividers and axis rulers. Only the zero lines should remain. Adjust the tooltip and set the worksheet background to None (transparent).
Putting it all together
Create a dashboard and set the size as stated. Set the background of the dashboard to pale grey (Dashboard – > Format).
Add the Hex map, and hide the title. Click on the Profit Ratio legend object and set to be floating. Then remove the right hand vertical container. Move the Profit Ratio legend to a suitable location.
Then add a text box as a floating object and use it to create the title. Add both the trend line and the scatter plot charts as floating objects without titles. Just position them as required. You can always use gridlines (Dashboard -> Show Grid) to help you line things up.
Finally add the interactivity.
Add a highlight dashboard action which highlights the hex map and the scatter plot when either of the other is selected ‘on hover’, and just targets the State/Province field.
Then add a Filter action which on hover of the Trend chart, targets the remaining charts.
And hopefully that’s it. My published viz is here.
Donna + DataViz 