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.
Kyle returned this week to set this challenge to create a bullet chart which compared Win % for baseball teams in a year (bar) to the previous year (line), along with call out indicators (red circles) if the difference was greater than a user defined threshold. To add an extra ‘dimension’ to the challenge, a viz in tooltip showing the historical profile for the selected team was also required.
Building the basic bar chart
We’re going to use a parameter to define which year we want to analyse, as we can’t just filter by the Year as we need information about multiple years
pYear
integer field defaulted to 2018, formatted so the thousands separator does not display, and populated by Add values from the existing Year field
With this, we can create the following fields
Wins – CY
IF Year = [pYear] THEN [Wins] END
Wins – PY
IF Year = [pYear]-1 THEN [Wins] END
Games – CY
IF Year = [pYear] THEN [Games] END
Games – PY
IF Year = [pYear]-1 THEN [Games] END
and subsequently
Wins per Game – CY
SUM([Wins – CY]) / SUM([Games – CY])
and
Wins per Game – PY
SUM([Wins – PY]) / SUM([Games – PY])
Both these 2 fields are formatted to a custom format of ,##.000;-#,##.000 (this basically strips off the leading 0, so rather than 0.595 it’ll display as .595.
Now we have all these, we can build a dual axis chart.
Add League and Team to Rows and Wins per Game – CY to Columns. Sort by Wins per Game – CY descending. Display the pYear parameter.
Add Wins per Game – PY to Columns, make dual axis and synchronise the axis. Change the mark type of the CY card to a bar and the mark type of the PY card to Gantt. Remove Measure Names from the Colour shelf of both cards, and change the colour of the gantt bar to black.
Show mark labels for the bar mark and align left (expand the width of each row if need be).
Colouring the bars
The colour of the bars is based on whether the CY value is greater or less than the PY value. So we need to find the difference
CY-PY DIfference
[Wins per Game – CY]-[Wins per Game – PY ]
and then work out if the difference is positive or not
CY-PY Difference is +ve
[CY-PY Difference]>0
Add this to the Colour shelf of the bar marks card and adjust accordingly.
Adding the call out indicator
The red circle is based on whether the difference is above a threshold set by the user . A parameter is required for this
pChange
float field set to 0.1 by default; the values should range from 0.05 to 0.2 in 0.05 intervals
Show this parameter on the sheet.
We then can create
CY-PY Diff Greater than Change
IF ABS([CY-PY Difference])>[pChange] THEN ‘●’ ELSE ” END
The ABS function is used, as we want to show the indicator regardless as to whether the difference is a +ve or -ve difference. The ● image I get from copying from https://jrgraphix.net/r/Unicode/25A0-25FF. I use this page a lot, so keep it bookmarked.
Add this field to Rows, and then format the field so it is red and the font size is bigger (I used 12pt)
Now the viz just needs to be tidied up by
Hide field labels for rows
Rotate label of the League field
Format the font of the Team text
Reduce the width of the first three columns
Remove gridlines and zero lines
Adjust the row divider to be a dotted line at the 2nd level
Remove column dividers
Add an axis ruler to Rows
Uncheck Show Header on the axis to hide them
Tidy up the tooltip on the gantt mark type.
Building the Viz in Tooltip line chart
For this we need the Win % for every year, so we need
Wins per Game
SUM([Wins]) / SUM ([Games])
Add Team to Rows, Year to Columns (change to be a continuous, green pill) and Wins per Game to Rows
Add pYear to the Detail shelf, then add a Reference line to the Year axis to display the value of pYear as as dotted line
Right click on the reference line > format and adjust the alignment of the value displayed to be top centre.
Show mark labels and set to just show the min & max values for each line.
Colouring the lines
The colour of the line is based on whether the current year’s value is bigger or smaller than the previous year (ie the colour of the line matches the bar).
However, we can’t just use the fields we’ve already built, as because we have Year in the view, the data only exists against the appropriate year, so the difference can’t be computed. You can see this better if you build out the table below…
We need to ‘spread’ these values across every year for each team.
Wins per Game – CY Win Max
WINDOW_MAX(SUM([Wins – CY]) / SUM([Games – CY]))
Format this as you did above.
Add this to the view and amend the table calculation so it is computing by the Year field only. You should see that the value of this field matches the Wins per Game – CY value, but the same value is listed against every year now, rather than just the one selected.
Similarly, create
Wins per Game – PY Win Max
WINDOW_MAX(SUM([Wins – PY]) / SUM([Games – PY]))
format, and add this to the view too and adjust the table calculation as you did above.
So now we have this, we can work out whether the difference between these two fields is +ve or not
CY-PY Difference is +ve Win Max
[Wins per Game – CY Win Max]- [Wins per Game – PY Win Max]>0
Add this to the Colour shelf of the line chart. Verfify the table calculation is set to compute by Year only for both nested calculations, and then adjust the colours to match.
Finally tidy up the viz, to remove all headings, axis, gridlines, row/column dividers etc Set the background colour of the sheet to a light grey, then finally set the fit to be Entire View.
Adding the Viz in Tooltip
Go back the bar chart, and on the tooltip of the bar mark, adjust the initial text, then insert the line chart sheet via the Insert > Sheets > Sheetname option.
I adjusted the maxwidth & maxheight properties of the inserted text to be 350px each
If you hover over the bar chart now, you should get a filtered view of the line chart.
Final step is to put all this on a dashboard. My published viz is here.
Sean set the challenge this week to build a custom relative date filter. He uttered the words “this week is pretty straightforward” which always makes me a bit nervous…
The premise was to essentially replicate the out of the box relative date filter. I had a good play with Sean’s solution, hovering my mouse over various fields, to see if there were any clues to get me started. I deduced the filter box, was a floating container, which stored multiple other worksheets and some parameters used to drive the behaviour.
I worked out that I think I’d need to build at least 4 sheets – 1 for the main chart, 1 to manage the date part ‘tabbed’ selector at the top of the relative date control, 1 to manage the radio button selections within the relative date control, and 1 to display the date range.
So let’s crack on.
Building the initial chart
I used the Superstore data from v2022.1, so my dates went up to 31 Dec 2022.
We need to create a line chart that shows the dates at a user defined date level – we need a parameter to control this selection
pDateLevel
string parameter containing the relevant date parts in a list, and defaulted to ‘Week’. Notice the Value fields are all lower case, while the Display As as all proper case. The lower case values is important, as this parameter will be fed into date functions, which expect the string values to be lower case.
Then we need a new date field to show the dates at the level specified
Display Date
DATE(DATETRUNC([pDateLevel], [Order Date]))
Then add this field onto Columns as a continuous exact date (green pill) and add Sales to Rows. Show the pDateLevel parameter and test the chart changes as the parameter does.
Name this sheet Chart or similar. We’ll come back to it later, as we need to ensure this data gets filtered based on the selections made in the relative date control.
Setting up a scaffold data source
So there were no additional data source files provided, and also no ‘you must do it this way’ type instructions, so I decided to create my own ‘scaffold’ data source to help with building the relative date control. Doing this means the concept should be portable if I wanted to add the same control into any other dashboards.
I created an Excel file (RelativeDate_ControlSheet.xslx) which had the following sheets
DateParts
A single column headed Datepart containing the values Year, Quarter, Month, Week, Day
Relative Options
3 columns Option ID (containing values 1-6), Option Type (containing values date part, n periods, to date) and Option Text (containing values Last, This, Next, to date)
I added both of these sheets as new data sources to my workbook (so I have 3 data sources listed in my data pane in total).
Building the Date Part Selector
From the DateParts data source, add the Datepart field to both the Rows and Text fields. Manually re-sort by dragging the values to be in the required order.
Manually type in MIN(1) into the Rows shelf and edit the axis to be fixed from 0-1. Align the text centrally.
We’re going to need another parameter to capture the date part that will be clicked.
pDatePartSelected
string parameter defaulted to ‘quarter’ (note case)
We also need a new calculated field to store the value of the date parts in lower case
Datepart Lower
LOWER([Datepart])
and then we can use these fields to work out which date part has been selected on the viz
Selected Date Part
[pDatePartSelected] = [Datepart Lower]
Add this to the Colour shelf, and adjust colours accordingly (False is white).
Remove headers, column/row dividers, gridlines and axis, and don’t show tooltips.
Finally, we’re going to need to ensure that when a value is clicked on in the dashboard, it doesn’t remain ‘highlighted’ as we’re using the colouring already applied to define the selection made.
In the DateParts data source, create new calculated fields
True
TRUE
False
FALSE
and add both these fields to the Detail shelf, along with the Datepart Lower field. We’ll refer to all these later when we get to the dashboard.
Building the Relative Date Radio Button Selector
In the Relative Options data source, create the following fields
Row
IF ([Option ID] %3) = 0 THEN 3 ELSE ([Option ID] %3) END
Column
INT([Option ID]<=3)
These fields will be used to define where each entry in our scaffold data source will be positioned in the tabular display.
We then need to get all the text displayed as is expected, but firstly we need to define the parameter that will capture n number of days, which is incorporated into the label displayed
pSelectPeriods
integer parameter
We can now create
Option Label
IF [pDatePartSelected] = ‘day’ THEN IF [Option ID]=1 THEN ‘Yesterday’ ELSEIF [Option ID] =2 THEN ‘Today’ ELSEIF [Option ID] = 3 THEN ‘Tomorrow’ ELSEIF [Option Type] = ‘n periods’ THEN [Option Text] + ‘ ‘ + STR([pSelectPeriods]) + ‘ ‘ + [pDatePartSelected] +’s’ ELSE UPPER(LEFT([pDatePartSelected],1)) + MID([pDatePartSelected],2) + ‘ ‘ + [Option Text] END ELSE IF [Option Type] = ‘date part’ THEN [Option Text] + ‘ ‘ + [pDatePartSelected] ELSEIF [Option Type] = ‘n periods’ THEN [Option Text] + ‘ ‘ + STR([pSelectPeriods]) + ‘ ‘ + [pDatePartSelected] +’s’ ELSE UPPER(LEFT([pDatePartSelected],1)) + MID([pDatePartSelected],2) + ‘ ‘ + [Option Text] END END
This looks a bit convoluted and could possibly be simplified… it grew organically as I was building, and special cases had to be added for the ‘yesterday’/’today’/’tomorrow’ options. I could have added more detail to the the scaffold to help with all this, but ‘que sera’…
Add Column to Columns and Row to Rows. Add Option ID to Detail and Option Label to Text. Manually re-sort the columns so they are reversed
We’re going to need to identify which of the options has been selected. This will be captured in a parameter
pOptionSelected
integer parameter defaulted to 2
and then we need to know if the row of data matches that selected, so create a calculated field
Selected Relative Date Option
[Option ID] = [pOptionSelected]
Manually type in to columns Min(0.0), then change the mark type to shape and add Selected Relative Date Option to the Shape shelf, and amend the shapes to be an open and filled circle. Change the colour to grey.
As before, remove all headers, axis, gridlines, row/column dividers and don’t show the tooltip. Also we’ll need those True and False fields on this viz too – you’ll need to create new calculated fields in this data source, just like you did above, and then add them to the Detail shelf.
Restricting the dates displayed
Now we need to work on getting the line chart to change based on the selections being made that get captured into parameters. But first we need one more parameter
pAnchorDate
date parameter, defaulted to 31 Dec 2022
From playing with Sean’s solution, what date is actually used when filtering can vary based on the date part that is selected. Eg if you choose Month, next 2 months, anchor date of 15 Dec 2022 and choose to display data at the day level, you’ll see all the data for the whole of December rather than just up to 15 Dec (there is no Jan 23 data to display – so you actually only see 1 month’s worth of data). Based on these observations, I created another calculated field to run the filtering against.
CASE [pOptionSelected] WHEN 1 THEN ([Order Date] >= DATEADD([pDatePartSelected],-1,[Compare Date]) AND [Order Date]< [Compare Date])
WHEN 2 THEN ([Order Date]>= [Compare Date] AND [Order Date] < DATEADD([pDatePartSelected], 1, [Compare Date]))
WHEN 3 THEN ([Order Date]>= DATEADD([pDatePartSelected],1,[Compare Date]) AND [Order Date] < DATEADD([pDatePartSelected],2,[Compare Date]))
WHEN 4 THEN ([Order Date]> DATEADD([pDatePartSelected],-1*[pSelectPeriods],[Compare Date]) AND [Order Date]<= [Compare Date])
WHEN 5 THEN ([Order Date] >= [Compare Date] AND [Order Date]< DATEADD([pDatePartSelected],[pSelectPeriods],[Compare Date]))
WHEN 6 THEN ([Order Date] >= [Compare Date] AND [Order Date]<= [pAnchorDate]) ELSE FALSE END
This returns a boolean true or false. Add this field to the Filter shelf of the line chart and set to True.
If you show all the parameters on the view, you can play around manually altering the values to see how the line chart changes
Building the data range display
On a new sheet, add Dates To Show to the Filter shelf and set to True. Then add Order Date as a discrete exact date (blue pill) to Text and change the aggregation to Min. Repeat adding another instance of Order Date and change the aggregation to Max. Format the text accordingly.
Note at this point, I have reset everything back to be based on the original defaults I set in all the parameters.
Finally, there’s one additional sheet to build – something to display the ‘value’ of the option selected in the relative date filter control.
On a new sheet, using the Relative Options datasource, add Selected Relative Date Option to Filter and set to True. Then add Option Label to Text. Add row and column dividers, so it looks like a box.
Putting it all together
On a dashboard, add the Chart sheet (without title). Position the pDateLevel parameter above the chart, and add the input display box we built last, next to it (remove the title). Add a title to the whole dashboard at the top. Then remove the container that contains all the other parameters etc which don’t want to display.
You should have something similar to the below.
Change the title of the pDateLevel parameter and then adjust the input box so it looks to be in line – this can be fiddly, and there’s no guarantee that it’ll be aligned when it gets published to server. You may have to adjust again once published. You can try using padding – ultimately this is a bit of trial and error.
Now add a floating vertical container onto the sheet (click the Floating button at the bottom of the Dashboard panel on the left and drag a vertical object onto the canvas). Resize and position where you think it should be. Add a dark border to the container and set the background to white.
Now click the Tiled button at the bottom of the dashboard panel on the left, and add the Date Part Selector sheet into the container. Remove the title, and also remove any legend that automatically gets added.
Now add a line : add a blank object beneath the date part selector. Edit the outer padding of this object to be 10 px on the left and right and 0 px on the top and bottom. Change the background colour to grey, then edit the height of the object to 1px.
Now add the radio button chart beneath the line (fit entire view)., and again delete any additional legend, parameters added
Now add a Horizontal container beneath the radio button chart. Then click on the context menu of the radio button chart and select parameter > pAnchorDate to display the anchor date on the sheet. Hopefully this should land in the horizontal container, but if it doesn’t, you’ll have to move it. Add the pSelectPeriods parameter this way too.
Change the titles of these parameters as required, then add another line blank object using the technique described above, although delay changing the height for now.
Add the Date range sheet below, and fit to entire view, and now you can edit the height of the line blank object to size 1px.
Next select the container object (it will have a blue border when selected like below) and use the context menu to select Add Show/Hide Button
This will generate a little X show/hide floating object. Use the context menu of this object to Edit Button
I used powerpoint to create the up/down triangle images, which I then saved and referenced via the ‘choose an image’ option
I then added a border to this option and positioned it (through trial and error) where I wanted it to be.
Adding the interactivity
The final step is to now add all the required dashboard action controls.
At least, this is the final step based on how I’ve authored this post. In reality, when I built this originally, I added the objects to the dashboard quite early to test the functionality in the various calculated fields I’d written, so it’s possible you may have already done some of this already 🙂
We need to set which date part has been selected on the control. This is a parameter action
Set Date Part
On select of the Date Part Selector sheet, set the pDatePartSelected parameter, passing through the value of the associated Datepart Lower field.
Add another parameter to capture which radio button has been selected
Select Relative Date Option
On select of the Relative Date Selector sheet, set the pOptionSelected parameter, passing through the value of the associated Option ID field (ensuring aggregation is set to None).
Then we need to add dashboard filter actions to stop the selected options from being highlighted when clicked. Create a filter action
Deselect Dale Part Selecteor
on select of the Date Part Selector view on the dashboard, target the Date Part Selector sheet directly, passing the fields True = False, and show all values when the selection is cleared
Apply a second filter action using the same principals but with the Relative Date radio button selector sheet instead.
And with all that, the dashboard should now be built and functioning as required.
Erica provided a set action based challenge this week requiring us to drill down to 2 levels in a hierarchy. I know there’s been similar challenges to this in the past, but I resisted checking them out, and attempted to build from memory. I succeeded – yay! Let’s crack on…
Note – I used the v2022.1 Superstore data that I already had on my laptop so filtered to December 2022. I also connected to the provided tds file rather than the excel instance, as for some reason, the tds includes Manufacturer which the excel file doesn’t.
Creating the main viz
On a new sheet, add Category to Rows and Sales to Columns and display as a bar chart. Add Order Date to Filter and restrict to the latest month/year in the data you have (in my case Dec 2022).
Create a set based off of Category (right click the Category pill > create set) and select a single value (eg Furniture) to be in the set. A field called Category Set should now exist in the data pane.
What we need to do is create a 2nd level in the hierarchy that shows the sub-categories related to the Category in the set, otherwise just display the Category.
2nd Level
IF [Category Set] THEN [Sub-Category] ELSE [Category] END
Add 2nd Level to Rows. Then right click on the Category Set field in the data pane and select Show Set to display the set values in a control on the sheet.
If you manually change the options in the Category Set control, the values displayed in the 2nd Level column will change.
Change the sort of the 2nd Level pill to sort by Sales descending
Create a set based off the 2nd Level field (right click field > create set), and select a single option that is one of the Furniture sub categories (eg Bookcases). A field called 2nd Level Set will be added to the data pane.
What we now need to do is create a 3rd level in the hierarchy that either shows the Category, the Sub-Category or the Manufacturer depending on the options selected in the sets. This is the only field which will actually be displayed on screen.
Display
IF [Category Set] AND NOT([2nd Level Set]) THEN ‘- ‘ + [Sub-Category] ELSEIF [Category Set] AND [2nd Level Set] THEN ‘ — ‘ + [Manufacturer] ELSE [Category] END
If we have a value selected in the Category Set but no value selected in the 2nd Level Set then display the Sub-Category (with some additional text formatting to give the indented effect).
Else, if we have a value selected in the Category Set and a value selected in the 2nd Level Set, then display the Manufacturer (again with additional text formatting and spacing).
Otherwise, display the Category.
Add Display onto the Rows, and then right click 2nd Level Set and select Show Set. Apply a sort to the Display pill so it’s also sorting by Sales descending.
Test the functionality, by changing the options in the set controls. For representative testing, ensure only 1 option maximum is selected in each set, and if an option in the 2nd Level Set is chosen, ensure it’s a Sub-Category related to the value selected in the Category Set.
Once happy, revert the options back to Furniture and Bookcases.
Format the sheet, and adjust the Row Divider so the slider is at the 2nd mark, and set the Column Divider to none.
Remove all gridlines. Then uncheck Show Header against the Category and 2nd Level pills in Rows. And finally, click on the Display title at the top of the column and Hide field labels for Rows.
To colour the bars, we’re going to use similar logic to that used to determine what to display
Colour Bar
IF [Category Set] AND NOT([2nd Level Set]) THEN ‘Sub Category’ ELSEIF [Category Set] AND [2nd Level Set] THEN ‘Manufacturer’ ELSE ‘Category’ END
Add this to the Colour shelf and adjust the colours. Update the tooltip and widen the rows slightly.
We’ve got the viz, now to add the interactivity so the changes happen ‘on click’ rather than via the set controls we’ve displayed.
Adding the interactivity
Create a dashboard and add the viz. Uncheck the options selected in both sets, so no options are selected – this should collapse the viz to just show the 3 Category values. Delete the container that shows the set controls/colour legend.
Create a new set action as below
Category Selected
on select (single selection only) assign the values to the Category Set, and remove all values when selection is cleared.
Create a 2nd set action
SubCat Selected
on select (single selection only) assign the values to the 2nd Level Set, and remove all values when selection is cleared.
Start clicking around, and BOOM! you should get the desired behaviour (note, once you reach the Manufacturer level, you’ll need to click twice on a manufacturer to get the viz to completely reset.
Bonus – building the ‘header’
I deduced from hovering over Erica’s solution that the ‘header’ was made up of multiple sheets, all aligned in a single row in the dashboard. I did have a play to see if I could come up with a single-sheet option, and while I think there is an alternative, it wouldn’t necessarily display exactly as Erica had shown. So I went down the multiple sheet option too.
Firstly we need a sheet to simply display the text >> Category.
On a new sheet, double click in the space below the Detail shelf on the marks card and type the text ‘dummy’. Then drag the ‘dummy’ pill onto the Label shelf, which will automatically change to now be labelled Text. Click on the Text shelf, and the 3dots to open the Edit Label dialog.
Delete the ‘dummy’ field, and replace it with ˃˃ Category. HOWEVER the ˃ symbol isn’t the > character I typed from keyboard. I found the double >> wasn’t retained this way, which I’m putting down to some HTML/XML related encoding that isn’t being handled. Instead I copied the symbol from this page https://www.compart.com/en/unicode/U+02C3 and pasted it into the text window. I then formatted the font size and style and coloured it the relevant blue.
Next we want to create a sheet showing the Category selected. Click on your viz to add a Category to the set (eg click the Furniture bar).
On a new sheet, add Category Set to the Filter shelf (this by default filter to values IN the set). Then add Category to Text and update the Text field as below, adjusting the font size/colour etc as before.
Duplicate this sheet as we can use this as the basis of the >>Sub-Category display.
On the duplicated sheet, update the text field to contain ˃˃ Sub-Category, where again the > is the character pasted. Format/colour the text
Now click on your viz to add a Sub-Category into the 2nd Level Set (eg click the Chairs bar).
On a new sheet, add 2nd Level Set to the Filter shelf (this by default filter to values IN the set). Then add 2nd Level to Text and update the Text field as below, adjusting the font size/colour etc as before.
Finally, once again, duplicate this sheet. Then on the duplicated sheet replace the text with >> Manufacturer instead
Now you have all the components you need. Add all these sheets onto your dashboard using a horizontal container to keep them all together. Apply the grey background shading if required (you’ll need to format the background colour on each worksheet, as well as the various objects on the dashboard.
This week’s #WOW2022 challenge required us to build an horizon chart. While I’m familiar with them, I haven’t had to build one before.
Luke provided references to a workbook and blog post as part of the challenge. I was also aware that Marc Reid had recently published this blog post on the subject and that in turn referenced this Tableau guest blog post by Yves Fornes. Reading through them both I hoped I’d have the clues I’d need to build the chart.
Note – the blogs referenced all give a good explanation on the process we’re trying to achieve with the horizon chart (divide into equal sized layers and ‘merge’), so I’m not going to spend time repeating that in this post.
Unfortunately, despite a good start, I couldn’t just couldn’t quite manage it – I tried both Marc’s and Yves techniques and also downloaded Joe Mako’s workbook and tried to follow his method, but I just couldn’t get the display to match up, and there were minimal clues in Luke’s published solution.
My fellow #WOW participant Rosaria Guana also pinged me, as she had got further than I had, but wasn’t matching Luke’s solution either. With some assistance from Rosario I managed to build the solution that matched hers and seemed the closest fit to Luke’s. As yet we can’t figure out where the discrepancy may lie… maybe we’ve overthought the issue… who knows.
For now though, I’ll step through the solution as per my published workbook.
Sorting out the dates
The data needs to be segregated into decades, so first I created
Decade
IF [Year]>=1990 AND [Year]<2000 THEN ‘1990s’
ELSEIF [Year]>=2000 AND [Year]<2010 THEN ‘2000s’
ELSEIF [Year]>=2010 AND [Year]<2020 THEN ‘2010s’
ELSEIF [Year]>=2020 THEN ‘2020s’
ELSE NULL END
In the requirements, it mentions using data from the 1980s onwards, but the solution only presents 1990s onwards. I interpreted this as a typo in the requirements, so I added a Data Source Filter (right click data source > Edit Data Source Filters), and added Decade excludes Null to eliminate all the unnecessary rows.
The chart shows 10 years worth of data at the day level across the x-axis. We can’t just use the existing Date field to plot as this will extend for 30+ years. Instead we need to ‘baseline’ the dates to a fixed set of 10 years.
Firstly we need to know what year in the decade it is (ie the 1st, 2nd, 3rd year etc).
Year Index
RIGHT(STR([Year]),1)
This returns values of 0, 1, 2, 3… or 9
I then create the years I’ll baseline my dates against – this can start at any year. I chose 1980.
Baseline Year
INT(STR(198) + [Year Index])
So this will result in years 1980, 1981, 1982… up to 1989.
I can then create a new date which I’ll use to plot against
If we pop some of this data into a table, you can see how the Date field relates to this revised date
The 1st of January for the 1st year in each decade will all be plotted against 1st Jan 1980 etc.
Normalising the Arctic ice (NH) value
The NH values need to be adjusted so that they all sit on a scale of 0-1. This means the date with the lowest NH value across all the years will have a value of 0, and the date with the highest NH value across all the years will have a value of 1, and all the values in between will sit proportionally between. To compute this we need
take the difference between the NH value and the minimum as a proportion of the difference between the max and min values. This gives us the scale we need – the image below is sorted based on the Normalise NH value descending (so the dates aren’t in order)
Creating the Levels & the chart
All of the above I managed with no issue. It was this section that I struggled with 😦 As mentioned I tried all sorts of calculations, and after discussions with Rosario, the calcs I was using based on Yves blog post came closest…
We’re aiming to get bands which are 20% (0.2) in width.
Range 0.8-1
IF ([Normalise NH]) > 1 THEN 0.2 ELSE IF ([Normalise NH])<0.8 THEN NULL ELSE ([Normalise NH]) – 0.8 END END
If the normalised value is over 1 then draw a mark at 0.2, our band size (there won’t be any values, but I did this for completeness). Otherwise if we’re below our lower threshold of 0.8, don’t draw anything, else as we’re within our specific range, draw a mark at the point which is the difference between our normalised value and our lower threshold of 0.8 ( ie a record with a normalised value of 0.91 will plot at 0.11).
On a new sheet add Baseline Date as a continuous exact date to Columns and Decade to Rows. Then add Range 0.8-1 to Rows too. Change to an Area chart.
Now let’s build the next level based on the same principals
Range 0.6-0.8
IF ([Normalise NH]) > 0.8 THEN 0.2 ELSE IF ([Normalise NH])<0.6 THEN NULL ELSE ([Normalise NH]) – 0.6 END END
As you can see this is very similar to the above calculation, but this time, we will have values greater than the upper threshold of 0.8, which will all be plotted at 0.2.
Drag this field on the Range 0.8-1 axis and drop when you see the ‘two column’ symbol appear. This will make the chart a Combined Axis chart and Measure Names and Measure Values will automatically get added to the view. Colour the Measure Names accordingly, and up the transparency on the Colour shelf to 100%.
This looks very close to what we need, but you can see the axis is plotting values at >0.2. This is because the measures are stacked on top of each other. We need to turn this off via the Analysis > Stack Marks > Off menu.
When you do this, it’ll look like some marks have disappeared, but they are just hidden behind the other marks. Use the Measure Namescolour legend on the right to drag and re-order the options listed.
Let’s now add the next level
Range 0.4-0.6
IF ([Normalise NH]) > 0.6 THEN 0.2 ELSE IF ([Normalise NH])<0.4 THEN NULL ELSE ([Normalise NH]) – 0.4 END END
And drag the field into the Measure Values box. For the purposes of display at this point, I have coloured this measure a light grey, when it should be white (but that won’t show up at this point).
Now if we continue with the pattern and create similar calculated field for the next 2 ranges, we end up with something that isn’t right… too much red, and white in places we shouldn’t have white… 😦
This is where I started having discussions with Rosario. I could see by observing Luke’s solution that the dark reds were ‘inverted’ ie looked like humps coming ‘down’ from the top line, rather than humps ‘going up’ from the bottom line, but I was stumped where I needed to go next. Rosario confirmed this was her thinking too, and provided alternative computations for the light and dark red levels.
Range 0.2-0.4
IF [Normalise NH] < 0.2 THEN -0.2 ELSE IF [Normalise NH]<0.4 THEN [Normalise NH] – 0.4 ELSE NULL END END
If we’re below the lower threshold, plot at -0.2 (minus 0.2) instead. If we’re within the lower and higher threshold, plot the difference between the Normalise NH value and 0.4 (which will be a negative number), otherwise plot nothing.
and finally for the last band
Range 0-0.2
IF ([Normalise NH]) < 0.2 THEN [Normalise NH]-0.2 ELSE NULL END
Add this to the view, but you’ll need to change the order, so this measure is sitting above the Range 0.2-0.4 one.
Ok, so the shapes look like they might be correct, although we are missing some dark red in the 1990s compared to Luke’s solution, and as mentioned at the start, we don’t know where the discrepancy comes from.
So how do we make this all be ‘above the line’.
Drag another instance of Measure Values onto Rows so it’s sitting next to the existing one.
The make the chart dual axis BUT DON’T synchronise axis.
Instead, right click on the left axis to edit it, and fix the axis from 0-0.2
Then right click on the right axis to edit, and fix this from -0,2 to 0.
Set the colour of the Range 0.4-0.6 back to white, and you should have
Now you just need to
add tooltips
Edit the date axis and fix from 01/01/1980 to 31/12/1989
hide all 3 axis
remove row gridlines
remove column dividers
hide the null indicator
hide field labels for rows
adjust the font side and alignment of the Decade header
Since authoring this blog, Luke has made his workbook available to download. I have looked at it, and the calculations he uses are much much simpler. However I’m still struggling with the concept and not fully convinced with it all. I have played around with the workbook, and try to step through based on how the horizon chart is described in the various blogs, but I’m not entirely sure I know what the right answer might be in order to know whether what I’m seeing is actually correct or not. Feels like this is one I could do with a face to face discussion over, and ultimately is probably one for another day. ..
It’s a double whammy this week – a #PreppinData & #WOW2022 combo! And I’m going to attempt to blog a solution guide to both! Wish me luck… it may take some time!
The #PreppinData & #WOW2022 crew combined with the requirement to prep some Salesforce data with Tableau Prep as per the challenge here, and to then use the output to visualise in Tableau Desktop as per the challenge here.
Prepping the Data
Two files were provided for the input: Opportunity provided 1 row per opportunity, and Opportunity History providing multiple rows per Opportunity, where each row indicated the stage in the lifecycle the opportunity goes through.
The first requirement was to pivot the Opportunity data, to get 2 rows per Opportunity; 1 indicating the date opened, and the other indicating the date closed (or expected to be closed).
After connecting to the Opportunity date and adding a Clean step to view the contents, add a Pivot step to transform Columns to Rows, adding the CloseDate and CreatedDate fields to the pivot.
Before adding any further steps, rename Pivot1 Names to Stage and rename Pivot1 Values to Date.
Add a Clean Step.
Change the CloseDate value in the Stage field to be renamed to ExpectedCloseDate, and the change the CreatedDate value in the Stage field to be renamed to Opened. To do this, simply double click on the value and type in.
We then need to further update this field, based on whether the Opportunity record is closed or not. The requirement said to refer to the StageName field for this, even though there is an IsClosed field. I chose to stick with the requirements.
Create a calculated field
Stage
IF CONTAINS([StageName], ‘Closed’) AND [Stage]=’ExpectedCloseDate’ THEN [StageName] ELSE [Stage] END
Having applied this logic, your Stage field should now contain 4 values rather than 2.
Then remove all fields except for Date, Stage, Id and StageName, and rename the step Opportunity.
We now need to combine with the OpportunityHistory data. This data contains a row for each stage the Oppotrunity goes through. What we’re looking to do is to supplement this with a Stage = Opened record, and, in the event the opportunity hasn’t been closed, a Stage = ExpectedCloseDate record.
For this we’ll need a Union step.
Add in the OpportunityHistory data, and add a Clean step. Rename this step to History. To help with the union step, rename the CreatedDate field to Date. In the Opportunity step above, rename Id to OppID.
Add a Union step to the Opportunity path. Then drag History and add it to the Union. Add a Clean step and view the data. If the renaming worked ok, you should have 6 fields.
Update the SortOrder field by creating a new calculated field
SortOrder
If [Stage]=’Opened’ THEN 0 ELSEIF [Stage]=’ExpectedCloseDate’ THEN 11 ELSE [SortOrder] END
Update the Stage fields for those which have null records.
Stage
IF ISNULL([Stage]) THEN [StageName] ELSE [Stage] END
All the records with SortOrder = null are actually duplicates now, as we have them captured as part of the pivoting step we did initially. All these records can therefore be excluded (click on the null value in the SortOrder field, right click and Exclude).
Now remove the redundant fields of Table Naems, Stage Name, and you should be left with 4 columns and 876 rows of data, which you can output to csv or similar.
Building the Viz
Now we’ve got the data sorted, we can build the viz. If you haven’t done the PreppinData challenge, you will need to download the Opportunity.csv input file and the provided Output.csv files from the website.
Modelling the data
In Tableau Desktop, connect to the file generated in the above process (or download the output file from the PreppinData site if you haven’t built your own). My file was called 2022_06_08_SalesOpps.csv.
Then add an additional connection to the Opportunity.csv file you used in the Prep challenge (or again download from the PreppinData site).
Add a relationship from Opp ID to Id
Building the Open Opportunities chart
We need to work out what Lorna has used to identify an ‘open’ opportunity. After a bit of trial and error, I discovered it was any opportunity that had an ExpectedCloseDate stage. To identify these, create a new calculated field
This will return the value 1 against all the rows for an Opp ID which have an ExpectedCloseDate Stage, and 0 for those that don’t.
As you can see below, all the rows for the 1st two Opp IDs listed are flagged with 1 as they have at least 1 stage which is ExpectedCloseDate. For the other Opp IDs listed, they are all 0.
Right click this field and drag to the Filter shelf. When you release the mouse, choose the All values option from the dialog displayed, then Next
Then select 1 to filter the sheet just to the the Opportunity records we care about.
Add Opp ID and Name to Rows. Add Date to Columns as a continuous exact date (green pill).
Change the mark type to Circle and add the Stage field to Colour, and adjust accordingly using the relevant colour palette.
To add the grey lozenge marks, add another instance of Date next to itself. This will create a second marks card called Date2. Remove the Stage field from the Colour.
Change the mark type of the Date2 card to line, and adjust the colour to a light grey and the size to be a bit larger.
Make this chart dual axis and synchronise the axis. Move the lozenge marks ‘to the back’ (right click on the top axis and move marks to back.
We need to identify those with a Close Date in the past. For the purposes of this exercise, I hardcoded ‘today’ into a parameter pToday and set it to 8th June 2022. Otherwise in a ‘live’ situation I would refer to the TODAY() function rather than the parameter.
Add this field to the Rows before the Opp ID field. You won’t get the symbol against every row, but don’t worry about that just yet. Format this circle to be right aligned and red font.
Now we need to mange the sorting. Firstly create a new parameter
pSortBy
an integer containing values 1 and 2, defaulted to 2 where the values displayed are aliased as below
To determine how long an opportunity has been open we need
Days Open
DATEDIFF(‘day’, [Created Date], [Close Date])
We then need a field to utilise this parameter and determine the measure we can use to sort
Sort By
CASE [pSortby] WHEN 1 THEN SUM([Days Open]) * -1 ELSE INT(MIN([Close Date])) END
If we’re sorting based on the Longest Open we’ll use the number of days the opportunity has been open. By default the data will ascend from smallest to largest value but we want the opposite, so we multiple by -1.
If we’re sorting based on the Close Date. then we can just use the Close Date field itself, converted into a integer.
Add Sort By to Rows , change it to be discrete (blue) and then move it to be the first pill on the Rows shelf. The data should now be reordered, and you’ll now get a red circle per row.
Add the pSortBy parameter to the sheet and test the sorting. Once happy, hide the Sort By field (right click and uncheck Show Header), then Hide Field Labels for Rows. Remove row & column dividers and make the first column narrow. You should now have your chart.
Building the legend
Lorna’s decided the legend should be circles rather than the ‘out of the box’ squares, so a custom sheet is required.
On a new sheet, add Sort Order discrete dimension (blue pill) and Stage to Rows. Add Stage to Filter and exclude Closed Lost and Closed Won. We’re going to need to organise these Stages into 2 rows and 3 columns, so we need some fields to help.
Row Index
IF [Sort Order] ❤ THEN 1 ELSE 2 END
Column Index
IF [Sort Order] = 0 OR [Sort Order] = 4 THEN 1 ELSEIF [Sort Order]=2 OR [Sort Order]= 8 THEN 2 ELSE 3 END
Note I’m very much ‘hardcoding’ this as this is acceptable for this view I believe.
Add Row Index as discrete dimension to Rows and Column Index as discrete dimension to Columns, and move Stage to Label.
Type in FLOAT(MIN(0)) into Columns to create a fake axis. Change mark type to circle, and add Stage to Colour. Increase the width of each row if all your text isn’t showing.
Edit the axis so it is fixed to start at -0.1 and end at 0.5 – this will shift the circles to the left.
Uncheck Show Header against the Row Index, Column Index and the MIN(0) axis. Then remove all gridlines and row/column dividers. Turn off tooltips.
The Summary View
Now we need to work on the summary values at the top of the screen. We’re focussing on ‘open’ opportunities in the current month, which is June 2022 (based on the pToday parameter). We need Has Expected Close Date Stage = 1 on Filter, and to identify the current month we use
Now let’s have closer look at the rows of data we’re got so far.
Add Opp ID, Sort Order (discrete dimension) and Stage to Rows and Amount to Text.
What we’re looking for is to aggregate the Amount by Stage, but if we remove the Opp ID and Sort Order fields, we don’t get the right values
This is because we’ve got multiple rows per Opp ID, so the value is counting in multiple Stages. We want to just limit the rows to the latest Stage before the ExpectedCloseDate stage.
First lets work out the maximum Sort Order value for each Opp ID that isn’t the Expected Close Date row (which has a Sort Order = 11).
Max Stage Sort Order
{FIXED [Opp ID]: MAX(IF [Sort Order]<=10 THEN [Sort Order] END)}
Add this to the tabular view as a discrete dimesion. Hopefully you can see from the image below that we’re getting the value we want.
Now let’s identify the matching row
Stage To Keep
[Sort Order] = [Max Stage Sort Order]
Add this to the Filter shelf and set to True.
Now if we remove Sort Order, Max Stage Sort Order and Stage from Rows, we get the correct summarised values.
Move Stage to Columns and add Stage to Text and Colour. Set to Fit Entire View. Align Text centred and remove all row/column dividers. Uncheck Show Header against the Stage field on Rows. Use the Colour Legend to manually sort the values into the correct order.
Update the sheet title.
Past Close Date
This is a bit simpler… add Past Close Date? to Filter and select ●. Add Stage to Filter and select ExpectedCloseDate. Add Amount to Text and the auto generated field Opportunity.csv (Count) to Text.
Format the text and add a title, and you’re all done You just need to add everything to the dashboard now.
Kyle set a challenge this week inspired by a chart Ann Jackson demoed at #data22 as part of her Advanced Speed Tips session with Lorna Brown. This was both an in person presentation and available virtually (hence the link to the video above). I couldn’t attend the in person session due to a clash, but I had already watched the session prior to this challenge being set, so I attempted to build from memory without referring back. To find out what to do, you can either watch Ann herself demonstrate via the link above, or read on 🙂
Building the WAR chart
Build a basic bar chart by adding Name to Rows and WAR to Columns
Whilst we could sort the data at this point, we’re going to leave for now, as we want the ‘ranking’ field to define the sort.
Now rather than use the RANK table calculation option, the essence of this being included in a tipping session, is to utilise the INDEX table calculation instead. By using INDEX, we don’t need to create as many fields as if we used RANK. We can reuse the INDEX across all the charts.
So, let’s create that field
Index
INDEX()
Format this to be a number with 0dp and prefixed by #
The INDEX() table calculation simply lists your rows or columns of data from 1 to however many entries there are. The Index can span the whole width/length of the table or can restart at intervals depending how you choose to partition/structure your table. You can also control how the data will be indexed based on how you want it sorted.
Add Index to Rows, then change it to be discrete (blue), and position in front of Name. Edit the table calculation setting so that it is computing using Name and is sorted by WAR descending.
We need to capture the name of a player, so we’ll create a parameter for this
pSelectedPlayer
String parameter defaulted to Ken Giffey Jr.
Show this parameter on the view.
We also need a field to identify whether the row matches the parameter
Is Selected Player?
[pSelectedPlayer] = [Name]
Add this to the Colour shelf and adjust accordingly.
Then re-edit the table calculation so it is computing by Is Selected Player as well.
Note – to prevent having to change the table calculation, the alternative is to make the Is Selected Player field on the Colour shelf an Attribute (just choose this setting from the context menu when you click on the pill).
Additionally add Is Selected Player to Rows before Index., the manually sort that field so True is listed first. This will move the relevant record to the top of the scree, but the rank number will be preserved.
Hide the Is Selected Player field (uncheck Show Header), and Hide Field Labels for Rows.
Adjust row dividers, so the divider is at level 1, and the header is dotted line, while the pane is solid.
Remove column gridlines, but add a column axis ruler.
Show mark labels, set them to be left aligned and the colour to match mark colour. You may need to widen the rows to see the labels display.
Add a border to the bars via the Colour shelf.
Format the Index and Name fields displayed – I used Tableau Regular font size 12, and right aligned the Index field.
Update the tooltip and add a header.
The final step, which we’ll do now, is to add a couple of fields to stop the rows from being highlighted when selected on the dashboard.
Create a field True = TRUE and False = FALSE, and add these fields to the Detail shelf.
Building the Batting Average chart
Create a basic bar chart with Name on Rows and BA on Columns. Add Is Selected Player to Colour and apply border.
Format the BA field using custom formatting of .000;-.000 then show mark labels, and left align and format as you did above.
Add Index to Rows, make discrete and move to be in front on Name. Adjust the table calculation to compute by Name and Is Selected Player and this time adjust the sort to use BA descending.
We now need to filter the data to only show the 5 rows above & below the selected index. First we need to identify them.
Selected Player Index
WINDOW_MAX(IF ATTR([Is Selected Player]) THEN INDEX() END)
The inner IF statement returns the index associated to the selected player. The WINDOW_MAX function then ‘spreads’ this value across all the rows in the data. So we can then identify the records we need
Records to Keep
[Index] >= [Selected Player Index]-5 AND [Index] <= [Selected Player Index]+ 5
Add this to the Filter shelf and initially select all the values.
Now edit the table calculation settings of this field. It will now contain nested calculations – one related to Index and one related to Selected Player Index. Ensure both are computing by Name and Is Selected Player and both are sorting by BA descending.
Then edit the filter and just select True.
Now apply the tooltip, set the heading and format the headers, remove gridlines, row dividers etc.
Then follow the same steps to create similar charts of the OPS and HR measures, remembering to apply the sorts on the table calculations to the relevant field.
Adding the interaction
Once you have all four sheets built, add to a dashboard. Then create a parameter action to select the player from the WAR sheet.
Select Player
On select of the WAR sheet, set the pSelectedPlayer parameter passing in the Name field.
Then create a filter action to prevent the selection on the WAR sheet from remaining highlighted.
Unhighlight WAR
On selection of the WAR sheet on the dashboard, target the WAR sheet directly and use Selected fields, setting True = False.
Fingers crossed and you should now have a working dashboard. My published version is here.
It only seems like yesterday I was writing a solution guide, and I’m back at it again. This week Sean asked us to recreate this challenge to build a small multiple / trellis chart using table calculations only.
A note on the data
After downloading and connecting to the provided data source, I found the dates weren’t coming through as intended – they’d been transposed from dd/mm/yyyy to a mm/dd/yyyy so consequently the only dates I was getting were for the first 12 days in January for every year. Rather than trying to solve this at source, I just created a new field which transposed the Date field back so it behaved as I expected
You may not need to do this if the data pulls in correctly.
Filters
There are two filters that should be applied, which can either be added as data source filters (right click data source > Edit data source filters) or can be applied to the Filter shelf on any sheets created. Ultimately, this challenge only requires 1 sheet, but when building and verifying logic, I tend to have additional ‘check sheets’. I therefore added the filters below to the Filter shelf of the first sheet I started working with, but set them to apply to all worksheets using the data source (right click pill once it’s on the filter shelf -> Apply to Worksheets -> All using this data source).
Gender : All
Date Corrected : starting date = 01 Jan 2012
Setting up the data
As is good practice when working with table calculations, I start by building out the calculations I need and validating them in a tabular format before I build any vizzes. So let’s do that.
All the countries are displayed in capital letters, so we need
Country UPPER
UPPER([Country Name])
Add this to Rows
Additionally, for the purpose of validation and performance only, add this field to the Filter shelf too and just filter to Australia and Austria.
If you haven’t already added them as data source filters, apply the filters mentioned in the section above to this sheet too and set to apply to all worksheets using the data source.
Add Date Corrected to Rows as a discrete (blue pill) exact date. Format the date so it displays in month year format.
Add Unemployment Rate to Text. Format this number to 1 decimal place and add a % as a suffix.
Now for the table calcs
Median
WINDOW_MEDIAN(SUM([Unemployment Rate]))
Format this to display as a % using the same option as above. Add this to the table and set to compute using Date Corrected
You should find that your median value only differs by country.
Now we work out
Variance
SUM([Unemployment Rate]) – [Median]
Format this to display as a % and add to the table, setting the table calc to compute by Date Corrected again. This is the measure that will be used to plot the trend line against.
We also need to display the range of Unemployment rates for each country – ie we need to work out the minimum and maximum values.
Max Unemployment Rate
WINDOW_MAX(MAX([Unemployment Rate]))
Min Unemployment Rate
WINDOW_MIN(MIN([Unemployment Rate]))
Format both of these to display as 5 with 1dp, and add to the table, verifying the calculations are computing by Date Corrected once again. Verify you get the same values for all the rows associated to a single country.
Now we know the calculations are as expected, we can start to build out the viz.
Building the core chart
To start with we’ll just focus on getting the line chart with the associated text displayed for the two countries Australia & Austria. So on a new sheet add Country (UPPER) to Filter and filter to these selections. The other filters should automatically add.
Add Country UPPER and Date Corrected (green continuous exact date) to Columns and Variance to Rows. Set Variance to Compute By Date Corrected.
Add Unemployment Rate to the Tooltip and adjust the text to match.
To add the country title and other displayed text, we’re going to use a ‘fake axis’ and plot a mark at a central date. On hovering over the solution, October 2016 seems to be the appropriate date selected. So we need
Title Position to Plot
IF [Date Corrected] = #2016-10-01# THEN 1 END
Add this to Rows in front of the existing pill. Change the mark type of this measure only to a circle and re-Size to make it as small as possible and adjust the Colour Opacity to 0%. This will make the mark ‘disappear’.
Add Country UPPER, Median, Max Unemployment Rate and Min Unemployment Rate to the Label shelf of this marks card. Ensure all the table calculation fields are set to compute by Date Corrected. Adjust the text as required, and align centre. Ensure the Tooltip is blank for this marks card.
Change the colour of the variance line to grey, then remove all gridlines, row dividers and axis. Set the Column Dividers to be a thick white line (this will help provide a separator between the small multiples later).
Creating the trellis
There are multiple blog posts about creating trellis charts. My go to post has always been this one by Chris Love. It’s a more complex solution that dynamically flexes the number of rows and columns based on the number of members in the dimension you’re visualising. There have also been other Workout Wednesday challenges involving trellis charts, which I’ve blogged about too (see here).
Ultimately we’re aiming to determine a ‘grid position’ for each member of our dimension. In this case the dimension is Country UPPER and its a static list of 36 values, which we can display in a 6 x 6 grid. So Australia needs to be in row 1 column 1, Austria in row 1 column 2….. Costa Rica in row 2 column 1… USA in row 6 column.
As our members are static, the calculations we can use for this can be a bit simpler than those in Chris’ blog.
Firstly, let’s get our data in a tabular layout so we can ‘see’ the values as we go.
Duplicate the data sheet we built up, then move Measure Name and Date Corrected from Rows/Columns to the Detail shelf. Remove the Country UPPER field from the Filter shelf. You should have something like below, showing 1 row per country
Double click into the Rows shelf and type in INDEX(), then change the resulting pill to discrete (blue). You will see that index numbers every row. It’s a table calculation and although working as expected, let’s explicitly set it to compute using County UPPER.
Let’s now create our grid position values.
Cols
FLOAT(INT((INDEX()-1)%6))
This takes the Index value and subtracts 1, and returns the remainder when divided by 6 (%6=modulus of 6 – ie 6%6=0, 7%6=1). 6 is the number of columns we want.
Rows
FLOAT(INT((INDEX()-1)/6))
This takes the Index value and subtracts 1, and returns the integer part of the value when divided by 6. Again we’re using 6 as this is the number of rows we want to display.
Add these to the table, set to be discrete (blue) and compute using Country UPPER.
You can see that the first 6 countries are all in the same row (row 0) but different columns (0-5).
Now that’s understood, we can create the small multiples on the viz.
Duplicate the sheet we created further above which displays the trend graph for Australia & Austria. As we’re now going to make the changes to create the charts for every country, if things go a bit screwy, you can always get back to this one to try again :-).
Add Cols to Columns. Set to discrete and compute using Country UPPER. Add Rows to Rows and do the same thing. Move Country UPPER from Columns to the Detail shelf on the All marks card. Then remove Country UPPER from the Filter shelf.
Hopefully everything worked as expected and you have
Final step is to uncheck Show Header against the Cols and Rows pills so they don’t display and you can add to a dashboard.
This week the whole #WOW2022 crew and myself were lucky enough to be able to attend #data22 – the Tableau Conference in Las Vegas. In the 10 years I’ve been involved in the Tableau Community, this was my very first US conference, and my first opportunity to meet some of the people I engage with on a weekly basis, in person. Meeting all those who have been involved in the WOW challenges over the years and my fellow regular participant, Rosario Gauna, was a big highlight for me.
Erica led the live #WOW2022 session at the conference on Thursday morning (not sure the organisers fully understood the title), with this challenge.
Erica walked through the challenge end-to-end in the session, but I attempted to build out my solution, just as I would at home, so let’s crack on.
Modelling the data
The viz compares actual sales vs target, and an additional data set was provided to store the target data. As a consequence this needs to be combined/joined/related with the actual sales data.
For this you need to download the two excel data sources provided in the challenge – SuperStore Sales and Superstore Category Monthly Sales Targets.
Connect to the Superstore Sales file and drag the Orders sheet into the canvas. The Add a new connection to theSuperstore Category Monthly Sales Targets file and drag in the Sales Targets sheet to the right of the Orders object in the canvas.
This will try to create a relationship between the two objects, but can’t as it needs to be defined.
In the section at the bottom, relate the Category field from the Orders object to the Category field in the Sales Target object.
This creates a valid link between the two objects, but it’s not enough. We also need to relate on a date field. In the Sales Targets object there is a field Month which stores the date on the 1st of a month. So to relate the Orders data we need to
Click + to Add more fields
From the Select a field dropdown on the Orders side, select Create a Relationship calculation
In the calculation window type in DATE(DATETRUNC(‘month’, [Order Date])). This returns the 1st of the month related to each Order Date into a Date rather than Datetime datatype.
Select Month from the Sales Targets dropdown.
Building the bar in bar chart
You can build this type of chart using a dual axis chart, where both axis are set to use the bar mark type, but the sizes of the bars are different. However, if you go down this route you’ll struggle to get the labels right (as the labelling is the trickiest part of this challenge).
Instead for this challenge you need to build a combined axis chart, which is possible since both measures are represented by the same mark type, a bar.
First up, add Order Date to the Filter shelf and select to filter by the Year 2021.
Then add Order Date to Rows and set it to the discrete (blue pill) ‘month’ level (the ‘May’ option rather than the ‘May 2015’ option on the context menu). Add Sales to Columns and change the mark type to bar.
Then drag Sum Targets from the left hand pane onto the Sales axis, releasing the mouse when you see the ‘double column’ green icon appear.
This will result in a combined axis chart being created, and the fields Measure Names and Measure Values automatically added to the viz.
Move Measure Names from the Columns and place on Size. Additionally add another copy of Measure Names onto Colour and adjust accordingly.
On the Analysis menu, select Stack Marks -> Off so the bars both start from 0, rather than being placed on top of each other.
We now need to adjust the bar sizing. Edit the sizes via the Size legend on the right hand side, so the range between the sizes is less than that set.
We now need to work on colouring the Sales bars based on whether they met target or not. For this we first need to work out whether the Target is bigger or not.
Met Target?
SUM([Sales]) > SUM([Sum Targets])
This returns a boolean true/false result. We want this on the colour shelf in addition to the Measure Names field that is already there. If we just drag it onto colour, it will replace Measure Names. Instead, drag Met Target? onto the Detail shelf. Then click on the 3 dots immediately to the left of the Met Target? pill in the marks card, and select the Colour icon.
This has the effect of adding this as an additional colour field, so four options rather than two are now presented in the colour legend. Adjust the colours once again.
We’ve now got the core bar-in-bar chart. We can just add some final formatting to this section.
Format the month axis to set the dates to be abbreviated, and then rotate the labels.
Click the Order Date label at the top of the chart and Hide field labels for columns.
Edit the Value axis and rename the title to Sales ($).
Amend the number format of both the Sales and Sum Targets fields to be a number with 0dp and $ prefix. Add both fields to the Tooltip and adjust so that both values display when you hover over either the Sales bar or the Targets bar.
Labelling the bars
Labelling the bars to match Erica’s display is the trickiest part of this challenge. Bottom line – it shouldn’t be so tricksy but that’s just the way it is until Tableau see fit to fix it in the product.
Anyway, on examining Erica’s published solution before I started, I could see, by hovering my mouse over the viz, that there was a small mark highlighted above the bars that missed target. This provided a clue that there was a dual axis involved (hence the need for a combined axis to display the bars), with a mark plotted at some distance above the existing data…but where…?
Before working that out, I need to build a couple of fields
Difference
SUM([Sum Targets])- SUM([Sales])
which returns the difference between the Sales and Sum Targets for each month. But since I only want the values when the target hasn’t been met, I need
Missed Target Diff
IF NOT([Met Target?]) THEN [Difference] END
which is formatted to $ with 0dp.
I also needed some text to only display when the target was missed
Label Text Missed Target
IF NOT([Met Target?]) THEN ‘Target missed by’ END.
Now to figure out what measure to plot….
In Erica’s solution, she worked out an ‘uplift’ of the Sum Targets value to plot, but only when the target was missed. She did this using quite a complex looking nested LOD calculation (check out her solution for this).
After much deliberation, discussions, trial and error, I finally came up with a alternative that isn’t perfect, but is ‘good enough’ in my book.
Firstly, I needed another measure to plot
Target to plot
IF NOT([Met Target?]) THEN SUM([Sum Targets]) END
This plots the Targets value but only against the months where the target wasn’t month.
I then added this to the Columns shelf and I changed the mark type to circle on the Target to Plot marks card only.
I then made the chart dual axis and synchronised the axis.
Then, remove Measure Names from the Colour and Size shelf on the Target to Plot marks card, and also remove the Met Target? field from the Colour shelf too.
Adjust the Size of this mark to the smallest possible, and change the Opacity (via the Colour shelf) to 0%, so the circle mark becomes invisible except on hover.
Add Missed Target Diff to the Text shelf of the Target To Plot marks card. Edit the text in the label as below
I added a carriage return, formatted the text to 11pts and orangey/red and added 5 spaces to the front of the 2nd line.
I then changed the label alignment so the text was rotated and it was positioned top centre.
Next I added Label TextMissed Target to the Label shelf on the Measure Values marks card.
I edited the text as below, left aligning and again adding 5 spaces in front
Then I adjusted the alignment to be top left
Adding Category to the Filter shelf and testing with the different filters, suggested this technique seemed to work (at least on Desktop).
Last step is to remove the secondary axis, remove the row & column dividers, and hide the ‘nulls indicator’. Then add to a dashboard.
This challenge certainly raised questions over these formatting specifics. As I mentioned above, it shouldn’t be that hard to add a label that looks like this – left aligned and positioned above the bar. However the product doesn’t let you achieve this easily as yet, which is disappointing and certainly confusing to new users of the product – it seems such a straightforward requirement after all.
If I was doing this for my own work, I’d have kept with whatever options a single label allowed (where the text was all right aligned), or placed on a single line. The impact on performance of a complicated calculation along with the maintainability of a dashboard using it, are important considerations when deciding what’s ‘good enough’, and are arguments that should be made if a client/user insists. After all, what real benefit does this particular format provide over
This week’s #WOW2022 challenge was focused on dashboard layout, specifically the use of containers and padding to give your charts room ‘to breathe’ and be aligned beautifully.
This has come at a great time for me, as now I’m spending more time in my role developing business dashboards, I’m trying to find a ‘go to’ style that works for me. I know things will often need to be adapted on a client by client basis, but having a consistent reference point for anything I develop personally, or just want to conceptualize will make my life easier (less decisions to be made). As a result I’m currently spending a lot of time figuring out what padding/background colours etc I want to use for my dashboards.
A note on the data
You may notice that my solution looks a bit different from Luke’s in terms of content. The requirements state to use Superstore v2021.4, but the excel file that ships with Tableau doesn’t actually contain a Manufacturer field which Luke uses. I thought I’d try to use the file linked in the requirements, but (at the time of writing), that actually linked to a 2019.4 version, so the numbers didn’t match up. I therefore chose to use the Superstore v2021.4 excel file I already had on my laptop, and built the table using the Product Name field instead. I think the Superstore.tds file that ships may contain Manufacturer, but I couldn’t see to find that either at the time.
I also chose to use a count distinct function when counting the Order IDs rather than a count, as this seemed more appropriate to me, which is why that KPI differs.
As the focus on the challenge was on the layout, I didn’t think it was necessary to get these points resolved before building my solution.
Building the KPIs
I managed to build the visuals required for the solution using 5 sheets – 1 sheet per KPI ‘card’ and 1 for the table. After I completed my solution, the requirements were updated and suggested 9 sheets were required, essentially 2 per KPI card – the measure and the bar chart. Personally I think having 1 sheet for each KPI is ‘cleaner’ so I left it as is.
In order to build each KPI in a single sheet, I need additional calculations to provide the ‘total’ measure value.
Total Sales
{FIXED: SUM([Sales])}
Count of Orders
COUNTD([Order ID])
Total Orders
{FIXED: [Count of Orders]}
Profit Ratio
SUM([Profit]) / SUM([Sales])
formatted to % with 1 dp
Total Profit Ratio
{FIXED: [Profit Ratio]}
formatted to % with 1 dp
Total Profit
{FIXED : SUM([Profit])}
Then create a bar chart by adding Order Date to Columns and setting to the Month (May) date part (blue discrete pill), and adding Sales to Rows. Add Total Sales to the Detail shelf. Change the mark type to Bar.
Change the Colour to #afaaf3 and reduce the Size. Edit the title of the chart to reference the Total Sales field and format as below with the SALES in 9pt and Total Sales in 15pt.
Modify the Tooltip then remove all gridlines/axis rulers etc, and hide the axis and the month headers.
Rename this sheet as Sales, then duplicate, and create one for Orders by replacing Sales with Count of Orders and Total Sales with Total Orders. You should be able to drop the replacement pill directly on top of the pill being replaced, and everything will update. Update the sheet title to change the word SALES to ORDERS. Rename this sheet Orders, then duplicate again and repeat the process to create the Profit Ratio and Profit sheets.
Building the table
On a new sheet add Product Name (or Manufacturer if you have the right data set) to Rows and add Sales, Profit, Count of Orders and Profit Ratio into the table so you end up with Measure Names on Columns and Measure Values on Text.
Remove all row banding, but add row dividers across each row. Widen each row and the header. Sort by Sales descending.
Change the mark type to Square then add another instance of Measure Values to the Colour shelf. Right click on the Measure Values pill and select Use separate legends.
This will display 4 colour legends, which you need to amend as follows :
Sales Legend
Edit the colour and select Purple sequential. The click on the coloured square at the right end of the range, and change this colour to #675CF3.
Then select the Stepped Colour checkbox and change the value to 8.
Profit Legend
Edit the colour and choose a Diverging colour palette. It doesn’t matter which one. Click on the coloured squares at each end and change them both to white. Set the number of steps to 2.
Count of Orders Legend
Do exactly as described above for the Profit Legend.
Profit Ratio Legend.
Edit the colours and choose a diverging colour palette. Change the colour on the left to #9A1500. Change the colour on the right to #777777. Set the number of steps to 8.
Building the dashboard
Now we have the charts, we need to start building the dashboard, and I’ll see if I can step through my approach to doing it, and if I’ll end up with the right result….
Firstly create a dashboard and set the size to 1100 by 850.
Now a habit I’ve got into when I want to be really precise about the formatting I’m applying (ie I’m building a business dashboard), is I start by adding a floating container to the dashboard, which is set to the exact dimensions of the dashboard. This was a tip I picked up from Curtis Harris here. The main reason for doing this, is that I feel I have more control as I don’t then use any Tiled container type that automatically gets added. If any do appear, I find the objects I want out of them and move them elsewhere, then delete the whole tiled section. In this instance I’m going to start by adding a Vertical floating container, and position it at 0,0 and 1100 wide and 850 high. Use the fields on the Layout pane to set these.
I also get into the habit of renaming the containers, to help me keep track of what is where and whether the objects are in the right place.
The next thing I always do when working with containers, is add a blank object. As we build out, this object will eventually be removed, but it’s a recommended step to help position other objects you add into the containers.
So on the Dashboard tab, change the selection to Tiled and then drag a blank object into the main canvas.
This ‘blank’ is now in the container and the ’tiled’ option means its anchored to that container, which means if you move the container, the objects within will move with it (unlike if the object was floating).
In the item hierarchy, you can see the blank nested in the Base container.
In the item hierarchy, click the Base container, so it is selected on the dashboard (it will be surrounded by a blue frame). Adjust the settings so the background is pale grey, the outer padding is 20px all round, and inner padding is 30px all round.
Add a Horizontal container above the blank object. This is going to be a header section for the title and the export options. Name the container Header in the item hierarchy. Add a blank object into the header container.
Add a Text object into the Header container to the left of the blank object, and enter the text for the title and strapline. I used Tableau Book 16pt bold for the main title and 9pt for the subtitle (there was no instruction for this). Set the outer padding of this text box to 0 all round.
Download the image files Luke provided.
Add a Download object to the right of the blank in the Header container. Edit the button and set to export to an image, use an image button style and select the relevant image.
The image will probably look incredibly large, but don’t worry. First, set the outer padding of this object to be 10 all round.
Then select the Header container and edit the height to be 55px (use the down arrow on the selected object to open the context menu and select Edit Height).
Add further download objects to the right of the existing one (making sure you’re in the Header container) – one to export to PDF and one to PowerPoint. Set the outer padding of the PDF one to 10 all round, and for the PowerPoint one, set the outer padding to 10 for left, top & bottom, but 0 for the right.
Now add another Horizontal container below the Header container and above the blank. Rename this to Main. Set the outer padding of the Main container so that it has 25px at the top. This padding (25) + height of header container (55) + inner padding of Base container (30) gives us the requirement that the height of the grey header to the components needs to be 110px. Add a blank object into the Main container.
You can now remove the blank object that is at the bottom of the Base container – the one underneath the Main container.
NoteIt’s worth saying at this point, that when I built my solution most of this was all bit trial and error / tweaking this & that to meet all the specific layout requirements Luke states. As I’m rebuilding my original solution as I write, I know what order I want to add objects to the dashboard and what all the settings need to be as I add each object.
The Main container is horizontal as it it will consist of 2 columns; 1 with the KPIs and 1 with the table. However both columns need to contain a Vertical container each. For the KPIs columns, this is to stack each KPI card on top of each other. For the table, this is because we have to add the purple bar on top of the table.
So, add a Vertical container into the Main container, to the right of the blank. Rename this container Table Column. Then add a blank into this container, and underneath that add the Table sheet.
If you look in the item hierarchy, you should see that a Tiled section has been automatically added to the dashboard – boo!! This contains all the colour legends associated to the table, and you can just make out where they are (top right) as they’re behind the floating Base container.
In fact, the Tiled container added actually contains lots of other containers.
We don’t need any of these legends to display or any of the containers that have been added, so remove (right click on the first Tiled container and Remove from Dashboard). You’ll get a warning message but just hit Delete to continue. The section will be removed and your item hierarchy looks nice and clean again 🙂
Now that’s sorted, we can focus back on the Table Column container and the objects within. Select the Blank object in the container. Change the background colour to #675cf3, set the outer padding to 0 all round, then edit the height to be 10 px. Rename the blank object in the item hierarchy to ‘purple bar’.
Now select the table sheet itself. Hide the sheet title. Set the outer padding to 0 all round and set the inner padding to 10 all round. Set the background colour to white. Set the sheet to Fit Width. Don’t worry at this point about the table looking cramped.
Now add another Vertical container into the Main container, this time to the left of the blank. Rename this container KPI Column and add a blank into it. You can now remove the blank that is sitting between the KPI Column container and the Table Column container. This will cause the table to expand.
Edit the width of the KPI Column container to be 300 px.
Now we’re ready to deal with the KPIs.
Each KPI ‘card’ consists of a purple vertical line and the KPI sheet itself. This means we need a horizontal container per card to manage this. WHAT! More containers……. Fun isn’t it 🙂
So add a Horizontal container into the KPI Column container, above the blank object. Name this container Sales KPI. Add a blank object into this container. Then add the Sales sheet into the Sales KPI container to the right of the blank. Hopefully you have something like below…
Select the blank object in the Sales KPI container and set the background colour to #675cf3, and the outer padding to 0 all round and edit the width to be 10 px. Rename this object to ‘purple bar’.
Then select the Sales sheet. Set the background to white, the outer padding to 0 all round and the inner padding to 10 all round.
Now select the Sales KPI container, and set the bottom and right outer padding to be 5px. The top and left should be 0.
Add another horizontal container to the KPI Column container directly below the Sales KPI container. Name this container Orders KPI. Set the outer padding on this container to be left 0, right, top & bottom all 5.
Add a blank object into the Orders KPI container, then add the Orders sheet to the right of this. As before, select the blank object and set the background colour to #675cf3, and the outer padding to 0 all round and edit the width to be 10 px. Rename this object to ‘purple bar’.
Now select the Orders sheet. Set the background to white, the outer padding to 0 all round and the inner padding to 10 all round.
Repeat this process to create the Profit Ratio and Profit sections. When you add the Profit KPI container, set the outer padding for the top and right to 5, and the bottom and left to 0.
Remove the blank object that should now be at the bottom of the KPI Column container. The click on the KPI Column container in the item hierarchy to select it, and use the context menu from the drop down at the top right of the object and Distribute Contents Evenly.
The final tweak to make is to add some left padding to the Table Column container. Select the container and set the left padding to 5 and the rest to 0.
And that should hopefully be it, and meet all the requirements. My full item hierarchy is visible below.
Granted there are more containers than Luke suggests, but hopefully you can see how it’s organised and much more maintainable.
Containers can be tricky beasts, but if you work methodically with them, they become easier to use. In summary, the steps I try to follow are
Start with a floating container sized accordingly, and add ’tiled’ (ie not floating) objects into that
Always add a blank object into any container you add. Add required objects, then remove the blank if no longer required.
Rename containers as you go.
Deal with any Tiled containers as they appear – if you need a legend/filter that gets added in it, select that object via the item hierarchy and move it to where you want it to be, then delete the complete tiled container.
It’s Tableau Conference next week, and I’m actually going to be attending in person for the very first time (I’m super super excited!). I’m not sure when next week’s challenge will actually land or when I’ll actually get a chance to complete, let alone blog. I might have to end up playing catch, so I apologise in advance for those who can’t attend and may be waiting for my guide to help them out. It will get published… I just don’t know when!
Donna + DataViz 