Lorna’s final #WOW challenge of 2021 was a parameter action based challenge, where the line chart at the bottom reflects the choices made by interacting with the bar chart at the top.
When I saw this was a drill down challenge, I immediately thought of previous similar challenges, so built based on the techniques I’d applied before (see this blog). This involved a parameter to capture the ‘level’ and a ‘drill down’ calculated field to pass through into the parameter on click. The solution I built (here) worked fine on Desktop (see tweet), but when published to Tableau Public, failed to display the Sub-Categories on selection :-(. I don’t know why. Having checked Lorna’s solution after, I realised I’d over complicated my solution, and had no need for the ‘level’ or ‘drill down’ fields. So I rebuilt to see if that fixed my issue with Tableau Public, and it did. So that is the solution I’ll blog about.
As stated in the requirements, I used Superstore v2021.3, but my numbers didn’t match Lorna’s. This isn’t an issue, but explains why my solution looks different from Lorna’s if you’re comparing. Having spoken to Lorna, we assume she didn’t use that version after all, but can’t recall what she may have used instead.
Anyway, onto the build.
First up, we need to define 2 parameters
pSelectedCategory
A string parameter defaulted to ‘nothing’ / empty string. This will be used to store the Category that the user clicks on in the bar chart.
pSelectedSubCat
Another string parameter defaulted to ‘nothing’. This will be used to store the Sub-Category that the clicks on in the bar chart.
We can’t just display the Sub-Category field in the bar chart, as it should only display a value ‘on click’. So we need a calculated field to store the value that needs to be displayed in the 2nd column of the bar chart – ie ‘nothing’ or the Sub-Category.
Sub Cat Display
IF [pSelectedCategory]=[Category] THEN [Sub-Category] ELSE ” END
If there’s a value in the pSelectedCategory, then display the Sub-Categorys, otherwise display a blank.
Build out the bar chart by adding Category and Sub Cat Display to Rows, and Sales to Columns. Order both Category & Sub Cat Display descending. Add Category to Colour and adjust. Label the marks, align the headings to the left, remove gridlines, hide the axis and column headings. If you show the parameter you can ‘type in’ a Category and see how the view looks.
On a new sheet, build out the line chart by adding Order Date to Columns set to the continuous (green pill) month format, and Sales on Rows. Label the max & min marks and remove all gridlines.
Create a new field which will be used to colour the line
Colour : Line
[pSelectedCategory]
Add this to the Colour shelf.
Show the pSelectedCategory parameter on the sheet and as you type in each Category value the colour legend will change. Adjust the colour for each value you type in, and for the empty value.
The line chart needs to change based on the selections made by the user, ie the values set in the parameters. A calculated field is required
FILTER
([pSelectedCategory]=”) OR ([pSelectedCategory]=[Category] AND [pSelectedSubCat]=”) OR ([pSelectedCategory]=[Category] AND [pSelectedSubCat]=[Sub-Category])
This is a boolean field. Add to the Filter shelf and set to True.
Finally, just to make things a bit more complete (and help me check the line chart was matching the selections), I created some additional fields to present on the Tooltip
TOOLTIP : Category
IF [pSelectedCategory]=” THEN ‘All’ ELSE [pSelectedCategory] END
TOOLTIP: SubCat
IF [pSelectedSubCat]=” THEN ‘All’ ELSE [pSelectedSubCat] END
I added these to the Tooltip shelf and amended the text as below
The final step is to place the two charts on a dashboard and then create 2 parameter actions, one which passes Category into the pSelectedCategory field on select. Clearing the selection needed to reset the value to ‘blank’.
The other parameter action needs to do similar, but set the pSelectedSubCat parameter from the Sub Cat Display field.
Week 49 of 2021, and it was Ann Jackson’s turn to set her final #WOW challenge (sniff sniff!). Ann’s been setting challenges for the last 4 years, and we’re all going to miss her (I’m sure she may pop up as a guest challenger at some point in the future ….).
Obviously in 4 years, Tableau has moved on, and new features make building this concept a bit more straightforward. So let’s crack on.
Modelling the data
Over the years Ann has adapted her style which includes capitalisation of text. Whilst the challenge can be built with the Tableau provided Superstore Sales data source, Ann very kindly provided her own version which already contained the capitalised fields. I used this version.
To do this type of ‘basket analysis’ you need to use 2 instances of the data source which you model using relationships as follows :
Add a relation of Order ID = Order ID and Sub-Category <> Sub-Category
This results in a data pane which lists a set of fields from the Ann Jackson Superstore data source and a set of the same fields from the Ann Jackson Superstore1 data source. In this blog, I’ll reference these data sources as AJS and AJS1. If you examine a single order with multiple sub-categories you’ll see how the data is being related
The first 2 columns above are from the AJS data source – the order contains 4 different sub-categories. The last 2 columns are from the AJS1 data source, and you can see that for each AJS.Sub-Category, only 3 AJS1.Sub-Category records are listed. This allows us to see the combination of what was ordered with what.
Building the matrix
Add AJS.Sub-Category to Rows and AJS1.Sub-Category to Columns and you have the start of your matrix.
The number of orders is displayed in each cell, which is
# Orders
COUNTD([Order ID])
Add this onto Text
This adds Null column, which is the number of orders containing a single Sub-Category.
Right-click on the word Null in the column to select the column, and Edit Alias to rename the Null to NONE.
To remove the top right hand side of the matrix, I indexed each row/column.
Sub Cat Index
INDEX()
Sub Cat 2 Index
INDEX()
Add Sub Cat Index to Rows, change to discrete (blue pill) and move to be in front of AJS.Sub-Category. Change the table calculation so that it is computing by the AJS.Sub-Category field. Each row should now be numbered from 1 to 17.
Now add Sub Cat 2Index to Columns, again change to discrete and move to be in front of the AJS1.Sub-Category pill. Edit the table calculation to verify it is computing by the AJS1.Sub-Category field. All the columns should now be numbered 1-18.
We want to remove the data for the records where the Row Index is greater then the Column Index
FILTER
[Sub Cat Index]>=[Sub Cat 2 Index]
Add this field to the Filter shelf, and set to true.
To colour the cells, add # Orders to the Colour shelf, and change the mark type to Square. Then edit the colour palette to use the Colour Brewer Blue Purple colour palette Ann advises (she’s used these palettes in the past, so I already have them installed, but you should be able to get them quickly from here).
Format the text to match mark colour and to be aligned centre. I also set the font to be Tableau Medium and bold. Set the column and row border divider lines to white.
Now uncheck show header against the two Sub Cat Index fields, and hide labels for column/ rows on the other column/row headings to remove them. Rotate the label for the columns and adjust the alignment, Adjust the font on both the column and row headers to bold and you should have the finished visual.
The Tooltips
We need to calculate the average sales contribution per order. We need 2 calculations for this, as we need to show a value for the AJS.Sub-Category on the rows and a value for the AJS1.Sub-Category displayed on the columns.
Average Sales (Rows)
SUM([Sales])/[# Orders]
Average Sales (Cols)
SUM([Sales (Ann Jackson Superstore1)])/[# Orders]
Add both of these to the Tooltip shelf. It’ll make a Null row appear – just right click and exclude.
Ann’s also been particular about some of the text displayed, so we need calculated fields to apply the required logic.
TOOLTIP – Sub Cat 2 Title
“& ” + [Sub-Category (Ann Jackson Superstore1)]
TOOLTIP- Sub Cat Text
IF [Sub Cat 2 Index]=1 THEN “SUB-CATEGORY” ELSE “SUB-CATEGORIES” END
TOOLTIP – Sub Cat 2
IF NOT(ISNULL([Sub-Category (Ann Jackson Superstore1)])) THEN [Sub-Category (Ann Jackson Superstore1)] + “:” END
Add all three fields to the Tooltip shelf, and adjust the tooltip as below
And with that, you should now have the fully completed challenge. My published version is here.
Finally, once again I want to thank Ann for all her contributions to the #WOW community over the last 4 years. I will miss her capitals and bold colour palettes – I’ll be storing away her pre-capitalised version of Superstore for sure :-)!
Candra set this seasonal fun challenge this week. It may not deliver data visualisation best practice, but as a ‘seasoned’ #WOW participant, this provided a chance to use a technique I rarely use (background images) and learn a brand new technique (colouring a bar chart with a gradient).
Getting set up
To start you’ll need to download all the files Candra provided, which will consist of the data (across 2 sheets) and 3 images.
The bauble and star image will need to be saved into your Tableau Shapes repository – I copied them into a new folder I called Xmas in this directory ..\My Tableau Repository\Shapes
The tree image just needs to be saved somewhere you’ll remember.
Building the Tree Chart
Connect to Sheet1 of the downloaded WOW Advent Calendar excel file.
Drag X to Rows and Y to Columns and disaggregate the marks (Analysis -> uncheck Aggregrate Measures)
You should get the basic layout of where the baubles are positioned. You may also notice that the X & Y values in the dataset were labelled wrong. The x-axis is your horizontal axis, but these have been stored in the Y field, while the vertical y-axis values have been stored in the X field. You may wish to rename these to save you getting confused.
Add Day to the Label shelf, and align middle centre.
We need to distinguish the bauble marks from the star mark. The star mark has a NULL value for the Day field, so we can utilise that
Day Is NULL
ISNULL([Day])
Add this onto the Shape shelf, and adjust the shape images to source the new images you saved into your shapes repository (if you can’t find them try clicking ‘reload shapes’). Adjust the initial size of the images via the slider on the Size shelf.
Now add Day Is Null to the Size shelf too. Edit the size and adjust the mark size range to suit, so the star is larger than the baubles.
Adjust the Label of the bauble so the text is larger and white.
To make the tooltip slightly more readable than that in the provided solution, I created my own custom version
Tooltip
IF [Day is Null] THEN “It’s Christmas Day!” ELSEIF [Day]=1 THEN STR([Day]) + ” day until Christmas” ELSE STR([Day]) + ” days until Christmas” END
Add this to the Tooltip shelf, and adjust so it’s just referencing this field.
Add Link to the Detail shelf – this will be needed for the interactivity later.
Now we’ll add the background image via the Maps -> Add Background Image -> <datasource> menu option. Click to add image, then browse to where you’ve saved the Xmas tree image.
We need to define the min/max coordinates for where the tree image should be positioned, and based on this, we want the horizontal X axis (the Y field) to range from 0-4 and the vertical Y axis (the X field) to range from 0 to 6. Press the Apply button and you see whether things look to be placed correctly before you close all the dialog windows.
Next we need to fix the axis, so that when interacting later, we keep the whole tree visible. The vertical axis should be fixed from 0-7, and the horizontal axis should be fixed from 0 to 4.
Then we need to remove all the axis from displaying (uncheck show header), and all row/column borders and gridlines and axis rulers.
The final step is to remove the map options from displaying when you hover over the chart – Map ->Map Options and uncheck all the options displayed in the dialog
Since the process is already documented, I don’t have to write all the steps out myself 🙂 I’ll just point out the fields I used/created, since we’re working with a different data set.
Firstly I created a new data source for this chart, which combined Sheet1 with the Range sheet, and used a relationship calculation of 1 = 1 to combine the data (Relationships didn’t exist when the blog post was written).
The equivalent of the Sales Adjusted field is the existing pre-computed Sheet1(Count) field, so we don’t need any calculated field for this.
Max Segmentsin my solution is
Max Range
WINDOW_MAX(MAX([Range (Range)]))
Note – Range (Range) is referencing the Range field from the Range table. If you just drag the field into the calculation window, it will automatically present in this way.
Total in my solution is
Total Count
TOTAL(COUNT([Sheet1]))
Sizein my solution is
Size
[Total Count]/[Max Range]
and finally Color in my solution is
Colour
([Max Range]-[Index]) * [Size]
Add Challenge Year to Rows (rather than Category), change the mark type to bar if it doesn’t change automatically. Reverse the axis so 2018 is listed at the top, then change the Challenge Year pill to be discrete (blue) rather than green.
If you follow the other blog post through, you should hopefully end up with
You then need to show the labels and adjust the tooltip against the CNT(Sheet1) marks card only.
As before, remove all gridlines borders etc.
Adding the interactivity
Add the 2 sheets to a dashboard. We need to allow the tree to filter on click of the bar chart. I used the ‘use as filter’ option on the context menu of the the bar chart object to quickly set this.
This adds a filter action to the dashboard, but is a quick way of getting it created rather than having to manually set it up via the dashboard -> actions menu.
I did use this menu though to add the URL action to the Tree sheet, which just needs to reference the Link to the Challenge field.
So a relatively short blog this week, since the most complicated section is already written up be Ken Flerlage (thanks Ken!). It was a great fun little challenge, which still allowed for plenty of learning opportunities. My published viz is here.
Sean Miller posted this week’s challenge based on the results of the annual NFL games hosted on Thanksgiving Day. It immediately reminded me of a previous #WOW challenge that Lorna posted in 2019 when she visualised Rugby League wins (see my viz here).
This is a table calculations based challenge. I did start using FIXED LoDs to help calculate the summary measures (Total Games and Win %) displayed at the front, but found that as there are 2 years (1975 and 1977) when the Dallas Cowboys did not host a game, I ended up with some pesky NULL values displaying which affected how the running sum area chart displayed.
Defining the calculations
As its a table calc challenge, I’ll build out what I can into a table to start with, to sense check I’m getting the correct numbers.
First up add Home Team, Game Date and Visiting Team to Rows and display Home Score and Visiting Score.
We start by determining the result of the fixture, based on whether it’s a home or away win or a tie. In the lollipop chart home wins are plotted at 1 and away wins at -1, so we’re going to store the result as a numeric value rather than text.
Result
FLOAT(IF [Home Score]>[Visiting Score] THEN 1 ELSEIF [Home Score]<[Visiting Score] THEN -1 ELSE 0 END)
The output is wrapped within a FLOAT, as this will help how the axis displays. Without it, by default Tableau will define the field to be a whole number, and the axis will extend to +/-2 which is too much room. We can’t adjust (fix) the axis to a decimal if the field itself is an integer, and adjusting to +/-1 chops off the displayed marks.
If you add this to the display, it will show 1, 0 -1 as you expect. You’ll notice though that the Axis on the lollipop chart is labelled as Win/Loss. This is achieved by applying a custom format to the field – “Win”;”Loss”;”Tie”
This is a sneaky but effective trick. The information stated before the first semi-colon applies to positive numbers, the info after the first semi-colon applied to negative numbers, and the information after the optional second semi-colon applies to zero.
Unfortunately though, it would appear that, at the point of writing, Tableau Public, isn’t honoring the zero formatting, and is displaying Win rather than Tie. The display works on Desktop though.
The win/loss/tie text is just a formatting feature and affects what is displayed, but the underlying value is still a number.
The Result field will be used to plot the lollipop chart. We now want a field to plot the area chart against. This is a running total of the Result values (ie win =1, win, win = 1+1, win, win, loss = 1+1 -1) and we need a table calculation.
However, as stated above due to a couple of missing years, I had to make an adjustment to ensure the running total displayed as Sean had in his challenge. I created another field
Result Adjusted
IIFNULL(SUM([Result]),0)
If the Result field doesn’t exist, as there is no data, then use 0 instead.
To see what’s going on, we’re going to need a different view of the data where the date field is continuous (green) rather than discrete (blue).
Build the below, and filter just for the first 10 years – you’ll see the gaps where the are no marks in 1975 and 1977 for Dallas
Use the context menu of the green YEAR(Game Date) pill and select the option to Show Missing Values. Marks will now display
Add Result to Label. Each mark is labelled Win or Loss, except the ones for Dallas for 1975 & 1977 as there is no data
Now add Result Adjusted to Label. A 0 value is now displayed against those two marks.
We can now build a running total off of this measure instead
Running Total Wins
RUNNING_SUM(([Result Adjusted]))
Add this to the Label too and verify the table calculation is computing by the Game Date field only. The running total for the 2 ‘missing’ dates is displaying a value which is the same as the previous value (since we’ve added 0 onto the running total). This will give us the flat line in the area chart when we come to build it.
Now back to our table of data, we can focus on the other calculated fields we need….
Total Games
WINDOW_COUNT(COUNTD([Game Date]))
This is a table calculation and is simply counting the number of distinct dates displayed. Add this to the table display we were building to start with, and adjust the table calculation to compute by all fields except Home Team. The total should display the same value for all the rows against each Home Team.
Next we want a field to indicate if the row is a win.
Is Win?
INT([Home Score]>[Visiting Score])
This is taking a boolean of true or false and converting to an INT (1 or 0).
From this we can work out the Win rate
Win %
WINDOW_SUM(SUM([Is Win?]))/[Total Games]
Add up all the Is Win? values associated to the Home Team as a proportion of the Total Games played. Format this field to a percentage with 0 dp. Again, add to the table and adjust the table calc to compute by all fields except Home Team, and verify the same settings applied to both the calculations nested in this calculation
For the All-Time Record, we need to know the number of wins and number of losses. We have a field to help us with the wins, but need an equivalent for the losses
This is the one field I kept from my LoD based attempt.
The circles on the lollipop chart are coloured based on the difference in the score, so lets’s create that
Score Difference
[Home Score]-[Visiting Score]
And finally we need some fields to help display the tooltips properly. The tooltip indicates whether the result was ‘won’ or ‘lost’ which is different text to the axis labels.
TOOLTIP-Result
IF [Result]=1 THEN ‘won’ ELSEIF [Result]=-1 THEN ‘lost’ ELSE ‘tied’ END
The tooltip also displays the scores, but the scores are always presented as highest score – lowest score and not home score – visiting score. So we need fields to store the right values
TOOLTIP–Higher Score
IF [Is Win?]=1 THEN [Home Score] ELSE [Visiting Score] END
TOOLTIP – Lower Score
IF [Is Loss?]=1 THEN [Home Score] ELSE [Visiting Score] END
Pop all these fields out onto the table, so you can validate you’ve got all your calcs right before building the viz.
Building the area chart
Add Home Team to Rows, Game Date (continuous, show missing values) to Columns and Running Total Wins to Rows (ensure table calculation set as required). Change to mark type of Area. You should have 2 horizontal lines from 1974-1975 and 1976-1977 against the Dallas Cowboys row.
Adjust the tooltip, edit the label of the Running Total Wins axis , and remove the label of the Game Date axis.
Building the lollipop chart
Now add Result to Rows directly after the Home Team pill. Change the mark type to circle.
Add Score Difference to the Colour shelf of the circle mark, and adjust the starting colour range to a dark grey. Readjust the colour of the area chart to blue too. Add a border to the area chart too (via the colour shelf).
Add another instance of Result to the Rows shelf, next to the existing one. Set the mark type of this to bar. Reduce the size to the smallest possible, set the colour to grey and remove the border.
Now set this to be dual axis, synchronise the axis, and set the marks of the 2nd Result axis displayed on the right hand side to move marks to back. Uncheck Show Header to remove this axis from displaying.
Add Visiting Team, TOOLTIP-Result, TOOLTIP-Higher Score and TOOLTIP-Lower Score to the Tooltip shelf of both the Result marks cards, and adjust the tooltip on both to
Remove the Column dividers.
Now drag Total Games to Rows and drop next to the Home Team field. Change to be discrete (blue). Verify the number is what you expect and adjust the table calc if need be.
Add All-Time Record and Win % (set to discrete) to the view too. Then format these 4 fields so the text is larger and aligned centrally.
All that’s left now is to add the sheet to a dashboard. My published viz is here.
Lorna created this challenge for #WOW2021 this week incorporating tips from the Speed Tipping session she and fellow WOW leader Ann Jackson had presented at TC21.
Defining the calculations
The requirements were to ensure there were only 7 calculated fields used, and no date hardcoding (including in the title – a feature I missed to start with). So let’s start by just going through the required calculations.
We need to identify the latest year in the data set
Current Year
YEAR({FIXED:MAX([Order Date])})
This uses an LoD (Level of Detail) calculation to identify the maximum date in the whole data set, which is 31st Dec 2021, and then extracts the Year of this ie 2021.
From this, we work out
Previous Year
[Current Year] – 1
Both of these fields return numbers, so automatically sit in the measures section of the left hand data pane (ie under the horizontal line). I want to treat these as dimensions, so I just drag the fields above the line.
We now need to create dedicated fields to store the Sales values for both years
CY Sales
IF YEAR([Order Date])=[Current Year] THEN [Sales] END
PY Sales
IF YEAR([Order Date])=[Previous Year] THEN [Sales] END
and with both of these, we can work out the
Difference
SUM([CY Sales])-SUM([PY Sales])
[TIP] This is custom formatted to △#,##0;▽#,##0.
I googled ‘UTF 8 triangles’ and used this link to find the suitable shapes which I just copied and pasted into the number format field.
We’re going to need to determine whether the difference is positive or not.
Is Loss?
MAX(0,[Difference]) =0
This is another [TIP] making use of the array function. If the Difference is negative, it will return 0 as this is the maximum of the two numbers. I’m not entirely sure if this is more efficient than simply writing Difference<=0, but I wanted to incorporate another of the tips presented.
The final calculation we need is another of the PY Sales field, as we need another distinct Measure Name value to display. I simply chose to duplicate the existing field to have a PY Sales (copy) field.
Building the viz
Add Category to Columns, Segment to Rows and then add CY Sales to Columns, which will create a horizontal bar chart. Then drag PY Sales to the CY Sales axis, and when the ‘two columns’ icon appears, drop the field.
This will automatically change the pills so Measure Values is on Columns and Measure Names is on Rows.
Swap the order of the pills on the Measure Values section on the left hand side, so PY Sales is listed before CY Sales.
Add Measure Names to the Colour shelf and adjust. Increase the width of the rows.
Check the Show Mark Labels option on the Label shelf and adjust alignment to display the text to the left
Increase the Size of the bars to the maximum size, and add a white border (via option on Colour shelf)
Add PY Sales (Copy) to Columns, and change the mark type to GanttBar. Remove Measure Names from the Colour shelf of this marks card, as it will automatically have been added. Instead add the Is Loss? field to Colour and adjust.
Add Difference to the Size shelf, then click on Size, and reduce it to as small as possible. Set the border of this mark to Automatic (it should become a little thicker).
Next add the Difference field to the Label shelf, align right and set the font colour to match mark colour.
Now make the chart dual axis, synchronise axis, and set the mark type of the Measure Names mark type back to a bar.
On the All marks card, add CY Sales, PY Sales and Difference to the Tooltip shelf. And add Current Year and Previous Year to the Detail shelf.
Adjust the Tooltip against the All marks card, so it is the same when you hover on all of the marks. And edit the title of the chart, referencing the Current Year and Previous Year fields.
The challenge has a ‘space’ between each Segment, and this is the final TIP I used.
On the Measure Values section on the left below the marks card, type in MIN(NULL). This will initially create a new ‘blank’ row between the bars and the gantt marks, which isn’t where we want the blank row to be.
To resolve this, simply click on the MIN(NULL) text in the chart and drag the text below the PY Sales (copy) text
And now you just need to uncheck Show Header against the Measure Names pill on Rows, and the Measure Values and PY Sales (copy) fields on the Columns. Then remove all row and column borders and gridlines and hide labels for rows and columns.
Hopefully you’ve got the final viz which you can now add to a dashboard. My published version is here.
Ann set this week’s #WOW2021 challenge during #TC21 and chose to live stream her build. I couldn’t watch it but I did then manage to catch a snippet of Kyle Yetter who bravely chose to share his attempt at recreating the challenge via a live stream too with Ann & Luke watching. I had already completed my build by the time I watched Kyle, and it was interesting to see where our approaches differed.
The main via is a single chart, so I started by building out all the data I needed in tabular form, so I could verify the sort.
Defining all the calculations for the main viz
First up, the data needs to be compared to ‘today’ where ‘today’ is set to 01 Jan 2022. I used a parameter to store this
pToday
Date constant set to 01 Jan 2022
We also need to understand the latest order date per customer, so need
Max Order Date By Customer
DATE({FIXED [Customer ID]: MAX([Order Date])})
and so with this we can calculate the days since last order, which is one of our key measures.
Days Since Last Order
DATEDIFF(‘day’, [Max Order Date By Customer], [pToday])
The other measures we need are
#Orders
COUNTD([Order ID])
Avg. Order Value
SUM([Sales])/[#Orders]
along with Sales and Quantity.
These are the 5 main measures displayed, but there are some additional calculations needed for the display labels and tooltips.
The label to indicate the ‘time since last purchase’ is displayed in days or years, so for this I created 2 specific fields
LABEL:Days Since Last Order
IF [Days Since Last Order] <365 THEN [Days Since Last Order] END
This will only store a value when the days is less than 365. I then formatted this field to have a ‘ days’ suffix
Similarly, I created
LABEL:Years Since Last Order
IF [Days Since Last Order]>=365 THEN [Days Since Last Order]/365 END
which stores a value for records >=365 only. This was formatted to have 1 dp and the ‘ years’ suffix instead.
The colour of the first measure is based on whether the customer is considered ‘active’ or not. The threshold for this is managed via a parameter
pActiveCustThreshold
An integer parameter defaulted to 90.
We can then create
Is Active Customer?
[Days Since Last Order]<=[pActiveCustThreshold]
This is a boolean field and will return true or false, but to get the tooltip value to display appropriately, I edited the alias of this field (right click > Aliases)
For the tooltip on the ‘total products’ (ie quantity measure), we need to display the number of distinct products orders, which we can capture in
#Products
{FIXED [Customer ID]: COUNTD([Product ID])}
Pop all these fields, along with the Customer ID and Customer Name into a table and you can see validate all the values are as you expect
Sorting the Sort
We need another parameter to manage the sort.
pSort
I used an integer parameter, but set the display to the required text strings. This is because writing logic on integers is more efficient than strings
I then created a calculated field to determine the measure to sort by
Sort By
CASE [pSort] WHEN 1 THEN SUM([Days Since Last Order]) * -1 WHEN 2 THEN [#Orders] WHEN 3 THEN SUM([Sales]) WHEN 4 THEN [Avg. Order Value] WHEN 5 THEN SUM([Quantity]) END
Notice the first measure is being multiplied by -1 since this measure is to be displayed in Ascending order rather than Descending.
In the table, amend the Customer ID pill to Sort by the Sort By field descending
Show the pSort parameter and test the functionality by switching the values.
Building the main viz
Having got all the calcs defined, this viz is relatively straight-forward. It is a multiple axis viz by Customer ID and Customer Name (note I use Customer ID as well, which I hide, just in case there are multiple customers with the same name).
Customer ID and Customer Name on Rows. Apply sort to Customer ID as described above. Hide Customer ID (uncheck Show Header). Align Customer Name to right, and adjust font.
Type in MIN(1) on Columns. Edit Axis to be fixed from 0-1. Add Is Active Customer? to Colour. and adjust. Add LABEL:Days Since Last Order and LABEL:Years Since Last Order to Label. Align Centre and match mark colour. Make sure the two fields are side by side on the label, and not underneath each other. Add Max Order DateBy Customer to the Tooltip shelf. Adjust Tooltip
Add #Orders to Columns. Strip off all the fields that have been automatically added to this marks shelf. Set the Colour accordingly. Show the Label at the end of the bar. Adjust Tooltip
Add Sales to Columns. Set the Colour accordingly. Show the Label at the end of the bar. Adjust Tooltip
Add Avg. Order Value to Columns. Set the Colour accordingly. Show the Label at the end of the bar. Adjust Tooltip
Type in MIN(0) on the Columns shelf. Change mark type to Circle. Set the Colour accordingly. Add Quantity to Label. Add #Products to Tooltip. Adjust Tooltip
Remove all row & column borders and gridlines, zero lines etc.
Add a 0 constant Reference Line to the #Orders, Sales and Avg. Order Value axes.
Hide the axes, hide field labels for rows.
Building the Viz in Tooltip
The Viz in Tooltip shows the Top 10 Products per Customer by Sales.
On a new sheet, add Customer ID, Customer Name and Product Name to Rows and Sales and Quantity (via Measure Values) to Text.
Add a Rank Quick Table Calculation to the Sales pill, then edit the table calc to be unique and to compute by Product Name
Click on the Sales rank pill , press Ctrl and drag it into the measures pane. Name the field Sales Rank. Format the field to have a # prefix. Add Sales back into the view.
Now drag the Sales Rank field from the Measure Values section and add to Rows, then change to be a discrete (blue) field, then move so it is to the left of the Product Name field. This should cause everything to re-sort into the required order automatically. It’s always worth double checking the table calc is still computing as expected.
Click on the Sales Rank pill and press Ctrl and this time drag to the Filter shelf and check values 1 to 10.
Now hide the Customer ID and Customer Name fields, and format the display.
Finally, set the display to Entire View. This will make the ‘view’ unreadable, but is necessary to ensure you don’t get a ‘viz is too large to display’ message when you get to add the Viz to the tooltip.
Adding the Viz in Tooltip
Back to the chart sheet, and edit the tooltip of the MIN(0) marks card. Insert the Top 10 sheet via Insert > Sheets. Adjust the width and height values and change the filter to just use <Customer ID>
Hovering on the circle mark should then display the Top 10 Products for that specific customer.
Adding the column headings
On the dashboard, use a horizontal layout container above the main viz. Add 6 text objects, enter the relevant text and centre. Adjust the width of each text object to line up with each column.
The help icon
Add a floating text object to the sheet – it’ll appear over the top of the chart. Position the text object to the top left, and set the background to white. Enter the text. From the context menu of the object, select the Add Show/Hide Button. Position the button in the appropriate location and resize. Edit the button options to have a grey background and show ? when hidden and X when shown.
I think that’s covered all the core points. My published viz is here.
With #TC21 looming next week, Candra’s set this week’s challenge, based on inspiration from past Tableau Conferences – a simple looking, but effective visualisation for understanding profit performance within some pre-established timeframes.
Building the BANs
Identifying Top 5 / Bottom 5 / Everything Else
Building the Chart and Labelling the Bars
Adding the interactivity
Building the BANs
The timeframes we need to report over need to be based on a specific date. In this case it’s the latest date in the data set. If you were using this for a business dashboard, you might be basing it on Today / 1st of the Current Month etc. Rather than hardcode the date I need, I’ve worked out the latest month I want to use by
Set all the Order Dates in the data set to be the 1st of the month, then get the maximum of these dates. So as the last date in the data set is 30th Dec 2021, that’s been truncated to 1st Dec 2021 which is then what this field stores.
I then want to capture the profit values for each month, quarter, year into separate fields, so we have
Month
IF DATETRUNC(‘month’, [Order Date])=[Max Month] THEN [Profit] END
This only stores Profit values for rows where the Order Date is also in December
Quarter
IF DATETRUNC(‘quarter’,[Order Date])=DATETRUNC(‘quarter’,[Max Month]) THEN [Profit] END
This only stores Profit values for rows where the Order Date is in the same quarter as December (ie the 4th quarter which is months Oct-Dec).
Year
IF DATETRUNC(‘year’,[Order Date])=DATETRUNC(‘year’,[Max Month]) THEN [Profit] END
This stores Profit data for rows where the Order Date is in the same year.
All these fields are formatted to be $ with 0 dp.
A basic viz can the be built with Measure Names on Columns and Measure Names and Measure Values on Text. The Measure Names heading is then hidden, and the font and table formatting adjusted so the sheet looks as below.
Note – Naming these fields Month, Quarter, Year rather than Monthly Sales, Quarterly Sales etc, makes this display much easier and also helps with the interaction later.
Identifying the Top 5 / Bottom 5 / Everything Else
We need to be able to identify the Sub-Categories which have the best profits, those that have the worst, and the ‘rest’. We’re going to use Sets to help us with this. However the set entries could change depending on whether we’re looking by month, by quarter or by year. So first we need to create a field that is going to store the particular Profit value we need depending on what time period is being selected.
We need a parameter pDatePart to capture the time frame. This is a string field which is just defaulted to the text ‘Month’.
The interactivity later will set this parameter to the different values.
So now we know the ‘selected’ date part, we need to get the appropriate profit value
Value To Plot
CASE [pDatePart] WHEN ‘Month’ THEN [Month] WHEN ‘Quarter’ THEN [Quarter] ELSE [Year] END
This just uses the values from the 3 measures we created to start with.
So now we can create the sets we need. Right click on Sub-Category > Create > Set and create a set called Top 5 that is based on the Top 5 Value to Plot values
Then create another set in the same way called Bottom 5
With these sets, we can now determine the Sub-Category ‘label’ that will be displayed
Sub-Category Display
IF [Top 5] OR [Bottom 5] THEN [Sub-Category] ELSE ‘Everyone Else’ END
and the grouping that will be used to colour the bars
Sub Cat Group
IF [Top 5] THEN ‘Top 5’ ELSEIF [Bottom 5] THEN ‘Bottom 5’ ELSE ‘Everything Else’ END
Building the Chart & Labelling the Bars
Ok, so now we’ve got the building blocks in place, we can build the chart. You will probably be tempted to build a bar chart (I did to start with), but positioning the labels then became a bit tricksy. When we get to the labels, we’re going to need to use the left and right alignment options. However, when you build a bar chart, if you right align the label, the label will be positioned outside at the end of the bar (even though this seems a little odd with negative values, as it looks to be on the left…).
Right aligned labels
But then we set the labels to be left aligned, the labels appear inside the bar instead, and not outside on the left.
Left aligned labels
So instead, rather than using the bar mark type, we need to build this chart using the gantt mark type, and base the Size on the Value to Plot field.
However, the value being plotted is actually an average value based on the number of Sub-Categories being ‘grouped’ as otherwise the value associated to Everything Else can end up bigger than all the rest. I created the following field
Avg Value To Plot
SUM([Value to Plot])/COUNTD([Sub-Category])
formatted to $ with 0dp.
So now we start building by adding Sub-Category Display to Rows and type in MIN(0) into Columns. Change the mark type to Gantt and add Avg Value To Plot to Size. Add Sub-Cat Group to Colour and adjust accordingly. Sort the Sub-Category Display field by Avg Value To Plot descending.
Now we can’t just label by a single field of the value or the sub-category, as while the ‘automatic’ label alignment option, almost puts the labels in the right positions, there is no way to define an ‘opposite’ to the ‘automatic’ alignment. We need to define some dedicated label fields based on where we want them to display.
Label – Left – Profit
IF [Avg Value To Plot]<0 THEN [Avg Value To Plot] END
If we’re in the bottom half of the chart, we’re going to display the Profit value on the left side.
Label – Left – Sub Cat
IF [Avg Value To Plot]>=0 THEN ATTR([Sub-Category Display]) END
If we’re in the top half of the chart, we’re going to display the Sub-Category Display on the left side.
Add both these fields to the Label shelf and then adjust the label alignment to be left.
To label the other ends, we need to create two further label fields
Label – Right – Profit
IF [Avg Value To Plot]>=0 THEN [Avg Value To Plot] END
Label – Right – Sub Cat
IF [Avg Value To Plot]<0 THEN ATTR([Sub-Category Display]) END
We then need to create another MIN(0) on Columns (easiest way is to hold down control, then click on the existing MIN(0) field and drag it next to itself to create a duplicate. Then on the 2nd marks card, remove the two Label – Left – xxx fields and add the two Label – Right -xxx fields. Change the alignment to right.
The make the chart Dual Axis and synchronise the axis.
Now you can hide the Sub-Category Display header from showing, hide the axis, remove gridlines etc.
Adding the interactivity
Once the two sheets are on the dashboard, you can add a dashboard parameter action which will on select of the KPI/BAN chart, pass the Measure Name into the pDatePart parameter. When the mark is unselected, the parameter value should stay as it is.
And hopefully, you should now have a working viz. My published version is here.
It was Sean Miller’s turn this week to set the challenge, which had an added twist – to complete only using the web authoring feature in Tableau Public, something I’d never tried before.
Most challenges use a version of Superstore Sales which I usually already have on my laptop, due to the multiple instances of Tableau Desktop installed, but this time, I had to get the data from data.world. I saved this locally then logged into Tableau Public online and started the process to build a viz online, by clicking the Create a Viz (Beta) option.
When prompted I then chose to upload from computer, the data source file I’d downloaded, and once done I was presented with the online canvas to start building
Unsure as to whether there was any auto save facility online, I decided to save the workbook pretty much immediately, although I was first prompted to navigate to the Data Sourcetab and create an Extract before I could save.
Now I was ready to start building out the requirements.
I started with the trend chart. The grain of the chart was at the month level, so I first created a specific field to store the Order Date at this particular level
Order Date Month
DATE(DATETRUNC(‘month’,[Order Date]))
I then added this to the Columns shelf as a continuous (green) exact date, and added Sales to the Rows shelf. I changed the mark type to Area, set the Colour to a blue, dropping the opacity to 50% in an attempt to match Sean’s colouring and adjusted the tooltip.
I then added another instance of Sales to the Rows shelf next to the existing one, changing the mark type of this instance to Line and setting the Colour to a darker blue at 100% opacity. I then used the context menu on this pill to set a Quick Table Calculation of type Moving Average.
By default this sets the average to the 3-month rolling average as per the requirement. I adjusted the tooltip, made the chart dual axis and synchronised the axis, then removed Measure Names from the Colour shelf that had automatically been added.
My next step at this point was to work on the filtering requirement. To help with this, the first thing I did was to Duplicate as crosstab (right click on the chart tab). This created a tabular view of the data so I could work on getting the calculations I need for the filtering. I flipped the columns and rows, so my starting point was as below.
I chose to use parameters to capture the min and max dates that the user selects on the dashboard.
pMinDate
Date parameter defaulted to 01 Jan 1900
And I also created pMaxDate exactly the same way.
I then needed fields to store the relevant dates depending on whether a selection had been made or not
Min Date Selected
IF [pMinDate]=#1900-01-01# THEN {FIXED : MIN([Order Date Month])} ELSE [pMinDate] END
The FIXED statement sets the date to the minimum month in the data set, if the parameter is set to its default. The below doed similar to get the maximum date in the data set.
Max Date Selected
IF [pMaxDate]=#1900-01-01# THEN {FIXED : MAX([Order Date Month])} ELSE [pMaxDate] END
Using these dates, I then created a field to determine whether the month was within the min & max dates
Is Month Selected?
[Order Date Month]>=[Min Date Selected] AND [Order Date Month]<= [Max Date Selected]
Pop this field onto the table view, and show the parameters and set them to some other values eg Min Date = 01 Jun 2018 and Max Date = 01 Sept 2018. You’ll also need to adjust the table calc setting of the Moving Average Sales pill to compute by Is Month Selected as well.
You’ll see True is stamped against the rows which match the date range. Now, if you make a note of the Moving Average values against these rows, and then add Is Month Selected? = True to the Filter shelf, you should get just the True rows displaying, BUT the Moving Average values are now different. This is because, with this type of field as a filter, the filter is being applied BEFORE the table calculation is being applied – the table calculation essentially has ‘no knowledge’ of the rest of the rows of data as they have been filtered out. Solving this issue is the crux of this challenge.
Remove Is Month Selected? from the filter shelf. We’re going to create a new field to filter by instead
FILTER
LOOKUP(MIN([Is Month Selected?]),0)
This field uses the LOOKUP table calculation, and is basically looking at the value of the Is Month Selected? field in it’s ‘own row’ (denoted by the 0 parameter).
Pop this onto the table view, and you can see that it’s essentially a duplicate of the Is Month Selected? field.
Now add the FILTER =True field to the Filter shelf instead, and this time the rows will once again filter as required, but the Moving Average values will be the same as those in the unfiltered list, which is what we need.
This works because the field we’re filtering by is a table calculation. When added to the filter shelf, these types of fields are applied at the ‘end’ of the processing; ie the table calculation is computed against all the rows in the data AND THEN the filter gets applied. As a result the Moving Average values are retained.
This is all part of Tableau’s Order Of Operations and is a fundamental concept when working with filters.
Armed with this knowledge, we can now apply the FILTER=True to the Filter shelf of the chart we built.
Now we can start to work on the bar charts, which will show Sales along with Average Monthly Sales. For this 2nd measure, I need to work out the number of months captured between the min & max dates.
No. Months
DATEDIFF(‘month’, [Min Date Selected],[Max Date Selected])+1
And with this I can now create
Avg Monthly Sales
SUM([Sales])/MIN([No. Months])
The bar chart viz can then be quickly created by adding Sub-Category to Rows and both Sales and Avg Monthly Sales to Columns, and sorting by Sales descending. The colours of each bar are adjusted to suit.
The bars also need to react to the dates selected, but we can simply add the Is Month Selected? = True to the Filter shelf for this, as none of the values displayed on this chart are reliant on table calculations.
To remove the gridlines from both charts, select Format > Workbook from the menu, scroll down the Format dialog displayed on the right and set Gridlines to Off
Finally, title both sheets appropriately, and then add to a dashboard. We need to add 3 actions to this dashboard – 2 parameter actions and 1 filter action.
Create a parameter action which will on Select, set the pMinDate parameter by passing the MinimumOrder Date Month, and will reset back to 01 Jan 1900 when unselected.
Then create another instance, which sets the pMaxDate parameter instead, passing the Maximum Order Date Month.
Finally, create a Filter Action which on Select of the bar chart, filters the trend chart
And with that, the challenge should be complete. My published viz is here.
There’s a lot packed into the challenge this week, which was “set” by Lorna Brown and Erica Hughes to test our Set Action skills. How detailed this blog will go, I have yet to decide… I’ve got a couple of hours to get this nailed, so it could get quite brief as we get towards the end 🙂
I’ve got 6 sheets/charts making up this dashboard, so my intention is to summarise each one, and I’ll define the various calculations that are going to be needed as we go.
The overall summary table
The selected months summary table
The trend line
The donut chart
The top 3 states table
The map
Adding the interactivity
The overall summary table
This challenge is focused on understanding the Sales per month. Whilst its possible to use the built in aggregation features of a date field, I often prefer to create explicit date fields at the level I require, so it’s easier to reference. Therefore, the first field I created for this challenge was
Order Date To Plot
DATETRUNC(‘month’, [Order Date])
This essentially ‘groups’ every order placed in a month to be tagged with the 1st of the month. I custom formatted this field to MMM yyyy (ie Oct 21).
For the overall summary table, I need to capture the total sales of the whole data set, and I use a Fixed LoD calculation for this.
Total Sales
{FIXED: SUM([Sales])}
This field is formatted to $0.00M
NOTE – I actually named this field <space>Total Sales<space> as I want to display the name of the field (the measure name) in the summary table, but the ‘selected months’ summary table also has a Total Sales measure which is a different calculation (see later). Adding the <spaces> is a sneaky way to get two fields with what appears to be the same name. As this field when displayed will be centred, the <spaces> aren’t noticeable.
We also need to get the monthly average sales for the whole data set
Average Sales by Month
AVG({FIXED [Order Date To Plot]: SUM([Sales])})
Format this to to $0.0K
We can now build the summary table by adding Measure Names to the Filter shelf and selecting these 2 fields. The placing Measure Names on Rows and Measure Names and Measure Values on Text. Reorder the measures as required, hide Measure Names on Rows and format the Text as required.
Change the title of the sheet to Superstore Sales, ensure the tooltip doesn’t display and remove all gridlines / row banding etc.
The selected months summary table
The core requirement of this challenge is to make use of set actions, so we’re obviously going to need a set which will contain the dates (months) the user will select on the chart. This set will be based off of Order Date To Plot. Right click on the field > Create > Set. Name it Order Dates To Plot Set and by default, select all the months between Oct 20 to Jun 21 inclusive.
Later I’ll describe how the values of this set will get updated, but for now, we need to get some information relating to the sales in these selected months.
Firstly, we want the total sales for the months in this set.
Total Sales
IF [Order Date To Plot Set] THEN [Sales] END
The default format for this field is set to $ with 0 dp.
Note – this is the other ‘total sales’ field mentioned earlier. This field name has no leading/trailing spaces.
To get the average, I needed a field just to store each member of the set (ie each selected month)
Selected Dates
IF [Order Date To Plot Set] THEN [Order Date To Plot] END
and with this I can then work out
Average Sales
AVG({FIXED [Selected Dates]: SUM([Total Sales])})
The final measure required for this section is the change within the date range, which is basically comparing the value of sales at the first month in the selection with the sales in the final month selected. We need a few fields to get to this.
Firstly, we want to identify the first and last months
Min Selected Date
{FIXED:MIN(IF [Order Date To Plot Set] THEN [Order Date To Plot] END)}
If the date is in the set, then return the date and then take the minimum of all the dates, and store against all the rows in the data. Similarly we have
Max Selected Date
{FIXED:MAX(IF [Order Date To Plot Set] THEN [Order Date To Plot] END)}
Putting this info into a table, you can see how the calculations are working. The values for the Min & Max dates are the same across every row.
Next we need to get the Sales at the min & max points, and spread that value across all rows
Sales at Min Date
{FIXED: SUM(IF [Order Date To Plot]=[Min Selected Date] THEN [Sales] END)}
Sales at Max Date
{FIXED: SUM(IF [Order Date To Plot]=[Max Selected Date] THEN [Sales] END)}
Now we can work out the difference
Change within Date Range
([Sales at Max Date]-[Sales at Min Date])/[Sales at Min Date]
format this to a percentage set to 1 dp
Finally, we need to know the number of months in the set, which is displayed in the title of the monthly summary sheet.
Months in Set
{FIXED: COUNTD(IF [Order Date To Plot Set] THEN [Order Date To Plot] END)}
If the date is within the set, then capture the date, and the count the distinct set of dates captured.
Make this a discrete field (move from the measures section at the bottom of the data pane to the dimensions section at the top (above the line), and add to the tabular view
Now we can build the summary sheet.
Add Measure Names to the Filter shelf and this time filter by Total Sales, Average Sales and Change within Date Range. Add Measure Names to Rows and Measure Values to Text. Reorder the measures.
Format the Total Sales to be in $K, by selecting the Format option from the context menu of the Total Sales pill on the Measure Values shelf (hover on the pill and click the carrot/down arrow that appears – by formatting this way, we’re changing the display of this field for this sheet only).
Add Min Selected Date and Max Selected Date to the Detail shelf and set to be Exact Date. Format both these fields via the pill context menu to be the ‘March 2001’ format
Also add Months in Set to the Detail shelf.
Adjust the title of the sheet as below
Finally you need to set the background of the worksheet to the relevant purple (I used #8074a8), adjust the colours of all the fonts to white and adjust the size/style of the fonts in the table. Remove all gridlines/row banding etc, and you should have something like below
The Trend Line
By this point we’ve built all the calculated fields we need for this chart. This is a dual axis line chart, as we want the colour of the line for the selected dates to be different from the non selected ones, and we want to display a label for the highest sales in the selected timeframe.
Add Order Date to Plot to Columns, and set as a Continuous (green) pill set to Exact Date
Add Sales to Rows
Add Total Sales to Rows
Make the chart dual axis, and synchronise axis.
Adjust the colours of the Measure Names colour legend
On the Label shelf of the Total Sales marks card, set to label the maximum value only
On the All Marks Card, add Min Selected Date and Max Selected Date to the Detail shelf and set to Exact Date.
Right click on the Order Date To Plot axis and Add Reference Line
Create a reference band that starts at the constant Min Selected Date, ends at the constant Max Selected Date, is bounded by dotted lines and shaded between
Hide the Sales and Total Sales axis, format tooltips and adjust the row & column dividers.
Change the title and you should get to
The donut chart
Donut charts are 2 different sized pie charts on top of each other, created using a dual axis chart. On the Rows shelf type in MIN(0). Then type the same next to it. This gives you two axis and two marks cards.
We only care about information related to the selected dates for this chart, so we can add Order Date To Plot Set to the Filter shelf, which by default will just restrict the information to the data ‘in’ the set.
Change the mark type of the 1st MIN(0) marks card to Circle and add Sales to the Tooltip shelf. Adjust the size of the charts/mark.
Change the mark type of the 2nd MIN(0) marks card to Pie and add Sales to the Angle shelf. Add State to the Detail shelf. Sort the State field by Sales descending.
Note – the circles might look the same at this point, but if you hover over the bottom one, you should see that it’s segmented by State.
We need some new fields now to help us identify the top ranking states.
Sales Rank
RANK(SUM([Sales]))
This is a table calculation, so it’s best to see how this field will work in a table view – build one out as below, and set the table calculation on the Sales Rank field as shown
We’re now going to ‘group’ the ranks into the top 3 and everything else
Sales Rank Group
IF [Sales Rank]<=3 THEN [Sales Rank] ELSE 10 END
We can now use this Sales Rank Group field to colour the pie chart. On the 2nd MIN(0) marks card, add Sales Rank Group to the Colour shelf. Adjust the table calculation to compute using State as above, then change the field to be Discrete (blue). Adjust the colours to suit.
Now make the chart dual axis, and synchronise the axis. Adjust the size of the 1st MIN(0) circle to be smaller than the pie. If it’s not showing, right click on the right hand axis and move marks to back. Colour the circle white. Adjust tooltips to suit and hide axis, column/row dividers etc. Update the title. You should have
The top 3 states table
Add Order Date To Plot Set to Filter
Add State to Rows and Sales to Text and sort descending.
Add Sales Rank to Filter and set to At Most is 3. This will just show the top 3 states.
Add State to Text
Add a Percent of TotalQuick Table Calculation to the existing Sales field that’s on the Text shelf (via the context menu of the pill)
Add another instance of Sales back onto the Text shelf
Adjust / format the font size and layout of the fields on the Text shelf
Add Sales Rank to the Size shelf and set to be discrete (blue) and set the mark type to be Text. Adjust the size of the marks – it’s likely it’ll need to be reversed and the range adjusted.
Hide the State field on Rows, adjust the font colours, remove row banding and row/column dividers. You should end up with…
The map
Add Order Date To Plot Set to the Filter shelf
Add State to Detail – this should create a map (edit locations to be US if need be – Map -> Edit Locations menu)
Add Sales to the Colour shelf
Edit the colour range to a suitable purple range ( I set the darkest colour of the range to #6c638f)
Adjust the map layers (Map -> Map Layers) so only the option highlighted below is selected.
Adding the interactivity
Add all the sheets to the dashboard, using vertical and horizontal containers to arrange the relevant layout. Then add a Set Action (Dashboard > Actions > Add Action > Change Set Values). The Set Action should be configured as below :
And fingers crossed, you should now be able to select marks on the trend line and see all the other charts, except the initial summary table, all update. My published viz is here.
A colourful #WorkoutWednesday challenge this week, courtesy of Ann Jackson incorporating pie charts, top N functionality and interactivity and a highlight table. Pie charts can cause much debate amongst the data viz community and if this one was just representing the multitude of sub-categories, it certainly wouldn’t be ideal. But when the core aim is to simply present 2 key measures (those in the top N against the rest), the pie is a familiar and effective visual. In this instance, the outer ring segmenting all the sub-categories provides additional context without detracting from the main purpose of the viz.
So lets build…
Creating the core calculations
Building the Pie Chart
Building the Highlight Table
Adding the Interactivity
Creating the core calculations
First up, we’re going to need a parameter to define the ‘Top N’. Create an integer parameter with a range from 1 to 17, that steps every 1 interval, and is defaulted to 5.
pTopN
Next we’re going to use a Set to capture the Sub-Categories that are in the Top N Sales. Right click on Sub-Category -> Create ->Set. Use the Top tab to define a set captures the Sum of Sales that is based on the pTopN parameter.
Now, we want to create a grouping of those in and out of the set, which will be used as part of the highlight table
Sub-Cat Group
IF [Sub-Category Set] THEN ‘IN TOP ‘ + STR([pTopN]) ELSE ‘ALL ELSE’ END
Pop all these fields out into a table so you can see what’s going on as you change the pTopN parameter. Sort the Sub-Category by Sales descending.
Now we need to identify the % value of Sales for the Sub-Categories that are in the Top N (this is the label on the darker segment of the central pie chart), so for that we need
Total Sales
{FIXED:SUM([Sales])}
Top N Sales (in hindsight, this should have been named Sales per Group or similar)
{FIXED [Sub-Category Set] : SUM([Sales])}
Top N Sales %
IF ATTR([Sub-Category Set]) THEN SUM([Top N Sales])/SUM([Total Sales]) END
Format this to percentage with 0 dp.
Adding to the table, we can see the values
The final field we need in order to build the pie, is an additional one to store the label text
Label:SubText
IF [Sub-Category Set] THEN ‘TOP ‘ + STR([pTopN]) END
Building the Pie Chart
To achieve this we’re going to build a dual axis pie chart, where one pie is used to define the In/Out of Top N segmentation in the centre, and the other pie is used to create the outer ring.
Create an axis by typing in MIN(0) onto the Rows shelf, and then adding another instance of MIN(0) next to it. This will generate 2 marks cards, which is where the fields to build the pie charts will be placed.
In the first MIN(0) marks card, change the mark type to Pie, then add Top N Sales to the Angle shelf and Sub-Category Set to the Colour shelf. Adjust colours to suit. Then add Top N Sales % and Label:SubText to the Label shelf. Adjust size of the view and the chart to suit. Also remove all text from the Tooltip.
Positioning the text is a bit fiddly. If you click on the text so the cursor changes to a cross symbol, you can then drag it to a better location. However, when you change the Top N parameter, the text will move. You can go through each parameter value and reposition the text each time (which I did.. it wasn’t too onerous for 17 values), however I found when published to Tableau Public, the positioning wasn’t honoured. Ann’s solution was the same, so I didn’t get too hung up on this, although if anyone resolved it, I’d love to know!
Now on the 2nd MIN(0) marks card, again change the mark type to Pie, and this time add Sales to the Angle shelf and Sub-Category to Colour. Sort the Sub-Category field by Sales descending. Additionally add Sub-Category Set to the Detail shelf (this will be needed later on to make the interactivity work). Edit the colour palette to use the Hue Circle options. Adjust the size of the pie chart. Adjust the tooltip too.
Now make the chart dual axis and synchronize the axis. If the colourful chart is displayed ‘on top’, then right click on the right hand axis and select move marks to back. Adjust the sizes of both pies, so the colour wheel is slightly larger than the other one.
Now hide the axis, and remove all borders and gridlines.
Building the Highlight Table
I’ve built the highlight table as a bar chart. Start off by adding Sub-Category Set, Sub-Cat Group and Sub-Category to Rows. Sort Sub-Category by Sales descending. Then type in MIN(1) into the Columns shelf.
Now add subtotals via the Analysis > Totals > Add all Subtotals menu. This adds 2 additional rows to each section
But we don’t want the ‘grand total’, so click on the Sub-Category Set context menu, and uncheck Subtotals
To position the totals at the top, go to Analysis > Totals > Column Totals To Top
Then add Sub-Category to the Colour shelf, and adjust the colour of the Total bar to white
We now need to get some text onto those bars, but we need some additional calculations to help up with this. Firstly, we want to get the rank of the Sub-Category. We’ll use a table calculation for this
Sales Rank
RANK(SUM([Sales]))
We also need a way to identify the Total rows differently from the main Sub-Categories. I referred to this Tableau KB for help here, and subsequently created
Size
SIZE()
To see what this is doing, add Size to the Label shelf, and adjust the table calculation setting to compute by all fields except the Sub-Category Set. The size of the total rows is 1.
Based on this logic, we can then create
LABEL:Bar
IF [SIZE]=1 THEN ‘SUBTOTAL FOR GROUP’ ELSE ‘#’+STR([Sales Rank]) + ‘ ‘ + ATTR([Sub-Category]) END
Add this to the Label shelf instead of the Size field and adjust the table calc settings as above. Align left. Then add Sales to the Label shelf too and adjust so its on the same row. Adjust the tooltip too.
Now hide the Sub-Category Set and the Sub-Category fields. Right click on the ‘IN TOP x’ text and Rotate Label, then click on Sub-Cat Group text and Hide Field Labels for Rows. Format the header text to suit.
Hide the MIN(1) axis, and set columns and gridlines to None. Adjust the Row dividers to be darker
Adding the Interactivity
Add the 2 sheets onto a dashboard, and add a Highlight Dashboard Action, that on Hover of either of the charts, it highlights the other chart based on the Sub-Category Set only.
I think that’s covered everything. My published viz is here.
Donna + DataViz 