Sean provided this week’s #WOW2024 challenge, to produce a dual axis chart which he labelled a ‘candle line’ chart. The line displays the 2024 sales per month, while the candles represent the absolute difference from the previous year.
Setting up the calculations
Rather than ‘hardcode’ to 2024, I decided to use a calculation to get the latest year in the data set
Latest Year
{MAX(YEAR([Order Date]))}
which I formatted to a number with 0dp which did not use thousand separators
and then created
Previous Year
[Latest Year] – 1
I moved both of these up into the ‘dimensions’ section of the data pane (above the line).
To get the latest year sales I created
Latest Year Sales
IF YEAR([Order Date]) = [Latest Year] THEN [Sales] END
and then created
Prev Year Sales
IF YEAR([Order Date]) = [Previous Year] THEN [Sales] END
Both these fields I formatted to $ with 0 dp
We need to know the difference between these values, so I created
Sales Diff
SUM([Latest Year Sales]) – SUM([Prev Year Sales])
which I formatted to $ with 0 dp.
and we also need the percentage difference
% Diff
[Sales Diff] / SUM([Prev Year Sales])
which was formatted to a % with 0 dp.
We need to know if the Sales Diff is positive or negative, so create
Diff is +ve
[Sales Diff]>=0
Finally, when we hover on the tooltip we can see the values are coloured based on whether the difference is +ve or not, so we need some additional fields for the tooltip
Tooltip Sales Diff +ve
IF [Diff is +ve] THEN [Sales Diff] END
and
Tooltip Sales Diff -ve
IF NOT([Diff is +ve]) THEN [Sales Diff] END
both these fields I custom formatted to +”$”#,##0;-“$”#,##0 (essentially this is $ with 0 dp, but positive values are prefixed with an explicit + sign).
And then we also need
Tooltip Sales Diff % +ve
IF [Diff is +ve] THEN ‘(‘ + STR(ROUND([% Diff]* 100,0)) + ‘%)’ END
and
Tooltip Sales Diff % -ve
IF NOT(Diff is +ve]) THEN ‘(‘ + STR(ROUND([% Diff]* 100,0)) + ‘%)’ END
Now we have all the fields, we can build the viz.
Building the viz
On a new sheet, add Order Date at the discrete month level (blue pill) to Columns and add Latest Year Sales to Rows.
Then add another instance of Latest Year Sales to Rows. On the second marks cards, change the mark type to Gantt Bar and add Sales Diff to Size. Reduce the Size to make the bars that display narrower.
Add Diff is +ve to the Colour shelf, and adjust accordingly. Change the colour of the line chart on the other marks card. Make the chart dual axis and synchronise the axis.
On the All marks card, add Latest Year and the four Tooltip xxx fields we created to the tooltip shelf, then update the tooltip to reference all the relevant fields, and colour them accordingly
Add Region, Category and Segment to the Filter shelf, selecting all values.
Then finally, tidy up the sheet by removing all row/column dividers, the right hand axis (uncheck show header), and the Order Date label (right click and hide field labels for columns). Rename the left hand axis Sales.
Add to a dashboard and position as appropriate adding a title and updating the filters to be single value drop downs.
That ultimately is the core of the challenge, but Sean did suggest to use the new Google font Poppins. I’m on Windows and that font isn’t visible by default/installed, so after publishing, I then edited on Tableau Public and changed the fonts throughout via the Format > Workbook menu option and setting All fonts to Poppins.
Let’s get stuck in, by starting with the selector sheets.
Building the Measure Selector
The measure selector will be used to set a parameter which will store the particular measure selected, so we need
pSelectedMeasure
string parameter defaulted to the value Sales
We also need a field to help us ‘draw’ the 3 selection boxes onto a viz, in such a way that we then have a measure value to pass into the parameter on selection. You could build this with its own separate dataset, but I’m going to utilise another field to ‘fake this’, and drive it off the Segment dimension as we don’t need this in the rest of the viz.
Measure Selector Alias
CASE [Segment] WHEN ‘Consumer’ THEN ‘Sales’ WHEN ‘Corporate’ THEN ‘Profit’ ELSE ‘Quantity’ END
Add Segment to Columns. Then double click into Columns and manually type MIN(1). Widen the row and then add Measure Selector Alias onto Label.
Edit the axis, and fix the range to run from 0 to 1. Adjust the label font and align centrally. Hide the axis and the Segment header.
We need to identify which measure has been selected, both through colour and an arrow indicator. So we need
Add this to the Label shelf and adjust so the fields are side by side and the arrow text is coloured red.
Finally, stop the Tooltip from displaying, then hide all gridlines, zero lines, axis and row dividers. Set the column dividers to be thick white lines and make the Size as large as possible.
Name the sheet Measure Selector.
Building the Year Selector
In order to not ‘hardcode’ the latest year, we need
Current Year
{FIXED:MAX(YEAR([Order Date]))}
format this to be a number with 0dp and not to show the thousands separator.
On a new sheet, add Comparison Year to Filter and exclude NULL. Then add Comparison Year to Columns and sort descending, so the latest year is listed first. Double click into Columns and manually type MIN(1).
As before widen the rows, and then add Comparison Year to Label. Edit the axis, and fix the range to run from 0 to 1. Adjust the label font and align centrally. Hide the axis and the Comparison Year header.
We’re going to need a parameter which will capture the year selected
pSelectedYear
integer parameter defaulted to 2022 with the display format set to not include the thousand separator
We need to identify which year has been selected, both through colour and an arrow indicator. So we need
Is Selected Year
[Comparison Year] = [pSelectedYear]
Add to the Colour shelf and adjust to suit.
Then create
Year Selected Arrow
IF [Is Selected Year] THEN ‘â–º’ END
Add this to the Label shelf and adjust so the fields are side by side and the arrow text is coloured red.
Finally, stop the Tooltip from displaying, then hide all gridlines, zero lines, axis and row dividers. Set the column dividers to be thick white lines and make the Size as large as possible. Name the sheet Year Selector.
Building the Current Year ‘card’
Double click into Columns and manually type MIN(1). Add Current Year to Label. Widen the row. Edit the axis, and fix the range to run from 0 to 1. Adjust the label font and align centrally. Hide the axis. Adjust the Colour to suit. Stop the Tooltip from displaying, then hide all gridlines, zero lines, axis and row dividers. Make the Size as large as possible. Name the sheet Curr Year.
Building the bar chart
Based on the measure selected, we need to get the value of the relevant measure for the current year and for the comparison year, so we need
Measure to Display – Curr Year
IF YEAR([Order Date]) = [Current Year] THEN CASE [pSelectedMeasure] WHEN ‘Sales’ THEN [Sales] WHEN ‘Profit’ THEN [Profit] ELSE [Quantity] END END
and
Measure to Display – Comp Year
IF YEAR([Order Date]) = [pSelectedYear] THEN CASE [pSelectedMeasure] WHEN ‘Sales’ THEN [Sales] WHEN ‘Profit’ THEN [Profit] ELSE [Quantity] END END
On a new sheet, add Sub-Category to Rows and Measure to Display – Curr Year to Columns. Sort descending. Then click and drag Measure to Display – Comp Year to the axis and release when the two green columns display. This will automatically add Measure Names and Measure Values into the view.
Reorder the pills in the Measure Values box, so the current year values are listed first. Add Measure Names to Colour and adjust to suit. Add Current Year, Measure to Display – Curr Year and Measure to Display – Comp Year to Tooltip, and adjust the tooltip so it is referencing those 3 fields along with the pSelectedMeasure and pSelectedYear parameters
So we have the bars, but now we need to add the ‘candlestick’, which we’re going to crate using a gantt bar. We need another ‘measure’ row to show, and need another instance of Measure to Display – Comp Year for this – we can’t use the existing measure, as it will put data on the same ‘row’. So simply duplicate the Measure to Display – Comp Year field, to get the Measure to Display – Comp Year (copy) field. Add this to Columns.
Change the mark type of this to a gantt bar. To get the size and the information we need for the labels and to colour the gantt, we need some more fields.
Difference
SUM([Measure to Display – Curr Year]) – SUM([Measure to Display – Comp Year])
custom format this to +#,##0;-#,##0 and add this field to both the Size and the Label shelf.
% Difference
IF (SUM([Measure to Display – Curr Year])>=0 AND SUM([Measure to Display – Comp Year])>=0) OR (SUM([Measure to Display – Curr Year])<0 AND SUM([Measure to Display – Comp Year])<0) THEN [Difference]/ABS(SUM([Measure to Display – Comp Year])) ELSE 0 END
If both the values are positive or both the values are negative, then calculate the difference, otherwise return 0, and then custom format to â–²0.0%;â–¼0.0%;- . The first section up to the ; formats the number when it’s positive, the next section formats when negative, and the last section formats when the number is 0, so in this case we’re replacing any 0 with a ‘-‘. Add this to the Label shelf.
Diff is +ve
[Difference]>0
Add this to the Colour shelf (remove Measure Names) and adjust accordingly.
Reduce the Size of the gantt bar, adjust the label so the font is smaller, organised as required, and aligned to the right. Remove all the text from the Tooltip. Then make the chart dual axis and synchronise the axis. Explicitly set the mark type of the Measure Names marks card to a bar.
Finally tidy up by hiding both axis, removing all gridlines, axis lines, zero lines and column dividers. Format the Sub-Category label headings and align middle left. Hide the Sub-Category row heading (hide field labels for rows) and hide the Measure Names field (uncheck show header). Name the sheet Chart.
Adding the interactivity
Using vertical and horizontal containers, arrange the objects on the dashboard. I used a horizonal container to align the Curr Year, Year Selector and Measure Selector sheets, adding blank objects in between. I edited the title of the 3 objects to display the required text. I then floated a blank object over the Current Year box, so it couldn’t be clicked.
To select the year and the measure, I needed parameter actions
Select Year
on select of the Year Selector sheet, set the pSelectedYear parameter, passing in the value from the ComparisonYear field
and
Select Measure
on select of the Measure Selector sheet, set the pSelectedMeasure parameter passing in the value from the Measure Selector Alias field
Finally to stop the years and measure boxes being ‘highlighted’ on click, create fields
True
TRUE
and
False
FALSE
and add both to the Detail shelf of the Year Selector and Measure Selector sheets. Then add dashboard filter action
Deselect Years
On select of the Year Selector sheet on the dashboard, target the Year Selector sheet itself, passing the values True = False.
Create another similar filter action for the Measure Selector sheet, and that should then be it!
For this week’s #WorkoutWednesday challenge, Lorna revisited a challenge set by Ann Jackson in 2019 which I completed and blogged about here. In that challenge we were using new navigational features introduced in v2018.3. In this recreation, we’re making use of the dynamic zone visibility feature introduced in v2022.3 (so you’ll need at least that version of Tableau to progress).
I used 5 worksheets to build this viz and, as required, just 1 dashboard. 4 of the worksheets relate to the KPI blocks, and the other for the bar chart.
Building the KPI blocks
I started by creating new measures
Count Customers
CountD([Customer Name])
Count Orders
COUNTD([Order ID])
Count Cities
COUNTD([City])
Count Products
COUNTD([Product Name])
On a new sheet, add Count Customers to Text. Then add Measure Names to Filter and filter to just show the Count Customers measure. Then add Measure Values to Text and Measure Names to Text. Remove the original Count Customers from Text. Set the view to Entire View and then centre align and format the text. Set the mark type to Square and increase the Size as large as possible. Set the Colour to the relevant colour and adjust the font to match mark colour. Remove any tooltips from showing by unchecking show tooltips on the Tooltip shelf. Name the sheet Customers.
Repeat the process for creating sheets for City, Products and Orders. You may find you need to format some of the measures to have no decimal places.
Finally add Aliases to Measure Names to change the display of the measure from being Count XXXX to just XXXX (right click on Measure Names in the left hand data pane – > Aliases
Note you could possibly have named the measures just Orders, Cities etc to start with, though I think a measure called Orders already existed, so I chose this way to be consistent.
Building the bar chart
We’re going to use Dimension swapping for this chart – that is build a single chart but use a parameter to determine which dimension needs to be displayed.
pDimension
String parameter which is hardcoded initially to the word Customers.
Create a new field which will determine which dimension to show
Dimension to Display
CASE [pDimension] WHEN ‘Orders’ THEN [Order ID] WHEN ‘Customers’ THEN [Customer Name] WHEN ‘Products’ THEN [Product Name] WHEN ‘Cities’ THEN [City] END
Note the names in the CASE statement to be stored in the parameter need to match the aliases we defined above.
On a new sheet, add Dimension to Display to Rows and Sales to Columns and sort by Sales descending. Add both Dimension to Display and Sales to Label. Format the label as required and align left. You may need to widen the rows to see the text.
Add pDimension to Colour and set the colour for the Customers dimension. Set the font to match mark colour.
Remove all axis and header columns (uncheck show header), all gridlines and row/column dividers. Add a title to the chart which references the pDimension field.
Now update the pDimension parameter to the word Orders, and set the colour. Repeat for Cities and Products.
Building the dashboard
Add the objects on to the dashaboard in such a way that you have a Vertical container that contains a Horizontal container with Customers and Orders side by side, then another Horizontal container underneath (still within the Vertical container), with City and Products side by side, and then finally add the Bar chart underneath. Set the 4 KPI block sheets to fit entire view and the bar chart to fit width. Try not to be tempted to adjust and heights of widths of objects manually on the dashboard, as that can affect things when we try to collapse later. You’ve probably got something like this:
Add a parameter action to drive the setting of the pDimension parameter
Select KPI
On select of any of the KPI block sheets, update the pDiemsion parameter passing through Measure Names. When selection is cleared, set the value to <empty string>/nothing
If you manually set the pDimension parameter to empty, your display should look like
Remove the titles from displaying from the KPI block sheets.
Hiding and showing the relevant sheets
To achieve this, we’re making use of Dynamic zone visibility functionality. For this we need some boolean fields to be created.
Crate a new calculated field
Dimension Selected
[pDimension]<>””
Similarly, create a new calculated field
Dimension Not Selected
[pDimension]=””
Back on the dashboard, select the Customers KPI block sheet (easiest way is to click on the object in the Item hierarchy pane at the bottom of the layout tab on the left hand side, as you may have trouble selecting on the dashboard itself due to the interactivity added). Once the sheet is selected it will have a grey border around it. On the layout tab on the left, select the Control visibility using value checkbox and select the Dimension Not Selected field.
Repeat this against the other 3 KPI block sheets.
Then for the bar sheet, do the same, except this time, select the Dimension Selected field instead. As soon as you do this, the bar should disappear, but reappear once you click on a KPI.
Candra set this seasonal fun challenge this week. It may not deliver data visualisation best practice, but as a ‘seasoned’ #WOW participant, this provided a chance to use a technique I rarely use (background images) and learn a brand new technique (colouring a bar chart with a gradient).
Getting set up
To start you’ll need to download all the files Candra provided, which will consist of the data (across 2 sheets) and 3 images.
The bauble and star image will need to be saved into your Tableau Shapes repository – I copied them into a new folder I called Xmas in this directory ..\My Tableau Repository\Shapes
The tree image just needs to be saved somewhere you’ll remember.
Building the Tree Chart
Connect to Sheet1 of the downloaded WOW Advent Calendar excel file.
Drag X to Rows and Y to Columns and disaggregate the marks (Analysis -> uncheck Aggregrate Measures)
You should get the basic layout of where the baubles are positioned. You may also notice that the X & Y values in the dataset were labelled wrong. The x-axis is your horizontal axis, but these have been stored in the Y field, while the vertical y-axis values have been stored in the X field. You may wish to rename these to save you getting confused.
Add Day to the Label shelf, and align middle centre.
We need to distinguish the bauble marks from the star mark. The star mark has a NULL value for the Day field, so we can utilise that
Day Is NULL
ISNULL([Day])
Add this onto the Shape shelf, and adjust the shape images to source the new images you saved into your shapes repository (if you can’t find them try clicking ‘reload shapes’). Adjust the initial size of the images via the slider on the Size shelf.
Now add Day Is Null to the Size shelf too. Edit the size and adjust the mark size range to suit, so the star is larger than the baubles.
Adjust the Label of the bauble so the text is larger and white.
To make the tooltip slightly more readable than that in the provided solution, I created my own custom version
Tooltip
IF [Day is Null] THEN “It’s Christmas Day!” ELSEIF [Day]=1 THEN STR([Day]) + ” day until Christmas” ELSE STR([Day]) + ” days until Christmas” END
Add this to the Tooltip shelf, and adjust so it’s just referencing this field.
Add Link to the Detail shelf – this will be needed for the interactivity later.
Now we’ll add the background image via the Maps -> Add Background Image -> <datasource> menu option. Click to add image, then browse to where you’ve saved the Xmas tree image.
We need to define the min/max coordinates for where the tree image should be positioned, and based on this, we want the horizontal X axis (the Y field) to range from 0-4 and the vertical Y axis (the X field) to range from 0 to 6. Press the Apply button and you see whether things look to be placed correctly before you close all the dialog windows.
Next we need to fix the axis, so that when interacting later, we keep the whole tree visible. The vertical axis should be fixed from 0-7, and the horizontal axis should be fixed from 0 to 4.
Then we need to remove all the axis from displaying (uncheck show header), and all row/column borders and gridlines and axis rulers.
The final step is to remove the map options from displaying when you hover over the chart – Map ->Map Options and uncheck all the options displayed in the dialog
Since the process is already documented, I don’t have to write all the steps out myself 🙂 I’ll just point out the fields I used/created, since we’re working with a different data set.
Firstly I created a new data source for this chart, which combined Sheet1 with the Range sheet, and used a relationship calculation of 1 = 1 to combine the data (Relationships didn’t exist when the blog post was written).
The equivalent of the Sales Adjusted field is the existing pre-computed Sheet1(Count) field, so we don’t need any calculated field for this.
Max Segmentsin my solution is
Max Range
WINDOW_MAX(MAX([Range (Range)]))
Note – Range (Range) is referencing the Range field from the Range table. If you just drag the field into the calculation window, it will automatically present in this way.
Total in my solution is
Total Count
TOTAL(COUNT([Sheet1]))
Sizein my solution is
Size
[Total Count]/[Max Range]
and finally Color in my solution is
Colour
([Max Range]-[Index]) * [Size]
Add Challenge Year to Rows (rather than Category), change the mark type to bar if it doesn’t change automatically. Reverse the axis so 2018 is listed at the top, then change the Challenge Year pill to be discrete (blue) rather than green.
If you follow the other blog post through, you should hopefully end up with
You then need to show the labels and adjust the tooltip against the CNT(Sheet1) marks card only.
As before, remove all gridlines borders etc.
Adding the interactivity
Add the 2 sheets to a dashboard. We need to allow the tree to filter on click of the bar chart. I used the ‘use as filter’ option on the context menu of the the bar chart object to quickly set this.
This adds a filter action to the dashboard, but is a quick way of getting it created rather than having to manually set it up via the dashboard -> actions menu.
I did use this menu though to add the URL action to the Tree sheet, which just needs to reference the Link to the Challenge field.
So a relatively short blog this week, since the most complicated section is already written up be Ken Flerlage (thanks Ken!). It was a great fun little challenge, which still allowed for plenty of learning opportunities. My published viz is here.
For #WOW2020 Week 48, Jami Delagrange set the challenge based on a problem she’d been puzzling over for a while.
The data set was one Jami created herself about turkeys creating a ‘conference’ (it is Thanksgiving week in the US).
The above is basically 2 bar charts plotting the time an event happened (on the x-axis) against the number of turkeys attending the event (on the y-axis). One bar chart is coloured based on the location, the other is a white bar which has a width based on the duration of the event.
There are a couple of calcs needed for the basic data
# Turkeys
COUNTD([User Name (Original Name)])
Duration(mins)
DATEDIFF(‘minute’,[Start time],[End time])
With these we can get a basic set of data
The Start Time and End Time fields can be custom formatted to hh:mm AMPM.
The first bar chart we need is very simple, plotting Start Time against # Turkeys, coloured by Location.
For the second chart, we need to make use of the Size shelf, and the feature that allows you to fix the size based on a field. If we add Duration (mins), we don’t get what we need
The bars are far too wide. As the Start Time being plotted is at the ‘minute’ level, each unit of the axis is 1 minute of a day. There are 24 * 60 = 1440 minutes in a day. So the size needs to be based as a proportion of the number of minutes in a day ie
Duration Proportion of Day
[Duration (mins)] / (24 * 60)
Adding into our data table, you can see what these values translate to
and if we drop this onto the Size shelf instead, we get the required display
Build these charts as a dual axis, and you’ve got the output. Tableau’s own help article, discusses the Size feature a bit more.
Donna + DataViz 