In this week’s #WOW2022 instalment, Luke set this map-based challenge to visualise COVID-19 cases across the continental US states on 30 December 2021.
Each ‘pyramid’ on the map represents a county within a state, with the height and colour of the pyramid indicating the volume of cases recorded.
Luke provided a workbook with some pre-modelled data as a starting point, so I used that.
To familiarise myself with the data I built out a basic table, focusing on the State of Idaho (ID) only.
Clat10 and Clon10 are the latitude and longitude positions of the County and metrics.caseDensity is the key measure used to indicate the volume of cases.
To draw the pyramid, we need to plot 3 marks for each County, 1 for each point in the triangle. This means we need to densify the data so we have multiple rows for each County. If we add Point as a discrete dimension (blue pill) to the table, we can see we can generate up to 20 rows per County.
This is obviously excessive – we only need Points 1-3, so for simplicity, we can add Point to Filter and limit to just 1, 2 and 3. I added the filter by duplicating the Point pill from Rows (hold ctrl and then click and drag the pill from Rows to Filter – this added the filter as a discrete dimension and I could just select options 1, 2, 3).
With only 3 points, we can now build the lat and long coordinates for each point. I am assuming point 1 is the bottom left point, 2 is the top point, 3 is the bottom right.
Triangle Long
CASE [Point] WHEN 1 THEN [Clon10] – 0.1 WHEN 2 THEN [Clon10] WHEN 3 THEN [Clon10] + 0.1 END
As the requirements stated the base of pyramid was 0.2 longitude points wide, then the bottom left and bottom right points need to be 0.1 points to the left or right of the County longitude, while the peak is aligned centrally.
Triangle Lat
CASE [Point] WHEN 1 THEN [Clat10] WHEN 2 THEN [Clat10] + ([metrics.caseDensity]/60) WHEN 3 THEN [Clat10] END
The latitude of the left and right bottom points are aligned with the County latitude, while the height is an additional 1/60 of the metrics.caseDensity value above the County longitude.
Change both the Triangle Lat and Triangle Long fields to be of the Geographic role type of Latitude or Longitude accordingly (right click field -> Geographic Role -> Latitude/Longitude).
Adding these into the table, we can see how the coordinates are forming
So with this, we can now build the map
Add State to Filter and exclude AK, HI, PR (Alaska, Hawaii, Puerto Rico).
Double click Triangle Lat then double click Triangle Long to add them to the sheet – a map should automatically be presented.
Add County to Detail, and a mark should appear for every County.
Add Point to Detail and change to a dimension – there should now be 3 marks per County forming triangles. Zoom in to really see this.
Change the mark type to line and the points all join up to form the pyramid.
Add metrics.caseDensity to Colour, then edit the colour. Choose the red-gold colour palette, then click the Advanced button, and first change the centre to 75, then set the start & end value to 0 and 200.
Modify the Tooltip and reduce the Size of the line. Zoom back out
Finally amend the map background ( Map menu -> Background Layers). Uncheck base and land cover, check terrain and coastline. Uncheck Country/Region Names and State/Province Names.
Hide the null indicator and then place on a dashboard. Done!
For the Salesforce Dreamforce (#DF22) conference, Lorna set this challenge based on using Salesforce data. You could access the data either by creating a Salesforce Developer account, or using the provided csv data set. I chose to use the latter.
I’m not overly familiar with the SF data, so it’s possible in the course of this blog, I may have missed a field in the data set that could have been used instead of whatever technique I describe. Feel free to let me know in the comments if this is the case.
Setting up the calcs
After connecting to the data, the first step was to amend the amount based on whether the Stage Name was ‘Closed Lost’ or not.
Revised Amount
IF [Stage Name]=’Closed Lost’ THEN -1 * [Amount] ELSE [Amount] END
I set the format of this field to be Currency £M with 1 dp.
Now the crux of the waterfall, is that the position of the mark that needs to be plotted it is based on the cumulative sum of the values displayed. So for this we need to create a table calculation
Revised Amount (Running Total)
RUNNING_SUM(SUM([Revised Amount]))
I set the format of this field to be Currency £M with 0 dp.
Let’s put the data into a table to see what’s going on.
The table calculation of the 2nd column (Revised Amount (Running Total)) is computing ‘down’ the table, so for this to work as we require the order of the rows is important. I just manually sorted by dragging each Stage Name into the relevant position. and then added a grand total.
With these 2 fields, we can now build the basic waterfall.
Build the Waterfall
I chose to duplicate the above sheet and then move the pills around as follows
Move Stage Name to Columns
Move Revised Amount (Running Total) to Rows. Amend the table calculation setting so it is set to explicitly compute using Stage Name.
Change the Mark Type to Gantt Bar
Move Revised Amount from Text to Size, then double click on the Revised Amount pill so it becomes editable and add * -1 to the end, to invert the value
Add Row Grand Total
Add Stage Name to Colour and manually adjust each colour accordingly
Add Revised Amount to Tooltip and adjust.
Edit Revised Amount (Running Total) axis (right click axis -> Edit Axis) and amend axis title
Format the Grand Total label (right click label -> Format) and amend says ‘Total’ and is bold.
Hide the Stage Name label at the top of the chart (right click label -> hide field names for columns)
Labelling the bars
Labelling the bars isn’t quite as simple as you think it might be, as the position of the label differs depending on whether the Revised Amount is positive (at the top) or negative (at the bottom).
So we need to create some new calculations
LABEL – +ve Revised Amount
IF SUM([Revised Amount])>=0 THEN SUM([Revised Amount])
formatted to £M to 1 decimal place
LABEL – -ve Revised Amount
IF SUM([Revised Amount])<0 THEN SUM([Revised Amount]) END
formatted to £M to 1 dp
Adding these to the tabular layout we had earlier you can see how these fields are behaving.
Switch back to the waterfall chart, and add LABEL – +ve Revised Amount to the Label shelf. Format the label to be smaller font (I chose 8pt), bold and explicitly aligned to the top.
Now duplicate the Revised Amount (Running Total) pill that is on Rows to add another instance directly next to it. I do this by holding down ctrl as I then click and drag the pill. This will create another axis.
On the second marks card, remove LABEL – +ve Revised Amount from Label and add LABEL – -ve Revised Amount instead. Adjust the label so it is aligned at the bottom instead.
Now make the chart dual axis and synchronise the axis.
Then hide the right hand axis, and remove all divider and gridlines.
Then add to the dashboard. I used a vertical layout container so I could add blanks of 1 pixel in height with a black background colour to present the this black lines separating the header and footer text.
Kyle set the challenge this week, revisiting his favourite topic – baseball. The aim was to build what I’ve often referred to as a ‘rocket chart’, as it charts progress from a single ‘launch’ date/point. However having had a quick google, I can’t see any other reference to this being used for this type of chart….no idea where it came from <shrug>.
Anyway, the requirement was to compare the profiles of when home runs (HRs) had been accumulated over the course of a player’s career, restricting to just the players who are in the all-time top 10. These players hadn’t necessarily played during the same years or even decades, so there was a need to baseline the information according to the days since they started. Kyle also threw in the requirement that this was to be an LoD based challenge only, with no use of table calculations.
Build the basic chart
As mentioned above, we first need to ascertain how many days have passed between when the player hit their first home run, and the subsequent dates. We use a FIXED LoD to work out the minimum date per player
Min Date Per Player
DATE({FIXED [Player] : MIN([Date])})
And with that we can the work out the number of days that have passed
Days Since Min Date
DATEDIFF(‘day’,[Min Date Per Player], [Date])
And with this, we can quickly build out the main crux of the chart. Add Days Since in Date to Columns, and change to be a continuous dimension. Add Career HR to Rows and amend the aggregation to use AVG rather than SUM, as I found there looked to be duplicate records for some dates for the same player. Add Player to Detail.
Colouring the lines
Kyle provided a custom colour palette to use based on the team colours of the player. I updated by preferences.tps file with this data, and closed and reopened Tableau Desktop to ensure it picked it up. For more information on working with custom colour palettes see this Tableau help article.
Along with the player colours, we also need to identify which player has been selected.
For that we need a parameter to define who the selected player is
pPlayer
string parameter using a List where the values are added from the Player dimension. this causes the default to be set to Albert Pujols.
Show the parameter on the display.
We can now create
Is Selected Player?
[Player] = [pPlayer]
which will return a boolen true/false.
Kyle stated that we should be able to set the colours without having to manually click against every Player|T or F combination.
Now I managed this when I first built my solution, but in writing this blog and trying to replicate the steps, I’m not getting the same behaviour. So I have managed to come up with another way. The gif below hopefully demonstrates, but I’ll list the steps too.
Move the Player pill from Detail onto Colour
Edit the Is Selected Player field to just return True (use // to just comment out the original calculation)
Add Is Selected Player to the Detail shelf, then click the detail icon to the left of the pill and change it to Colour. This is a way to get multiple pills on the Colour shelf. Dragging will just replace the field being used for colour.
The colour legend dialog box should display a list of <Player>, True entries (if the legend isn’t displaying go to Worksheet > Show Cards > Reset Cards – you may then have to add the parameter to the display again).
Edit the colour legend, select the MLB HR Top 10 colour palette and click Assign Palette. This will automatically assign the relevant colour to each entry, since they were added based on alphabetical order.
Re-edit the Is Selected Player field, so it is back to [Player] = [pPlayer].
The entries in the colour legend will now only list one <Player>, True entry and the rest all false.
Edit the colour legend, and multi-select (ctrl-click) all the False entries, and then select the lightest shade of grey from the Seattle Grays palette. This should give you the desired display.
Select Alan Rodriguez from the parameter control. Both Albert, False & Alan, True should now be coloured. Edit the colour legend again and manually set the Albert Pujols, False entry to the same grey shade.
Now if you select any other player, only 1 line should be coloured, and it should be coloured to the corresponding player’s colour.
Setting the Tooltip
Add Season HR to Tooltip and change the aggregation to AVG. Add Date to Tooltip too and set it to be an Attribute. Amend the tooltip accordingly.
Adding the highest season HR indicator
Firstly we need to determine what the maximum Season HR value is per player
Max Season HRPer Player
{FIXED [Player]: MAX([Season HR])}
With this, we then want to get the corresponding Career HR value for that same time.
Career HR | Max Season HR
IF [Season HR] = [Max Season HR Per Player] THEN [Career HR] END
Add this field to Rows and change the aggregation to Avg.
Set to Dual Axis, Synchronise Axis and then set the mark type to Circle. Adjust the size of the circle mark slightly if need be.
Labelling the lines
On the Line marks card, add Player and Career HR to the Label shelf. Adjust the aggregation of Career HR to Avg. Edit the label, so only line ends are labelled. Adjust the font size to something quite small, and set the colour to Match Mark Colour.
Finally remove all gridlines, row & column dividers, and hide the axis. Title the chart.
When added to a dashboard, I then used a floating text object for the introductory text and positioned the parameter as a floating object underneath the text.
Sean set the challenge this week and went retro, revisiting a challenge from 2017 originally set by Emma Whyte to build a Marrimeko chart.
Hang on… a what chart? Marrimeko…???
A good place to understand what a Marrimeko chart actually is, is this blog post by Tableau Visionary & Ambassador, Jonathan Drummey. This leads onto this post which explains the steps to help build.
The main concept of this type of chart is to show part-to-whole relationships across two variables at once. In the above for each job title, we have a split vertically based on proportion by gender; but the proportion of people with each job title is also being represented horizontally by the width of the ‘bars’. Both the x and the y axis are up to 100%.
I was actually around when the original challenge was set, so have my solution from then already on my Tableau Public profile. But it was a long time ago, with an older version of Tableau, so I built this from scratch (using the referenced blogs as a quick refresher). This blog will take you through the steps I took (which actually didn’t end up that different from the last time).
The data
The data set isn’t very large and contains all the information we will need, but not quite in the structure we might expect
The Sub-Type = Total field contains the % we will need to define how the width of the bars should be split (all the Total values add up to 100% or just about).
Whereas the combination of the Sub Types of Male and Female define how the height of the bars should be split (Male + Female = 100% for each Job Type).
So when we come to build, we ideally want to ‘filter out’ the Sub-Type = Total field, but we still need to retain this information to build the viz.
We need to get the % values associated to each Total row to be reflected against the Male/Female rows.
We’ll create an LoD for this
Job Type Percentage
{FIXED [Job Type]: SUM(IF [Sub-Type] = ‘Total’ THEN [Percentage]END )}
format this to % with 0 dp
If we pop the data out into a tabular format as below and add Sub-Type to the Filter shelf so it excludes the Total row, we can see we have essentially transposed the values which were stored against the Total row into it’s own column.
Building the Marrimeko chart
As stated above, the Job Type Percentage field defines the width of the bars we’ll be displaying. But before we can get to the point, we also need to define where on the x-axis each mark should be positioned. This too is based on Job Type Percentage, but is the cumulative value.
On a new sheet add Job Type to Rows, Filter by Sub-Type to exclude Total, and add Job Type Percentage to text. Sort by Job Type Percentage descending.
Now add a Running Total Quick Table Calculation to the Job Type Percentage field, and the cumulative values will display.
It is these values we need to plot on the x-axis.
Duplicate the sheet.
Edit the table calculation against the Job Type Percentage field, so that it is explicitly set to compute using Job Type. This is important to ensure the calculation is retained regardless as to where we put the pill on the canvas.
Now move this pill from the Text shelf to Columns, and change the mark type to Gantt Bar. Move Job Type from Rows to the Detail shelf. Reapply the Sort to the Job Type field so its sorting by Job Type Percentagedescending
The markers for the Gantt should all be positioned at the correct position.
Now add Percentage to Rows and change the mark type to Bar.
Add Job Type Percentage to the Size shelf, then click on the shelf, and change to be Fixed and aligned Right.
Now add Sub-Type to Colour. Manually drag the values in the COlour legend to re-order (so Male is listed first) and adjust colours to suit.
Ta dah! The crux of the chart.
Now to add the ‘bells and whistles’.
The chart is labelled, but only for the Entry Job Type. We need a calculated field to manage this.
Label: Sub Type
IF [Job Type] = ‘Entry’ THEN [Sub-Type] END
Add this to the Label shelf, and set to Show Mark labels. If it doesn’t show (like it didn’t on mine), change the field to be an attribute (I need to do a dig on why this is required… I think it’s something to do with the table calcs….).
I tried to use the alignment setting of the label to set to ‘top left’, but while they would left align they wouldn’t move to the top. I manually moved them instead – simply click on the label and when the cursor changes to a cross, drag the label to the desired position.
Do this for both labels, and adjust the formatting of the label too (I set it to 12pt bold).
Remove all gridlines, axis lines, zero lines etc.
Add a 50% reference line on the y-axis. Right click on the Percentage axis ->Add Reference Line. Add a table level constant of 0.5 which is a thin dotted grey line that is almost invisible (due to the colour selected).
Hide the axes (uncheck show header).
The tooltip has a minor nuance in that it refers to ‘Women’ & Men rather than Male & Female, so we need a field for this
Gender
CASE [Sub-Type] WHEN ‘Female’ THEN ‘Women’ WHEN ‘Male’ THEN ‘Men’ END
Add this to the Tooltip shelf and adjust the tooltip accordingly.
Labelling the axes
The final viz has labels on both the x and y axis, but these are all managed by text/image objects positioned ‘cleverly’ on the dashboard. It’s a bit of trial and error to get everything aligned as required.
To label the Job Type, I used a horizontal container, positioned beneath the chart, with several text objects, some of which had the text rotated.
The Equal Proportions and Less Equality text are both floating text objects. I saved an ‘arrow’ image from the internet and added a floating image object too.
Lorna set this challenge this week to test some LoD fundamentals. My solution has a mix of LoDs and table calculations – there weren’t any ‘no table calcs’ allowed instructions, so I assumed they weren’t off limits and in my opinion, were the quickest method to achieve the line graph.
Note – For this challenge, I downloaded and installed the latest version of Tableau Desktop v2022.2.0 since Lorna was using the version of Superstore that came with it. The Region field in that dataset was set to a geographic role type. I built everything I describe below using the field fine, but when I extracted the data source at the end and ‘hid all unused fields’ before publishing, the Region field reported an error (pill went red, viz wouldn’t display). To resolve, I ended up removing the geographic role from the field and setting it just to be a string datatype. At this point I’m not sure if this an ‘unexpected feature’ in the new release…
Ok, let’s get on with the build.
Building the basic viz
I started by building out the basic bar & line chart. Add Region to Rows, Order Date as continuous month (green pill) to Columns and Sales to Rows. Change the mark type to bar, change the Size to manual and adjust.
Drag another copy of the Sales pill to Rows, so its next the other one. Click on the context menu of that 2nd pill, and select Quick Table Calculation -> Moving Average
Change the mark type of the 2nd Sales pill to line.
Now click on the context menu of the 2nd Sales pill again and Edit Table Calculation. Select the arrow next to the ‘Average, prev 2, next 0’ statement, and in the resulting dialog box, change the Previous values to 3 and uncheck the Current Value box
At this point you can verify whether the values match Lorna’s solution when set to 3 previous months.
But, we need to be able to alter the number of months the moving average is computing over. For that we need a parameter
pPriorMonths
integer parameter, default to 3, that ranges from 2 to 12 in steps of 1.
Then click on the 2nd Sales pill and hold down Shift, and drag the pill into the field pane on the left hand side. This will create a new field for you, based on the moving average table calculation. Edit the field. Rename it and amend it so it references the pPriorMonths parameter as below
Moving Avg Sales
WINDOW_AVG(SUM([Sales]), -1*[pPriorMonths], -1)
Adjust the tooltip for both the line and the bar (they do differ). Ignore the additional statement on the final bar for now.
Colour the line black and adjust size. Then make the chart dual axis and synchronise axis. Hide the right hand axis. Remove Measure Names from the Colour shelf of both marks cards.
Colouring the last bar
In order to colour the last bar in each row, we need 3 pieces of information – the value of Sales for the last month, the moving average value for the last month, and an indicator of whether one is bigger than the other. This is where the LoDs come in.
First up, lets work out the latest month in the dataset.
finds the latest date in the whole dataset and truncates to the 1st of the month. Note, this works as there’s sales in the last month for all Regions, if there hadn’t been, the calculation would have needed to be amended to be FIXED by Region.
From this, we can get the Sales for that month for each Region
Latest Sales Per Region
{FIXED [Region] :SUM( IF DATETRUNC(‘month’, [Order Date]) = [Latest Month] THEN [Sales] END)}
To work out the value of the moving average sales in that last month, we want to sum the Sales for the relevant number of months prior to the last month, and divide by the number of months, so we have an average.
First let’s work out the month we’re going to be averaging from
This subtracts the relevant number of months from our Latest Month, so if the Latest Month is 01 Dec 2022 and we want to go back 3 months, we get 01 Sept 2022.
Avg Sales Last n Months
{FIXED [Region]:SUM( IF DATETRUNC(‘month’, [Order Date]) >= [Prior n Month] AND [Order Date] < [Latest Month] THEN [Sales] END)} / [pPriorMonths]
So assuming we’re looking at prior 3 months, for each Region, if the Order Date is greater than or equal to 01 Sept 2022 and the Order Date is less than 1st Dec 2022, get me the Sales value, then Sum it all up and divide by 3.
And now we determine whether the sales is above or below the average
Latest Sales Above Avg
SUM([Latest Sales Per Region]) > SUM([Avg Sales Last n Months])
If you want to sense check the figures, and play with the previous months, then pop the data into a table as below
So now we’re happy with the core calculations, we just need a couple more to finalise the visualisation.
If we just dropped the Latest Sales Above Avg pill onto the Colour shelf of the bar chart, all the bars for every month would be coloured, since the calculation is FIXED at the Region level, and so the value is the same for all rows associated to the the Region. We don’t want that, so we need
Colour Bar
IF DATETRUNC(‘month’, MIN([Order Date])) = MIN([Latest Month]) THEN [Latest Sales Above Avg] END
If it’s latest month, then determine if we’re above or below. Note the MIN() is required as the Latest Sales Above Avg is an aggregated field so the other values need to be aggregated. MAX() or ATTR() would have worked just as well.
Add this field to the Colour shelf of the bar marks card and adjust accordingly.
Sorting the Tooltip for the last bar
The final bar has an additional piece of text on the tooltip indicating whether it was above or below the average. This is managed within it’s own calculated field.
Tooltip: above|below
IF DATETRUNC(‘month’ ,MIN([Order Date])) = MIN([Latest Month]) THEN IF [Latest Sales Above Avg] THEN ‘The latest month was above the prior ‘ + STR([pPriorMonths]) + ‘ month sales average’ ELSE ‘The latest month was below the prior ‘ + STR([pPriorMonths]) + ‘ month sales average’ END END
If it’s the latest month, then if the sales is above average, then output the string “The latest month was above the prior x month sales average” otherwise output the string “The latest month was below the prior x month sales average”.
Add this field onto the Tooltip shelf of the bar marks card, and amend the tooltip text to reference the field.
Finalise the chart by removing column banding, hiding field labels for rows, and hiding the ‘4 nulls’ indicator displayed bottom right.
Creating an Info icon
On a new sheet, double click into the space within the marks card that is beneath the Detail, Tooltip, Shape shelves, and type in any random string (”, or ‘info’ or ‘dummy’). Change the mark type to shape and select an appropriate shape. I happened to have some ? custom shapes, so used that rather than create a new one. For information on how to create custom shapes, see here. Amend the tooltip to the relevant text. When adding to the dashboard, this sheet was just ‘floated’ into the position I was afte. I removed the title and fit to entire view.
Week 32 of #WOW2022 and Kyle set this challenge to build a dumbbell chart (also sometimes referred to as a barbell chart, a gap chart, a connected dot plot, a DNA chart). I first built one of these a long time ago, and have built many since, so chose not to refer to Ryan Sleeper’s blog Kyle so generously provided. I figured I’d have a go myself from memory. Looking back now, I actually started by going down the same route a Ryan, but then switched my approach in order to handle the tooltip requirements.
So here’s what I did.
Defining the calculations required
I’m going to start with these, as it’s then easier just to explain how everything then gets built. In reality, some of these calculations came along mid-build.
I decided to create explicit fields to store the Ownership values for the 2015 & 2019 years ie
Ownership 2015
IF [Year]=2015 THEN [Ownership] END
Ownership 2019
IF [Year]=2019 THEN [Ownership] END
Both these fields I formatted to % with 0dp.
Given these, I can then work out the difference
Difference
SUM([Ownership 2019])-SUM([Ownership 2015])
also formatted to % with 0dp
and finally, I can work out whether the change exceeds 20% or not
Difference>0.2
[Difference]>0.2
These are all the fields required 🙂
Building the Dot Plot
Add Age to Columns as a discrete dimension (blue pill) and Ownership 2015 to Rows. This will create a bar chart.
Then drag Ownership 2019 onto the canvas, and release your mouse at the point it is over the Ownership 2015 axis and the 2 green columns symbol appears.
This will have the effect of adding Measure Names and Measure Values to the view.
Change the mark type to Circle and move Measure Names from Columns onto Colour. Adjust colours accordingly.
Show mark labels and adjust so they are formatted middle centre, and are bold. Increase the size of the circles so the label text is completely within the circle, and the font colour should automatically adjust to white on the darker circles and black on the lighter ones.
Add Ownership 2015, Ownership 2019 and Difference onto the Tooltip shelf, and adjust the tooltip to suit. Note – I chose to adjust the wording of the tooltip to not assume there was always an increase. I used the word ‘change’ instead and as a result applied the custom formatting of ▲0%;▼0% to the Difference field that was on the Tooltip shelf.
So my tooltip was
which generated a tooltip that looked like
Connecting the dots
Add another instance of Measure Values to Rows alongside the existing one. This will generate a 2nd Measure Values marks card.
On that marks card, remove Measure Names from Colour and add Difference>0.2 to the Colour shelf instead, and adjust the colours.
Change the mark type to Line and add Measure Names to the Path shelf.
Move the Difference pill from the Tooltip shelf to the Label shelf. Edit the label controls, so the label only displays at the start of the line, which should make the label only show at the bottom. Adjust the label alignment so it is bottom centre.
Delete all the text from the Tooltip of the ‘line’ marks card.
Now make the chart dual axis and synchronise the axis. You’ll notice the bars sit on top of the circles, and the difference text is not displaying under the circles.
To remedy this, first, right click on the right hand axis, and select move marks to back. The lines should now be behind the circles.
Then, on the lines marks card, edit the Label text and just add a single carriage return in the dialog box, so the text is shifted down.
Adding Age to be displayed at the top
The quickest way to do this is to double click in the space on the Columns shelf, next to the Age pill, and type in ‘Dummy’ or just ” if you prefer. This has the affect of adding another ‘dimension’ into the view and as a result the Age values immediately get moved to the top.
Now just remove all axis, all gridlines, all row & column dividers. Then uncheck show header against the Dummy pill and hide field labels for columns for the Age header (right click on the Age label in the view itself to get this option, not the pill). Then format the Age text to be a bit bigger and bolder, and also increase the Size of the lines mark type. And that’s your viz 🙂
Additional Note
If you had followed Ryan’s blog post, then you would still have needed to create separate calculated fields to store the ownership values for 2015 and 2019, but these would have had to been LOD fields eg
Ownership 2015
{FIXED [Age]:SUM(IF [Year]=2015 THEN [Ownership] END)}
and a similar one for Ownership 2019. The other 2 fields related to the difference would then need to reference these instead.
Sean set this fun and very relevant challenge this week displaying key measures (KPIs) along with further details for a selected measure displayed in a horizontal layout rather than the more traditional long-form, using ranking. The ‘gotcha’ part of this challenge was ensuring the tooltips displayed the measures in the right format, ie $ for Sales and Profit, % for Profit Ratio and a standard number to 0 dp for # of Orders.
We’ll start by
Building the KPI sheet
The data source I connected to already had Profit Ratio included, but if yours doesn’t, you’ll need to create as
Profit Ratio
SUM([Profit])/SUM([Sales])
You’ll also need to create
# of Orders
COUNTD([Order ID])
On a new sheet, double-click Sales and then double-click Profit to add them both to a sheet, and then use Show Me to display the fields as a text table.
Move Measure Names from Rows to Columns, then add Profit Ratio and # of Orders into the Measure Values section under the marks card. Re-order the measures into the required order.
Modify the format of each pill in the Measure Values section to the relevant format (millions, thousands, %)
Add Measure Names to the Text shelf, and adjust the text to be aligned middle centre and resize the fonts.
Hide the header (uncheck Show Header against the Measure Names in the Columns, and remove row dividers. Set the Tooltip not to show.
Building the Ranked Chart
The value to display in this chart is dependent on the KPI measure clicked on. We’re going to use a parameter to help drive this behaviour, so start by creating
Measure to Display
string parameter defaulted to ‘Sales’ containing 4 options: Sales, Profit, Profit Ratio, # of Orders
Based on the parameter, we need to determine which value to display
Selected Value
CASE [Measure to Display] WHEN ‘Sales’ THEN SUM([Sales]) WHEN ‘Profit’ THEN SUM([Profit]) WHEN ‘Profit Ratio’ THEN [Profit Ratio] WHEN ‘# of Orders’ THEN [# of Orders] END
On a new sheet, add Sub-Category to Rows and Selected Value to Text and sort by Sub-Category descending. Show the Measure to Display parameter on the sheet and play around with the different options to see how the viz changes. When you’re happy it’s working as expected, reset back to ‘Sales’.
We’ll need to display the rank value, so create
Rank Value
RANK_UNIQUE([Selected Value])
and format this to be a number with 0dp but pre-fixed by #
Add this into the table.
Now, to display this information, we need to distribute each Sub-Category across 5 rows and 4 columns which is based on the Rank Value. So let’s work out which row and column each entry should be in
Rows
IF [Rank Value]%5 = 0 THEN 5 ELSE [Rank Value]%5 END
If the rank is divisible by 5, then place in the 5th row, otherwise place in the row associated to the remainder when divided by 5.
Cols
IF [Rank Value] <=5 THEN 1 ELSEIF [Rank Value] <=10 THEN 2 ELSEIF [Rank Value] <=15 THEN 3 ELSE 4 END
Add these fields to the table, so you can see how they’re working.
The Rank Value, Rows and Cols fields are all table calculations, and should be set to explicitly compute using Sub-Category.
On a new sheet, add Sub-Category to Detail, Cols to Columns as a blue discrete pill and Rows to Rows as a blue discrete pill. Add Selected Value to Columns. Drag Sub-Category from Detail on to Label and adjust to align the labels left. Widen each row slightly, so the text is visible. Adjust the Colour of the bars to be a pale grey.
Change the Measure to Display parameter to ‘Profit’, and notice what happens to the bars & labels associated to those with a negative profit. They’re positioned where you’d expect, on the negative axis, but this doesn’t match Sean’s solution.
Sean has chosen to display the values as absolute values (although the tooltips display the negative values). This means we need
Abs Value to Display
ABS([Selected Value])
Drag this field and drop directly on top of the Selected Value pill on the Columns shelf – this will replace the measure being displayed.
Now that’s sorted, we can get the ranking added.
Double click in the space next to Abs Value to Display on Columns and type in MIN(0) to create an additional axis and create a 2nd marks card.
On the MIN(0) marks card, move Sub-Category to the Detail shelf and thenadd Rank Value to the Label shelf. Make sure the table calc is set to compute by Sub-Category as before.
Change the mark type of the MIN(0) card to GanttBar, set the size to the smallest possible, and colour opacity to 0. The change of the mark type, will have changed the position of the rank label to be aligned to the left of the mark.
Make the chart dual axis, synchronise the axis and change the mark type of the Abs Value to Display back to Bar. Remove Measure Names from both of the marks cards.
Hide both axis and the headers and remove grid lines and divider lines. Make the zero line on the columns a solid line, slightly thicker.
Formatting the Tooltips
The tooltips display the selected measure name and associated value, formatted appropriately. As we’re using a ‘generic’ field to display the value, we can’t format this field as we’d usually do. Instead, I chose to resolve this using dedicated fields to store each value based on the measure selected.
Tooltip: Sales
IF [Measure to Display]=’Sales’ THEN [Sales] END
format this to $ with 0dp.
Tooltip: Profit
IF [Measure to Display]=’Profit’ THEN [Profit] END
format this to $ with 0dp.
Tooltip: Profit Ratio
IF [Measure to Display]=’Profit Ratio’ THEN [Profit Ratio] END
format this to 5 with 2 dp
Tooltip: #Orders
IF [Measure to Display]=’# of Orders’ THEN [# of Orders] END
format this to a number with 0 dp.
Add all these fields to the Tooltip shelf on the Abs Value to Display marks card, and then amend the tooltip so all these fields are listed side by side with no spacing. As only one of the fields will only ever contain a value, the correctly formatted figure will display. The Tooltip will also need to reference the Measure to Display parameter to act as the ‘label’ for the value displayed.
Finally adjust the title of the sheet to also reference the Measure to Display parameter,
Adding the interactivity
Add the sheets to a dashboard, then add a parameter action
Select Measure
On select of the KPI sheet, update the Measure to Display parameter, passing through the Measure Names value. When the measure is unselected, revert the display back to ‘Sales’.
Bonus – Colour the bars
As an added extra to call out the fact the negative values were being displayed on the positive axis, my fellow #WOW participant, Rosario Gauna, chose to colour the bars.
Colour Bar
[Selected Value] < 0
Add this to the Colour shelf of the Abs Value to Display marks card and adjust colours according.
Erica set the challenge this week using a custom made dataset. The focus was to handle viewing different aggregations of the dates based on the date range selected, ie for short date ranges seeing the data at a day level, for medium ranges, view at the week level and then at a monthly level for larger ranges. This type of challenge has been set before, but the visual is typically a line chart rather than a heat map. The principles are similar though.
Determining the Date to Display
Each row in the data set has a Date associated to it. For this challenge we need to ‘truncate’ the date to the appropriate level depending on the date range and the date level selected by the user. By that I mean that if the level is deemed to be weekly, we need to ‘reset’ each date to be the 1st day of the week.
To start with, we need to set up some parameters which drives the initial user input.
pStart
Date parameter defaulted to 11/07/2022 (11th July 2022), with a display format of <weekday>, <date>
pEnd
As above, but defaulted to 24/07/2022 (24th July 2022).
pDateLevel
integer with values 0-3 as listed below, with a text Display As. Defaulted to ‘Default’. Note, I am using integers as we will be referencing them in IF statements later, and integers are more performant than comparing strings.
With these parameters, we can then build
Date To Display
DATE( IF [pDateLevel] = 0 THEN IF DATEDIFF(‘day’, [pStart], [pEnd]) < 28 THEN DATETRUNC(‘day’,[Date]) ELSEIF DATEDIFF(‘day’, [pStart], [pEnd]) < 90 THEN DATETRUNC(‘week’, [Date]) ELSE DATETRUNC(‘month’, [Date]) END ELSEIF [pDateLevel] = 1 THEN DATETRUNC(‘day’,[Date]) ELSEIF [pDateLevel] = 2 THEN DATETRUNC(‘week’,[Date]) ELSE DATETRUNC(‘month’, [Date]) END )
If the pDateLevel is set to 0 (ie Default), then compare the difference between the dates entered and truncate to the ‘day’ level if the difference is less than 28 days, the ‘week’ level if the difference is less than 90 days, else truncate to the month level (which will return 1st of the month). Otherwise, if the pDateLevel is 1 (ie Day), truncate to the day level, if it’s 2 (ie Week), truncate to the week level, else use the month level.
To see how this field is working, add Date and Date To Display to Rows, both as discrete exact dates (blue pills), display the parameter fields, and adjust the values. Below you can see that using the Default level, between 1st May and 24th July, the 1st day of the associated week against each date is displayed.
Building the Bar Chart
This is the simpler of the two charts to build, so we’ll start with this.
Add Employee Name to Rows, and Minutes to Columns. and sort by Minutes descending (just click the sort descending button in the toolbar). Add Project to Filter, select ‘A’ and then show the filter. Adjust the colour of the bars. I used #311b92
We need to restrict the data displayed based on the date range defined.
Dates to Include
[Date]>=[pStart] AND [Date]<=[pEnd]
Add this to the Filter shelf and set to True.
Now, we need to label the bars, and need new fields for this.
Duration (hrs)
FLOOR(SUM([Minutes])/60)
Using the FLOOR function means the value is always rounded down to the relevant whole number (eg 60.5 and above will still result in 60).
Format this field to be 0 decimal places with a ‘h’ suffix
Duration (mins)
SUM([Minutes])%60
Modulo (%) 60 means return the remainder when divided by 60. Format this to be 0 dp.
Add both these fields to the Label shelf, and adjust so the labels are displaying on the same line and are aligned to the left. You may need to increase the width of each row to see them.
Add Project to the Tooltip shelf and update, then remove all gridlines and the axis. Leave the Employee Name column visible for now. We’ll come back to this later.
Building the Heat Map
On a new sheet, add Employee Name to Rows and Date to Display as a discrete exact date (blue pill) to Columns. Add Minutes to Colour and a heat map should automatically display.
Go back to the bar sheet, and set both the Project and the Dates To Include filters to apply to the heat map worksheet as well (right click each pill on the filter shelf -> Apply to Worksheets -> Selected Worksheets and select the other one you’re building).
Then click the sort descending button in the toolbar, to order the data as per the bar.
Add both Duration (hrs) and Duration (mins) to the Label shelf and change the mark type to Square. Adjust the label so it is on the same line and left aligned (I added a couple of spaces to the front of the text so it isn’t so squashed).
Edit the colours of the heat map – I used a colour palette I had installed called Material Design Deep Purple Seq which seemed to match, but may not be installed by default.
Format the main heat map section to have a light grey background by right clicking on the table -> format and setting the shading of the pane only to pale grey
Then adjust the row dividers of the pane to be white, and the header to be pale grey (set the level to the max).
Adjust the column dividers similarly, setting the pane to be white, the header to none, and level to max
Next we want to deal with the column labels. By default they’re showing the date, but we want this to show something different depending on the date level being displayed.
Column Label
IF (([pDateLevel] = 1) OR (([pDateLevel] = 0) AND (DATEDIFF(‘day’, [pStart],[pEnd])<28))) THEN UPPER(LEFT([Weekday],3)) ELSEIF (([pDateLevel] = 2) OR (([pDateLevel] = 0) AND (DATEDIFF(‘day’, [pStart],[pEnd])<90))) THEN ‘w/c ‘ + IIF(LEN(STR(DATEPART(‘day’, [Date To Display])))=1,’0’+STR(DATEPART(‘day’, [Date To Display])), STR(DATEPART(‘day’, [Date To Display]))) + ‘-‘ + UPPER(LEFT(DATENAME(‘month’, [Date To Display]),3)) ELSE UPPER(LEFT(DATENAME(‘month’, [Date To Display]),3)) END
If the date level is ‘day’ or the date level is the default and the start & end are less than 28 days apart, then show the day of the week (1st 3 letters only in upper case).
Else if the date level is ‘week’ or the date level is the default and the start & end are less than 90 days apart, then show the text ‘w/c’ along with the day number (which should be 01 to 09 if the day is < 10) a dash (-) and then the 1st 3 letters of the month in upper case.
Else, we’re in monthly mode, so show the 1st 3 letters of the month in upper case.
Add this to the Columns shelf, then hide the Date To Display field (uncheck show header), hide field label for columns and hide field label for rows. Format the Employee Name field so its a different font (I changed to Tableau Book).
Again play around with the parameters and see the changes to the column label.
Finally, add Project to the Tooltip and update. You’ll need to adjust the formatting of the Date To Display field to get it into <day of week>, <date> format.
Adding to the Dashboard
When you add the two charts to the dashboard, you’ll need to set them side by side within a horizontal layout container. Remove the titles. They both need to be set to ‘fit entire view’, and the width of the heat map chart should be fixed (I set it to 870px), so it retains the space it stays in, even when you only have 1 month displayed.
Once you’re happy the order of the heat map employees matches the order of your bar chart, uncheck show header against the Employee Name field on the bar chart.
To get the bars to align with the heat map, I showed the title of the bar, removed the text and just entered a space. This dropped the bars so they just about aligned. You may need to tweak by increasing the size of the bars slightly.
Finally, you need to move your parameters around. I placed them in a horizontal container, whose background colour was set to pale grey. I then set the objects within to be equally spaced by setting the container to Distribute Contents Evenly.
I then altered the padding of each of the parameter objects to have outer padding = 0 and inner padding = 5 and added a pale grey border surrounding each.
Indeed! What is the time-weighted return? It’s not a term I’d ever heard of until Luke set the challenge this week. Fortunately he provided a link to a web page to explain all about it – phew!
I actually approached the challenge initially by downloading the data source, copying the contents of sheet2 into a separate excel workbook, and then stepping through the example in the web page, creating new columns in the excel sheet for each part of the calculation. I didn’t apply any rounding and ended up with slightly different figures from Luke. However after a chat with my fellow WOW participant Rosario Gauna, we were both aligned, so I was happy. I’d understood the actual calculation itself, so I then cracked open Desktop to begin building. This bit didn’t really take that long – most of the time on this challenge was actually spent in Excel, but shhhhh! don’t tell anyone 🙂
Building the calculations
I’m going to build lots of calculated fields for this, primarily because I like to do things step by step and validate I’m getting what I expect at each stage. You could probably actually do this in just 2 or 3 calcs. As I built each calc, I added it into a tabular view of the data. I’m not going to screen shot what the table looks like after every calc though.
To start though, I chose to rename some of the existing fields as follows :
Flows -> A: Flows
Start Value -> B: Start Value
End Value -> C: End Value
I did this because I have several new fields to make, and coming up with a meaningful name for each was going to be tricky – you’ll understand as you read on 🙂
First up, create a sheet and add Reporting Date as an exact date discrete blue pill to Rows then add the above 3 fields.
Now onto the calculations, and this all just comes from the website. Each calc builds on from the previous. I chose to add inline comments as a reminder. After I built each calc I added to the table.
//difference between end value and (flow+start) SUM([C: End Value]) – [D: A+B]
F: E/D
//Difference as a proportion of (start + flow) [E: C-D]/ [D: A+B]
I formatted this one to 3 decimal places so the figure showed on screen, but ‘under the bonnet’ Tableau will use the full number in the onward calcs.
At this point now, F: E/D stores what the article describes as the ‘HP’ value for each date. Now we need to start building the calcs we need to compute the TWR value.
G: 1+F
1+[F: E/D]
formatted to 3 dp
H: Multiply G values
//Running Product of column G [G: 1+F]*PREVIOUS_VALUE(1)
This is the core calculation for this challenge. This takes the value of the G: 1+F field in each row, and multiples it by the value of the G: 1+F field in the previous row. As each row ends being the product of itself and the former row, this ultimately builds up a ‘running product’ value.
Add these fields onto the table and explicitly set the table calculation of the H: Multiply G values field to compute using Reporting Date.
And finally we can create
I: TWR
//subtract 1 from the running product -1 [H: Multiply G values]-1
format this to be a % with 0dp. Add to the table and again ensure the table calc (and all nested calcs) are set to compute using Reporting Date
The final calculation needed is one to use to display the TWR value for the last date.
J: Latest TWR Value
//get the last value computed WINDOW_MAX([I:TWR])
format this to be a % with 0dp.
This function looks for the highest number across the whole table, and spreads that value across every row. Since the I: TWR field is cumulative, the highest number will be the last value. Add this onto the table and verify all the tab calcs are set to compute by Reporting Date.
And now you can build the viz
Building the Viz
Add Reporting Date as an exact date continuous green pill to Columns and I:TWR to Rows, setting the compute by of the table calculation as described above. Add J:Latest TWR Value to Detail, and again set the compute by.
Then update the title of the sheet to reference the J:Latest TWR Value field, amend the tooltips and update the label on the y-axis. Add to a dashboard and you’re done 🙂
Lorna Brown set the challenge this week to build this butterfly chart, so called because of the symmetrical display. I’ve built these before so hoped it wouldn’t be too taxing.
The data set isn’t very verbose, fields for gender & year specific values by age bracket
When connecting to this excel file, you need to tick the Use Data Interpreter checkbox which removes all the superfluous rows you can see in the excel file.
Now, I started by building a version that required no further data reshaping, which I’ve published here. This version uses two sets of dual axis, and I use the reversed axis feature to display the male figures on the left. This version required me to float the Population axis label onto the dashboard so it was central. Overall it works, the only annoying snag is that I have two 0M labels displayed on the bottom axis, since this isn’t a single axis. As a result, I came up with an alternative, which matches the solution, and which I’m going to blog about.
Reshaping the data
So once I connected to the data and applied the Use Data Interpreter option, I then selected the Males, 2021 and Females, 2021 columns, right clicked and selected Pivot
This results in a row for the Male figures and a row for the Female figures, with a column Pivot Names indicating the labels for each row. I renamed this to Gender, and the Pivot Values column to Population.
Building the Chart
On a sheet, add Age to Rows and Population to Columns and Gender to Colour. Set Stack Marks Off (Analysis > Stack Marks > Off).
You’ll see that both the values for the Male & Female data is displaying in the positive x-axis. We don’t want this. So let’s create
Population – Split
IF [Gender]= ‘Males, 2021’ THEN [Population]*-1 ELSE [Population] END
Replace the Population pill on Columns with Population Split
So this is the basic butterfly, but now we need to show the Total Population. Firstly, let’s create
Total Population per Age Bracket
{FIXED Age: SUM([Population])}
And then similarly to before, we need to display this in both directions against the Male and the Female side, so create
Total Population Split
IF [Gender]= ‘Males, 2021’ THEN [Total Population per Age Bracket]*-1 ELSE [Total Population per Age Bracket] END
Drag this field onto the Population Split axis until the two green column icon appears, then let go of the mouse. This is creating a combined axis, where multiple measures are on the same axis.
Move Measure Names from Rows to the Detail shelf and then change the symbol to the left, so the pill is also added to the Colour shelf. Adjust the colours of the 4 options accordingly.
Edit the axis and rename to Population. Then Format the axis, and set the scale to display in millions to 0dp
The tooltip needs to display % of total, so we’ll need
% of Total
SUM([Population]) / SUM([Total Population per Age Bracket])
Add this to the Tooltip shelf
We also need the population figures on the tooltip displayed differently, so explicitly add Population Split and Total Population Split to the Tooltip shelf. Use the context menu of the pills on this shelf, to set the display format in the pane to be millions to 2 decimal places. But this time, once set using the Number(Custom) dialog, switch to the Custom option and remove the – (minus sign) from the format string. This will make the values displayed against the Males which are plotted on the -ve axis, to show as a positive number.
Finally, set aliases against the Gender field, so it displays as Males or Females (right click on Gender > Aliases).
Now you have all the information to set the tooltip text.
Now add a title to the sheet to match that displayed, hide field labels for rows, and adjust any other formatting with row/column dividers etc that may be required, and add to a dashboard.
And that should be it. My published version of this build is here.
Donna + DataViz 