My inspiration for this week’s #WOW challenge came from Sarah Palette’s post on X from last year, and has been something I’d been meaning to try for ages, so figured a #WOW challenge was the perfect opportunity. Sarah has her own blog post which contains the core ‘trick’ to nailing the display, but as usual I’m going to walk you through step by step 🙂
After connecting to the data, start by adding Sub-Category to Rows, Sales to Columns, Category to Colour and sort by Sales descending. Adjust the colours to suit.
Format Sales to be $ to 0 dp, and add to Label. Apply a quick table calculation of Percentage of total to the Sales pill, and then format the pill to be % to 1 dp. Add another instance of Sales to Label. Adjust the layout and format the label to 8pt bold and match mark colour. Reduce the Size of the mark.
Double click into the Columns shelf, and manually type MIN(0). Then drag the MIN(0) pill from Columns and drop it on the Sales axis when the green ‘2-column’ icon appears. This automatically adds Measure Names and Measure Values into the view. Re-order the pills in the Measure Values section. This display now has 2 measures sharing a single axis.
Change the mark type to Line and line type to stepped (via the Path pill). Adjust the Label so it is labelling line ends and start of line only. Align label top right. Adjust the size of the line as required.
Click on the MIN(0) pill in the Measure Values section, and while holding down Ctrl drag the pill to Columns to create a copy of the same measure. This is important as typing in another instance of MIN(0) will create another separate measure, and we need it to be the same one.
This will create another marks card (MIN(0)). Remove the Sales pills from the Label shelf, and add Sub-Category instead. Align the label middle right, and add a couple of spaces before the Sub-Category text. Set the chart to be dual axis, synchronise the axis and then remove Measure Names from the All marks card. Adjust the width of each row to push the Sub-Category label into the corner of the L.
To give the labels at the end of the bar some ‘breathing room’, create a field
Max Sales + 10%
WINDOW_MAX(SUM([Sales])) * 1.1
Add this to the Detail shelf on the All marks card. Adjust the table calculation so it is computing by both Category and Sub-Category. Then right click on the bottom axis and Add Reference Line that uses the average of the Max Sales + 10% field for the Entire Table. Don’t display and lines/labels/tooltip
Finally tidy up by
Hiding both axis (right click -> uncheck show header)
Hiding both the Sub Category and Measure Names headers (right click, uncheck show header)
Remove all gridlines, zero lines, row & column dividers
Adjust Tooltip as required
Add a title colouring the text of the words to match the colours used in the legend.
This week’s #WOW2025 challenge was a guest post by Kieran Adair and had two parts to it; one to show Sales and Profit Ratio for 2024 only on the same chart, and the other to show comparisons to 2023 data on the same chart.
I built the first one way, and had to make changes in order to build the second. I could have chosen to rebuild the first chart using the fields I ended up creating for the second, but I chose not to, so this will ultimately be 2 blogs in one, as for each chart I needed different fields.
Restricting the data
We only need data for 2023 and 2024 for this challenge, so apply a data source filter (right click data source > Add data source filter) and restrict to Years 2023 and 2024 only.
Building the 2024 chart
We will be plotting Profit Ratio so need to create
Profit Ratio
SUM([Profit])/SUM([Sales])
format this to % with 0 dp.
Add Sub-Category to Rows and Profit Ratio to Columns. Add Order Date to Filter and restrict to the Year 2024 only. Sort the bars descending |(just click the icon on the toolbar)
Change the Mark type to circle and set the colour to black. Widen the rows a little.
Add Sales to Columns. Change the mark type of the sales marks card to bar. Set the colour to white and add a black border.
Adjust the Tooltip on the All marks card and verify both measures are displayed whether hovering over a circle or a bar.
Edit the Sales axis and set to custom to ensure it is fixed to start at 0 and end ‘automatic’. Adjust the axis title to include the text [Bars◻].
Make the chart dual axis BUT DO NOT synchronise axis. Remove Measure Names from the Colour shelf of the All marks card. Right click on the Sales axis at the to and Move marks to back.
Edit the title of the Profit Ratio axis so that it includes the text [Circles ●]. Remove all gridlines and zero lines and hide the Sub-Category row heading (right click the text and hide field labels for rows). Update the title and name the sheet RAVE 2024 or similar.
Building the YoY chart
For this version, we need to create some additional calculated fields
Sales – 2024
IF YEAR([Order Date]) = 2024 THEN [Sales] END
Sales 2023
IF YEAR([Order Date]) = 2023 THEN [Sales] END
Profit – 2024
IF YEAR([Order Date]) = 2024 THEN [Profit] END
Profit 2023
IF YEAR([Order Date]) = 2023 THEN [Profit] END
PR – 2024
SUM([Profit – 2024])/SUM([Sales – 2024])
format to % with 0dp
PR – 2023
SUM([Profit – 2023])/SUM([Sales – 2023])
format to % with 0dp
On a new sheet, add Sub-Category to Rows and PR – 2024 to Columns. Sort descending. Change the mark type to circle.
Drag PR – 2023 onto the PR -2024 axis and release the mouse when the 2 green column symbol appears.
This will automatically update the viz to include Measure Names and Measure Values. Adjust the Colour legend so PR 2024 is black and PR 2023 is grey, and PR 2024 is listed first (so black circles are on top when the marks overlap)
Add Sales 2024 to Columns. Change the mark type of the Sales 2024 marks card to bar. Remove Measure Names from the Colour shelf of the Sales 2024 marks card. Set the colour to white and add a black border.
Add Sales – 2023 to the Detail shelf of the Sales 2024 marks card. Right click on the Sales 2024 axis and Add Reference Line per cell referencing the Sales 2023 value as below.
Update the title of the Sales – 2024 axis to Sales [Bars◻] and set the axis to be fixed from 0 to automatic. Update the title of the other axis (Value) to Profit Ratio [Circles ●].
On the All marks card add PR 2023, PR 2024, Sales – 2023 and Sales -2024 to the Tooltip shelf and update to suit.
Make the chart Dual Axis but once again DO NOT synchronise the axis. As before move the marks (bars) of the Sales axis to the back, and remove the Sub-Category label. Remove all gridlines and zero lines and update the title.
And that’s it. I added both my sheets to dashboards, juts to improve the presentation slightly. My published viz is here.
In 2024 the #WOW coaching crew has extended to include Yusuke Nakanishi (@YusukeNakanish3) and Yoshitaka Arakawa (@yoshi_dataviz) from the Japanese #datafam. This week Yusuke set this challenge asking us to build donut charts that represented when percentages were > 100%.
Building out the calculations
In the data set provided the Self-sufficiency ratio for food in calorie base [%] is a number that represents the actual % value ie 90 means 90%, 196 means 196%. To help me remember that, I formatted the field to be a number with 0 decimal places which had a % suffix.
But building the donut charts we need to ‘normalise’ the figures to represent a percentage out of 100. ie, if the value is 90%, we want 90%, but if the value is 196%, we want 96%. So I created
Self Sufficient %
([Self-sufficiency ratio for food in calorie base 【%】] %100) / 100
and then formatted this to a % with 0 dp.
but I can’t build a donut chart with just this value, I need to know the non self sufficient % too
Not Self Sufficient %
1-[Self Sufficient %]
and formatted this to be a % with 0 dp too.
Let’s put these into a tabular view. Add Prefecture to Rows and Measure Names to Columns. Add Measure Values to Text. Add Measure Names to Filter and restrict to the fields Self-sufficiency ratio for food in calorie base [%], Self Sufficient % and Not Self Sufficient %. Add Fiscal Year to Filters and restrict to FY2018. Sort the data by Self-sufficiency ratio for food in calorie base [%] descending.
You can see those rows where the Self-sufficiency ratio for food in calorie base [%], Self Sufficient % and Not Self Sufficient % is over 100% have a different value for the Self Sufficient %.
Showing the Fiscal Year as a filter the user can select, I can change to FY2019 and also see how these fields are behaving; ie when Self-sufficiency ratio for food in calorie base [%] is also over 200%, I’m getting the ‘remainder’ over 200 displayed ie 216% has a Self Sufficient % of 16%, which is what is needed for the ‘bonus’ challenge.
However, due to the way I’m building, I’m going to able to get the display working for the top & bottom 7 records regardless of year (making the build of the bonus challenge a bit easier).
So with this in mind, I now what to categorise the Self-sufficiency ratio for food in calorie base [%] based on what percentage range the values fall into.
% Bracket
FLOOR([Self-sufficiency ratio for food in calorie base 【%】] / 100)
This gives me values of 0, 1 and 2. By default this field will be created within the ‘measures’ section of the data pane (ie below the line). Drag it into the top section to convert it to a discrete dimension. Alias the values (right click -> Aliases) and set as below:
Then add to Rows.
and with the filter still set for FY2019, we can see the rows are categorised into 3 brackets.
Set the filter back to 2018. Now we want to restrict to the top and bottom 7 records only. For this I use Sets.
Create a set against Prefecture (right click the field > create > set).
Top 7
Top 7 records by Sum of Self-sufficiency ratio for food in calorie base [%]
Then create another set, this time for the bottom 7
Bottom 7
Bottom 7 records by Sum of Self-sufficiency ratio for food in calorie base [%]
Then create a Combined set (right click on one of the sets > create Combined set), that includes all values from both sets
Records to Include
Add this to the Filter shelf. By default it will show the records ‘in’ the set. However based on how the order of operations in Tableau works, this will apply the conditions based on the set first before it considers the year being filtered. Ie it will get the top and bottom 7 records based on the total sum of the Self-sufficiency ratio for food in calorie base [%] field for all the data in the data set, then filter to 2018. It means it could show some records that were in the top 7 overall, but not in the top 7 for 2018. To resolve this, and to ensure the data gets filtered by the Fiscal Year first, we need to add the Fiscal Year pill on the Filter shelf to context (right click pill and Add to Context). The pill will go grey.
Next we need a way to ‘categorise’ which rows are the top and which are the bottom. Add Top 7 to Rows which will split the rows into In or Out. Alias these values so In displays as Top 7 and Out displays as Bottom 7 (right click the text > Edit Alias).
Finally, when we build the viz, we need to ensure the 7 entries for each section align with each other. For this create
Top | Bottom Index
INDEX()
and convert the field to Discrete, then add to Rows before the Prefecture pill. Edit the table calculation so that it is computing based on the Prefecture and % Bracket pills only. This gives us an index from 1-7 for each set.
Building the Top & Bottom 7 Donut chart
On a new sheet, add Fiscal Year to Filter and set to 2018. Add the pill to context. Show the filter. Also add Records to Include to Filter.
Add Top 7 to Rows, then double click into rows and manually type MIN(0) to create a ‘fake axis’. Change the mark type to Pie.
Add Prefecture to Detail and Measure Values to Angle. Ensure only Self Sufficient% and Not Self Sufficient % are the only measures displayed (remove any others by dragging them out of the Measure Values box). Add Measure Names to Colour.
Add Top | Bottom Index to Columns and edit the table calculation so it is just computing by Prefecture. This should now give 7 columns of pie charts.
Add a Sort to the Prefecture pill on the Detail shelf, so it is sorting by the Self-sufficiency ratio for food in calorie base [%] field descending.
Add % Bracket to the Detail shelf, then click on the 3 dot icon to the left, and select the colour icon to add this pill to the Colour shelf as well as the Measure Names pill.
Re-edit the table calculation associated to the Top | Bottom Index pill so it is now also computing by the % Bracket field, so you get back to your correct top & bottom 7.
Adjust the order of the pills on the Colour shelf so that the % Bracket pill is listed before the Measure Names pill. Also adjust the order of the pills in the Measure Values box so Self Sufficient % is listed before Not Self Sufficient %.
Then adjust the colours on the colour legend as below and add a dark border to the marks (via the colour shelf).
Change the year filter to FY2019, and you can adjust the colours for the 200% entries too.
To make the ‘hole’ in the donut, double click into the Rows and add another instance of MIN(0). This will create a 2nd MIN(0) marks card.
On that marks card, move Prefecture from Detail to Label. Remove % Bracket, Measure Names and Measure Values. Add Self-sufficiency ratio for food in calorie base [%] to Label. Change the mark type to circle. Set the Colour of the circle to white. Align the label middle centre, and adjust the format/font to suit.
Set the chart to be dual axis and synchronise the axis. Adjust the Size of the pie chart mark independently from the size of the circle mark, so one is slightly larger than the other.
Finally tidy up the display by hiding the axis (uncheck show header), and the Top | Bottom Index field. Remove all gridlines, zero lines, axis lines/ticks and row/column dividers. Hide the In/Out Top 7 field label heading (right click label and hide field labels for rows. Update the title of the sheet so it references the Fiscal Year field. Change the filter back to 2018. Hide all tooltips.
Building the Legend
On a new sheet double click into Columns and type MIN(0.0). Add % Bracket to Rows. Change the mark type to square and add % Bracket to Colour and Label. Adjust colours to suit and add dark border to shape.
From the donut chart sheet, click on the Fiscal Year pill in the Filter shelf and set to apply to worksheets > selected worksheets and select the sheet you’re building the legend on. This will add the pill to this sheet too and changing the value on one sheet will impact the other.
Edit the axis so it is fixed to start at -0.1 and end at 1. This will shift the display to the left.
Hide the axis, and the % Bracket header column; remove all gridlines, zero lines, row/column dividers. Hide the tooltips.
Both the legend and the donut can now be added to a dashboard, and you’ve completed the main challenge. I chose to add the Fiscal Year filter to the display so the user could switch years if they wished.
The bonus challenge – Building the trellis chart
Because of how I’ve built the above, we’ve already done the hard work to handle the 200%+ data. The challenge here now is just to display all the 47 Prefectures for a given year in a 10 x 5 grid – a trellis.
So to build this, I started with the donut chart already built and duplicated the sheet.
When creating trellis charts, we need to create fields that represent which row and which column each Prefecture should sit in. There’s lots of blogs on creating trellis charts. As we know the number of rows and columns we need, I created
Cols
INT((INDEX()-1)%10)
Take the index of each Prefecture, decrement by 1 and find the remainder when divided by 10. This means the Prefecture with the highest % value at at position (rank) 1 will be positioned in column (1-0)%10 = 0. The Prefecture at position 11 will also be positioned in column (11-1)%10 = 0.
We also need
Rows
INT((INDEX()-1)/10)
Take the index of each Prefecture, decrement by 1 and divide by 10.
Convert both fields to be Discrete.
From the duplicated sheet, remove Top | Bottom Index from Columns and In/Out Top 7 from Rows. Remove Records to Include from Filter. Don’t panic if things look odd!
Add Cols to Columns. Adjust the table calculation to compute by both %Bracket and Prefecture. Specify the sort order to be a custom sort on the field Self-sufficiency ratio for food in calorie base [%] descending.
Now add Rows to Rows and apply the same settings on the table calculation. If all is well, you should have all 47 donuts displaying in the correct order.
Hide the Cols and Rows headings, and you can then add this to another dashboard.
For this week’s challenge, Kyle revisited a previous challenge from 2020, but remade it over utilising some of the newer features of Tableau, specifically Dynamic Zone Visibility. I blogged my solution to the original challenge here, so this blog will lift some of the techniques (and documentation) I employed directly.
Define the parameters
The first step in this challenge is to define all the parameters needed, these being :
pDateSelector
A string parameter just set to contain the value Last 30 Days
This parameter will be set via a Parameter Action, so there is no need to define this a list with all the options.
pDays
An integer parameter defaulted to 120
pStartDate
A date parameter defaulted to 01 Jan 2023
pEndDate
Another date parameter defaulted to 01 Aug 2023
The chart needs to adjust based on a measure selected, so we need to capture the measure option selected
pSelectedMeasure
string parameter defaulted to Sales
This parameter will be set by a parameter action, so again there is no need to actually list the possible values.
Finally, I also created a parameter
pToday
Date parameter defaulted to 02 May 2023.
The requirements indicate the information displayed should be restricted to ‘today’, using the TODAY() function. However given the data set is static, if I use TODAY() and look at this in a year’s time, nothing will show. So instead I have ‘hardcoded’ ‘today’ using this parameter.
Using this parameter I then created
Order Date < Today
[Order Date] <= [pToday]
and added this as a data source filter set to True. This ensures that all the sheets I build is then automatically ignoring and data where the Order Date is 3rd May 2023 onwards.
Building the time-series chart
We need to determine which measure to show based on the selection made by the user and stored in the pSelectedMeasure parameter. This will be Sales,Profit or a count of the number of orders. First we need to create
Count Orders
COUNTD([Order ID])
and then we can create
Measure to Display
CASE [pSelectedMeasure] WHEN ‘Sales’ THEN SUM([Sales]) WHEN ‘Profit’ THEN SUM([Profit]) WHEN ‘Orders’ THEN [Count Orders] END
Add Order Date set to the continuous day level (green pill) to Columns and Measure to Display to Rows. This gives us all data from the earliest day in the data set up to 2nd May 2023.
We need to restrict this based on the ‘date selector’, so create
In Timeframe
CASE [pDateSelector] WHEN ‘Last 30 Days’ THEN [Order Date]>DATEADD(‘day’,-30,[pToday]) WHEN ‘Last N Days’ THEN [Order Date]>DATEADD(‘day’,-1*[pDays],[pToday]) ELSE [Order Date]>=[pStartDate] AND [Order Date]<=[pEndDate] END
If ‘last 30 days’, get the data that is greater than 30 days ago; if ‘last n days’, get the data that is greater than the last ‘n’ days ago, otherwise get the data between the start & end dates specified.
Add this to the Filter shelf and set to True.
Add another instance of Measure to Display to Rows, make dual axis and synchronise axes. Change the mark type of the first marks card to be Area and set the other to explicitly be a line.
Add pSelectedMeasure to the Colour shelf of the All marks card. Adjust the colour of the ‘Sales’ value accordingly.
If you’re not already displaying it, show the pSelectedMeasure parameter input, and manually change the value by typing in ‘Profit’. The chart will change colour, so again adjust accordingly. Repeat the process by typing in ‘Orders’.
Set the parameter back to ‘Sales’.
The tooltip needs to display different formatted values, so we need a couple of fields to handle this
Tooltip- Sales or Profit
IF [pSelectedMeasure] <> ‘Orders’ THEN [Measure to Display] END
Format this to $ with 0 dp, and set to use ( ) when value is negative.
Tooltip – Orders
IF [pSelectedMeasure] = ‘Orders’ THEN [Measure to Display] END
Format this to a number with 0 dp.
Add both these fields to the Tooltip shelf of the All marks card, and adjust the tooltip accordingly, positioning the two ‘Tooltip’ fields directly adjacent to each other
Hide the right hand axis (right click, uncheck show header), and edit the left hand axis to set the axis title to be sourced from the pSelectedMeasure parameter
Remove all gridlines, zero line, axis ticks etc and row/column dividers, and edit the date axis to remove the axis title. Name the sheet ‘Chart’ or similar.
Building the BANs
Add Measure Names to Columns
Add Measure Names and Measure Values to the Text shelf
Add Measure Names to the Filter shelf and select just the 3 measures we’re interested in.
Reorder the columns to match the requirement
(Optional) Change the mark type to shape and set the shape to be a transparent shape (see this blog post for more details)
Change the formatting of the Measure Names and Measure Values on the Text shelf to set the size of the font to suit, and align middle, centre
Format the Sales and Profit measures to be $ with 0 dp and ( ) for neg values.
Add Measure Names to the Colour shelf and adjust colours to match the ones used on the line chart. (If you’ve used the transparent shape, edit the label to set the font to match mark colour).
Format the display to remove the row lines
Hide the Measure Names heading (right-click the Measure Names pill on the Columns shelf and unselect Show Header).
Add In Timeframe = True to the Filter shelf to restrict the data.
Uncheck the show tooltip option from the Tooltip shelf to stop the tooltip from displaying.
Name the sheet ‘BANs’ or similar
Building the Date Selector
On a new sheet, double click into the Columns shelf and type in MIN(0.0) Change the mark type to Shape.Create a new field
Label: Last 30 Days
‘Last 30 Days’
And add this to the Label shelf.
Create anew field
Is Selected Date Option – Last 30 Day
[pDateSelector] = [Label:Last 30 Days]
And add this to the Shape shelf. Adjust the shape for the ‘True’ value to be a filled circle.
Show the pDateSelector parameter and change the text in some way. This should change the ‘shape’ to ‘False’. Set this shape value to be an open circle.
Change the colour to be a dark grey, and increase the font size. Uncheck ‘Show Tooltip’.
Now type in another instance of MIN(0.0) to Columns. Create the following fields
Label:Last N Days
‘Last N Days’
and
Is Selected Date Option – Last N Days
[pDateSelector] = [Label:Last N Days]
ON the 2nd MIN(0.0) marks card, replace the fields on the shape and label shelves with these ones.
By default, the ‘last n days’ shape should be set to false, so make sure it’s an open circle. Change the value in the pDateSelector parameter to ‘Last N Days’ and the shape should now be ‘true’ – set to a closed circle.
Create a 3rd instance of MIN(0.0) on Columns and create
Label:Custom Dates
‘Custom Dates’
and
Is Selected Date Option – Custom Dates
[pDateSelector] = [Label:Custom Dates]
and repeat the process above
Edit the axis of each to be fixed from -0.2 to 2. This has the effect of ‘left aligning’ the marks.
Hide the axis, and remove the row & column dividers, all gridlines and zero lines and axis lines.
Add Measure Names to the Detail shelf of the All marks card. The right click on Measure Names in the left hand data pane and select Alias. In the dialog box that presents, alias the 3 MIN(0.0) fields as per the 3 date selector options.
This step is the key to enabling the parameter action that will be set up to pass the appropriate ‘value’ into the pDateSelector parameter
Finally format the background of the whole worksheet to be grey.
Controlling the visibility of the selections
On the dashboard, I used a vertical layout container to add my title, the BANs and the Chart. Between the title and BANs, I added a horizontal container. On the left side of that I added the Date Selector sheet. On the right I then had another vertical container which contained other containers to display the start & end data parameters. It took a bit of ‘twiddling’ to get everything where I wanted.
I added the pDays parameter as a floating object and positioned it below the Last N Days option. I used the control visibility using value option to set this to display based on the value of the Is Selected Date Option – Last N Days calculated field.
Similarly, for the date input fields, I had the section all within a single container, so I set the visibility at the container level, rather than the object level (although you could repeat the step against all the objects you need to hide individually). For the container, I used the control visibility using value option to set this to display based on the value of the Is Selected Date Option – Custom Dates calculated field.
Adding the interactivity
To change the timeframe displayed in the BANs and chart, create a dashboard parameter action
Set Date Selection
On select of the Date Selector sheet, target the pDateSelector parameter, passing though Measure Names. When the selection is cleared, reset to ‘Last 30 Days’.
To change the measure displayed in the chart, create a dashboard parameter action
Select Measure
On select of the BANs sheet, target the pSelectedMeasurew parameter, passing through Measure Names. When the selection is cleared, reset to ‘Sales’.
Prevent the BANs and Selected Date option from being selected/highlighted
By default, clicking on one of the BAN numbers, or selecting an option in the date selector, will leave the option chosen ‘highlighted’ or ‘selected’ while the other options are ‘faded out’. To
Create two calculated fields
True
TRUE
False
FALSE
Add both these fields to the Detail shelf of the BANs sheet and the All marks card on the Date Selector sheet.
Then on the dashboard, create a dashboard filter action
Deselect BANs
On select of the BANs sheet on the dashboard, target the BANs sheet directly, passing through selected fields where True = False. Show all Values when the selection is cleared.
Repeat the exact process for the Date Selector sheet, creating a dashboard filter action called Deselect Date Selector.
You should now have a complete dashboard. My published viz is here.
For this week’s challenge, Kyle got us to look at dashboard layout, specially using containers to arrange the charts and KPIs. He added in a sprinkling of interactivity to make the challenge more complete.
The charts aren’t overly complex, so I won’t go into too much detail on building them all out. I used the latest version of Superstore, v2023.1.
Building the KPIs
When I first did this, I built the KPIs on a single sheet, then realised that wouldn’t work to get the layout required, so I ended up with 3 sheets.
Rename Sales to SALES and format to $ with 0 dp.
Add Measure Names to Filter and select SALES only.
Add Measure Names and Measure Values to the Text shelf.
Align centrally and format the text (I used font size 12pt and 20pt).
Add Category to the Filter shelf, and select all. Set the filter to apply to worksheets > all using this data source
Add Order Date to Filter and select Range of Dates. The Order Date pill will be ‘green’. Click on the context menu of the pill and select the Month (May 2015) option, so the range of dates will change at monthly intervals rather than daily. Set the filter to apply to worksheets > all using this data source
Create a field called True containing the value TRUE and False containing the value FALSE and add both these fields to the Detail shelf. These will be needed later to stop the sheet from remaining highlighted on selection.
Stop the Tooltip from displaying.
Name the sheet Sales
Repeat the steps for the Profit Ratio measure – if this doesn’t exist, create the field
PROFIT RATIO
SUM([Profit])/SUM([Sales])
and format this to % with 1 dp.
For the Orders measure create
ORDERS
COUNTD([Order ID])
If need be, you can simply duplicate the Sales sheet and just change the Measure Names filter to the appropriate measure.
Creating the Line Chart
Create a parameter to store the name of the selected measure.
pSelectedKPI
string parameter defaulted to the word ‘ORDERS’
Create a field to store the measure to display based on the value of the parameter
Measure to Display
CASE [pSelectedKPI] WHEN ‘ORDERS’ THEN [ORDERS] WHEN ‘SALES’ THEN SUM([SALES]) WHEN ‘PROFIT RATIO’ THEN [PROFIT RATIO] END
On a new sheet, add Order Date to Columns and set to be a green (continuous) month level and Measure to Display to Rows.
I wanted to make my tooltips and labels reflect the measure selected (this isn’t actually part of the challenge). I created
Label – Orders
IF [pSelectedKPI] = ‘ORDERS’ THEN [ORDERS] END
format this to a number with 0 dp.
Label – Profit Ratio
IF [pSelectedKPI] = ‘PROFIT RATIO’ THEN [PROFIT RATIO] END
format this to % with 1 dp
Label – Sales
IF [pSelectedKPI] = ‘SALES’ THEN SUM([SALES]) END
format this to $ with 0 dp.
Add all 3 fields to the Tooltip shelf.
Also create
Chart Label
PROPER([pSelectedKPI])
this is a new function introduced in v2023.1 and will convert ‘ORDERS’ to ‘Orders’ and ‘PROFIT RATIO’ to ‘Profit Ratio’ etc. Add this to Tooltip too.
Modify the Tooltip as below – the 3 Label fields should be directly side by side with no spacing. Only one field will have a value at any time.
Remove the titles from the axes, and remove all gridlines. Name the sheet Line.
Building the Bar Chart
Add Category to Rows and Measure to Display to Columns. Sort descending. Add the 3 Label fields to the Label shelf, and arrange side by side.
Add Chart Label to Tooltip and adjust the tooltip.
Hide the axes, remove all gridlines/axis rulers etc and hide the Category label. Adjust the formatting of the fonts as required. Name the sheet Bar.
Building the dashboard
Describing using layout containers can be quite tricky – objects move around as you place things in. I’m going to do my best to describe the set up/structure I have and hope that it gets you what you need.
My preference is to always start with a floating container sized as per the dashboard. I then add tiled objects into that. I also have a habit of trying to rename my containers in the navigation layout pane to help me find the right section.
So let’s start. Create a new dashboard and set it to to 1200 x 900px.
Click the Floating button at the bottom of the left hand pane, and add a Horizontal container. Set the x & y position to be 0,0 and the width to 1200px and height to 900px. Rename the container to Base.
From the Objects list on the Dashboard pane, click Tiled and add a Text object. Enter the text for the dashboard title. Add a blank object beneath the text object.
When working with containers, it’s always good to add blank objects as a ‘starting point’ These all then get removed.
Now add a vertical container between the Title and Blank object. Name this container Main Body. Add a blank object into that container.
Add a vertical container to the left of the blank object in the Main Body container. Name this container Left Nav. Add a Text object into the Left Nav container and enter the instructional text. Add a blank object above the instructional text.
Add another vertical container to the right of the Left Nav container within the Main Body container. Add the Bar and the Line chart into this container, one above the other. Call this container Charts.
At this point a Tiled container object will have been automatically added containing the parameter.
Leave this for now – we’ll address this shortly.
We can remove some of the blanks now too. Remove the blank within the Main Body container, that is to the right of the Charts container. You can select the object on the item hierarchy layout, right click and remove from Dashboard.
You can also remove the blank object at the bottom of the Base container, below the Main Body container. Your item hierarchy should look something like
Now add another Horizontal container to the charts container, above the bar chart. Add the 3 KPI sheets into this container, position side by side. Name the container KPIs. Select the KPIs container, and use the context menu to Distribute Contents Evenly.
If they haven’t already appeared, the select one of the charts/KPIs and from the context menu select Filters > Category and then Filters > Month of Order Date to get the filter controls visible on the dashboard. They should appear on the right hand side, within that Tiled container (underneath the viz).
From the item hierarchy section, expand the Tiled container until you find the controls listed (see how many containers that got automatically added!).
Select the Category filter (easiest to do this by clicking on the object in the item hierarchy), and then move that object and position it above the blank object in the Left Nav container. Then delete the blank object. Select the Month of Order Date filer object and do the same.
Now we’ve got everything we want , we can remove the Tiled container and all of the objects it contains from the dashboard. Just right click on the first Tiled container in the item hierarchy and Remove From Dashboard (say yes/ok to any prompt that appears). You should have something that looks a bit like this – not the item hierarchy layout.
Now you’ve got everything needed on the dashboard in the right containers, we need to tidy it all up.
Fix the width of the Left Nav container to around 205 px.
Change the Category filter to single value dropdown, defaulted to All
Fix the height of the KPIs container to around 175 px
Remove the titles from all the KPI objects, and ensure all set to fit entire view
Amend the titles of the bar and line charts to reference the Chart Label field.
Fix the height of the bar chart to around 340px and set to fit entire view.
Set the background colour of the whole dashboard to grey (Format menu -> Dashboard – Dashboard Shading)
Set the background colour of the bar and line chart to White and add a grey border (slightly darker than the background colour)
Add borders round the 3 KPI charts too.
Set padding around all the objects – I tend to use both inner and outer padding. The key is consistency to ensure the spaces between the objects are the same. I typically start with 10 px outer padding all round, and then adjust as required. Sometimes you may add padding to the container and not to the objects themselves, other times you may set the container padding to 0 and apply to the objects, or a combination of both.
Adding the Interactivity
To set the category bar chart to work as a filter, simply select the object and from the context menu select use as filter. Then go to the Actions list (Dashboard > Actions) and edit the ‘Filter 1 (generated)’ action and rename it to something more useful eg Filter bu Category.
For the bar & line chart to update ‘on click’ of a KPI, add a parameter action
Select KPI
On selection of the Orders, Profit Ratio or Sales sheet, set the pSelected KPI parameter, passing the value from the Measure Names field. Keep current value when selection is cleared.
Finally to prevent the KPIs from remaining highlighted in blue on selection, add 3 filter actions (1 per KPI) set up as follows
Deselect Sales KPI
On selection of the Sales sheet on the dashboard, target the Sales sheet directly, and set the fields as Source = True to Target = False.
Finally, to collapse the left hand nav section, select the Left Nav container and from the context menu, select add Show/hide button. A X button will appear which is floating by default. Move this to where you choose (you might need to add some additional left padding to the title to make space.
Load the dashboard in presentation mode to easily test the hide/show functionality.
Once you’ve grasped the concept of containers, they really are the best way of controlling the behaviour and layout of objects on your dashboard. When I’m building something formal, I personally never want to have a Tiled container on my dashboard – this is an object that gets automatically added, and you can see from above, how many nested containers it ended up adding through a single action I took. If you’re not careful, you can end up with such a nest of containers, that it can get really hard to unpick.
For her final challenge of #WOW2022, Erica set this interesting challenge; to show how the sales of the top n Products within each Sub-Category compared against the total sales. An added twist was to flip the display between actual sales, and % of total.
It did take me a short while to get my starting point. When I’ve worked with the concept of proportional brushing before, it’s typically involved set actions and one viz driving the interactivity on another viz via a dashboard action. I didn’t have this. I’ve obviously come up with a solution, but I’m not sure if it’s overly complicated… it doesn’t seem it, but for some reason I feel it could have been simpler than I’ve made it. Only time will tell as I check out other solutions.
My solution involves table calcs, so as I do with many challenges, I’ll start by building out all the data I need in tabular form.
Defining the calculated fields
Start off by adding Sub-Category to Rows, Product Name to Rows and Sales into Text and sort descending. We want to identify the Product Names in the top n, so lets first rank the products per sub-category. Create a calculated field
Sales Rank
RANK_UNIQUE(SUM([Sales]))
Make it a discrete field, then add it to Rows between Sub-Category and Product Name. Set the table calculation to compute using Product Name.
The rows should be numbered from 1 upwards, and restart at 1 when the Sub-Category changes
We want to get the sum of the sales for all those products in the top n. We’ll use a parameter to identify the n
pTop
Integer parameter defaulted to 10
Then we’ll use another table calculation to get the sum of the sales in that top n
Sales in Top n
WINDOW_SUM(IF [Sales Rank]<=[pTop] THEN SUM([Sales]) END)
If the Sales Rank is less than or equal to the top parameter, then get the Sales for that row, then sum all the sales values up together.
Show the parameter on screen, and add Sales in Top n into the table, setting the table calc to once again compute using by Product Name.
You should see that the sum of the sales for the rows marked 1-10 should equate to the value in the Sales in Top n column for the Sub-Category
We also need to get the total sales for each Sub-Category, which we can do with
Sub Cat Sales
{FIXED [Sub-Category]: SUM([Sales])}
Format to $ with 0 dp and displayed in k.
With this , we can also determine the proportion of sales
Top n % of Total
[Sales in Top n]/SUM([Sub Cat Sales])
Format this to a % with 0 dp.
Add both these fields to the table, making sure the table calcs are set to compute using Product Name.
So now we’ve got the base fields that form the building blocks for the viz. But we have this added functionality where we want to show either actual values (ie Sales in Topn and Sub Cat Sales), or we want to show the Top n % of Total compared to the ‘whole’ ie 1.
To manage this, we first need the parameter to control the decision
pView
integer list parameter set to 0 or 1, with appropriate display values. and defaulted to Sales Value
We can then build
Measure 1 to Display
CASE [pView] WHEN 0 THEN [Sub Cat Sales] ELSE 1 END
and
Measure 2 to Display
CASE [pView] WHEN 0 THEN [Sales in Top n] ELSE [Top n % of Total] END
Pop these into the table and show the pView parameter. Test changing the parameter and see the results. Don’t worry that the % shows 0.. you can format the Measure 2 to Display field to show 2 dp if you want to see the data really changing.
Building the bar chart
On a new sheet, add Sub-Category to Rows, Product Name to Detail and Measure 1 to Display to Columns.
Drag the Measure 2 to Display field onto the Measue 1 to Display axis, and release when the 2 green columns symbol appears
Set the Measure 2 to Display field to compute using Product Name, then move Measure Names from Rows to Colour, and adjust the colours. Ensure Measure 2 to Display is listed first in the colour legend, so the marks are on top. Set stack marks to off (Analysis menu -> Stack marks -> off) to make the bars a single block, rather than being split per Product.
Switch the view parameter to change the display and verify it behaves as expected
Add the Top n % of Total field to Rows and change to be discrete (blue). Ensure the field is set to compute by Product Name still.
Create a new field
Label to Display
CASE [pView] WHEN 0 THEN [Sub Cat Sales] ELSE NULL END
Format this to $ with 0 dp and displayed in k. Add this to the Label shelf
Add Sub Cat Sales to the Tooltip shelf and then adjust the tooltip to match.
The final step (other than formatting) is to apply the sorting. Create a new field
Sort
CASE [pView] WHEN 0 THEN SUM([Sub Cat Sales]) * -1
ELSE [Top n % of Total] *-1 END
Make this field discrete and then add to Rows in front of the Sub-Category pill. You should now find the bars are being sorted descended depending on the view being displayed
Finally, tidy everything up – Uncheck show header on both the Sort and Measure Values pills. Hide field labels for rows to remove the column headings. Change the font size/format of the first two columns, and adjust the colour of the bar label. Remove all gridlines, column banding etc.
Finally add to a dashboard. I used a horizontal container, text boxes and padding to build up the parameter selections at the top. My published viz is here.
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.
Week 32 of #WOW2022 and Kyle set this challenge to build a dumbbell chart (also sometimes referred to as a barbell chart, a gap chart, a connected dot plot, a DNA chart). I first built one of these a long time ago, and have built many since, so chose not to refer to Ryan Sleeper’s blog Kyle so generously provided. I figured I’d have a go myself from memory. Looking back now, I actually started by going down the same route a Ryan, but then switched my approach in order to handle the tooltip requirements.
So here’s what I did.
Defining the calculations required
I’m going to start with these, as it’s then easier just to explain how everything then gets built. In reality, some of these calculations came along mid-build.
I decided to create explicit fields to store the Ownership values for the 2015 & 2019 years ie
Ownership 2015
IF [Year]=2015 THEN [Ownership] END
Ownership 2019
IF [Year]=2019 THEN [Ownership] END
Both these fields I formatted to % with 0dp.
Given these, I can then work out the difference
Difference
SUM([Ownership 2019])-SUM([Ownership 2015])
also formatted to % with 0dp
and finally, I can work out whether the change exceeds 20% or not
Difference>0.2
[Difference]>0.2
These are all the fields required 🙂
Building the Dot Plot
Add Age to Columns as a discrete dimension (blue pill) and Ownership 2015 to Rows. This will create a bar chart.
Then drag Ownership 2019 onto the canvas, and release your mouse at the point it is over the Ownership 2015 axis and the 2 green columns symbol appears.
This will have the effect of adding Measure Names and Measure Values to the view.
Change the mark type to Circle and move Measure Names from Columns onto Colour. Adjust colours accordingly.
Show mark labels and adjust so they are formatted middle centre, and are bold. Increase the size of the circles so the label text is completely within the circle, and the font colour should automatically adjust to white on the darker circles and black on the lighter ones.
Add Ownership 2015, Ownership 2019 and Difference onto the Tooltip shelf, and adjust the tooltip to suit. Note – I chose to adjust the wording of the tooltip to not assume there was always an increase. I used the word ‘change’ instead and as a result applied the custom formatting of ▲0%;▼0% to the Difference field that was on the Tooltip shelf.
So my tooltip was
which generated a tooltip that looked like
Connecting the dots
Add another instance of Measure Values to Rows alongside the existing one. This will generate a 2nd Measure Values marks card.
On that marks card, remove Measure Names from Colour and add Difference>0.2 to the Colour shelf instead, and adjust the colours.
Change the mark type to Line and add Measure Names to the Path shelf.
Move the Difference pill from the Tooltip shelf to the Label shelf. Edit the label controls, so the label only displays at the start of the line, which should make the label only show at the bottom. Adjust the label alignment so it is bottom centre.
Delete all the text from the Tooltip of the ‘line’ marks card.
Now make the chart dual axis and synchronise the axis. You’ll notice the bars sit on top of the circles, and the difference text is not displaying under the circles.
To remedy this, first, right click on the right hand axis, and select move marks to back. The lines should now be behind the circles.
Then, on the lines marks card, edit the Label text and just add a single carriage return in the dialog box, so the text is shifted down.
Adding Age to be displayed at the top
The quickest way to do this is to double click in the space on the Columns shelf, next to the Age pill, and type in ‘Dummy’ or just ” if you prefer. This has the affect of adding another ‘dimension’ into the view and as a result the Age values immediately get moved to the top.
Now just remove all axis, all gridlines, all row & column dividers. Then uncheck show header against the Dummy pill and hide field labels for columns for the Age header (right click on the Age label in the view itself to get this option, not the pill). Then format the Age text to be a bit bigger and bolder, and also increase the Size of the lines mark type. And that’s your viz 🙂
Additional Note
If you had followed Ryan’s blog post, then you would still have needed to create separate calculated fields to store the ownership values for 2015 and 2019, but these would have had to been LOD fields eg
Ownership 2015
{FIXED [Age]:SUM(IF [Year]=2015 THEN [Ownership] END)}
and a similar one for Ownership 2019. The other 2 fields related to the difference would then need to reference these instead.
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
Ann Jackson set this week’s #WOW2021 challenge, based on a recent ‘real world’ situation she had encountered.
Analysing Ann’s solution (by interacting with her published solution), I deduced we’d need to use set actions to add and remove the selected Sub-Categories into and out of the set (I hadn’t noticed Ann had tagged the challenge on the main page with Set Actions 🙂 ). I also realised the initial visual in the first column, wasn’t using reference lines to depict the target, as the tooltip displayed on hover, was much more detailed than what you can add to a reference line tooltip.
So armed with this knowledge, I set about building what was required.
Defining the required calculations
Building the BANs
Building the viz
Adding the interactivity
Defining the required calculations
This is one of those challenges where I want to get all the calcs sorted up front in a tabular view, before even attempting the viz. I’ll go through what I ended up with, but be assured this did take a bit of time and change of direction to get what I needed.
Let’s start with parameters. Firstly I created a parameter to simulate ‘today’, that is hardcoded to 9th June 2021
I then created a parameter to store the % uplift we want to use for one of the goal options. I default this to 0.1 (ie 10%)
We need to work out the sales so far this year (ie the sales in 2021 up to and including 9th June 2021), and the equivalent sales for the previous year (ie the sales in 2020 up to and including 9th June 2020).
SALES YTD by Sub Cat
IF YEAR([Order Date]) = YEAR([pToday]) AND [Order Date] <= [pToday] THEN [Sales] END
SALES LY YTD by Sub Cat
IF YEAR([Order Date]) = YEAR([pToday])-1 AND [Order Date] <= DATEADD(‘year’,-1,[pToday]) THEN [Sales] END
Let’s pop these into a table with the Category and Sub-Category fields, and add grand totals, so we can see what figures we need to be aiming for the BANs at the top.
Now let’s work out the values for the goals.
One goal is based on finding the average sales per day in 2021, then extrapolating this across the whole year.
So firstly, we need to know how many days in the year up to ‘today’.
This finds the number of days between 01 Jan 2021 and 01 Jan 2022.
These methods ensure the right number of days is recorded if the ‘current’ year happens to be a leap year.
We can now work out one of the goals
Same Pace Goal
(SUM([SALES YTD by Sub Cat])/[# Days So Far This Year]) * [# Days In Year]
Add up the Sales so far this year and divide by the number of days so far to get the average sales value per day, then multiply by the total number of days in the year.
For the other goal, we need to determine the sales for the whole of the previous year, and multiple by the uplift %.
SALES LY
IF YEAR([Order Date]) = YEAR([pToday])-1 THEN [Sales] END
This gives the total sales for 2020.
LY + Percent Increase
SUM([SALES LY])*(1+[pPercentIncrease])
This applies the % increase parameter to the total sales for 2020.
Now we want to decide which of the goal values to use based on ‘selection’. To define the selected Sub-Categories, we’re going to use a set. Right-click on Sub-Category > Create > Set, name accordingly and select ‘Phones’ as the initial value in the set.
Use Last Year With Increase
Now we can use whether the record is in or out of the set in the logic to determine the goal to use
YEAR_END GOAL per Sub-Cat
IF ATTR([Use Last Year With Increase]) then [LY + Percent Increase Goal] ELSE [Same Pace Goal] END
Add the set and the goal field to the table, and you’ll see the value in the final column is matching the relevant previous columns, depending on whether the Sub-Category is IN or OUT of the set.
NOTE – when you do this, you’ll get In or Out displayed against each row. To get the description as I’ve got displayed, right click on the text In or Out and Edit Alias.
These data fields are going to be used to build out the central viz. There’s a couple of other fields we need to finish off the data requirements for the viz
Value to Goal
[YEAR-END GOAL Per Sub Cat]-SUM([SALES YTD by Sub Cat])
and
% of Goal
SUM([SALES YTD by Sub Cat])/[YEAR-END GOAL Per Sub Cat]
This field needs to be formatted to percentage with 0 dp (all other monetary fields need to be formatted to $ with 0 dp).
So now we have the core fields stored against each row that will help us build out the main viz. You can edit the set and add further sub-categories, so you can validate how the values change.
Now we want to work on the data we need for the BANs. You may think the grand totals sum up all the rows above, however while this works fine for the SALES YTD by Sub Cat and SALES LY YTD by Sub Cat columns, it isn’t the case for the YEAR-END GOAL by Sub Cat column.
If we select all the rows in that column, and examine the tooltip that appears on hover, the total of the selected columns differs from the total at the bottom of the column.
The value in the hover text is what we need. The discrepancy with the column grand total is because it’s working across the whole data and not row by row. Because at least 1 record is in the set, its taking the logic to use the same pace goal. You’ll notice the total of this column matches the total of the Same Pace Goal column. In fact as you add more values to the set, the total will match the Same Pace Goal total up until all values are in the set. At that point the total will match the LY + Percent Increase column.
So back to the summary measures. We’re going to use table calcs to solve this.
SALES YTD
WINDOW_SUM(SUM([SALES YTD by Sub Cat]))
SALES LY YTD
WINDOW_SUM(SUM([SALES LY YTD by Sub Cat]))
YEAR-END GOAL
WINDOW_SUM([YEAR-END GOAL Per Sub Cat])
These are all basically summing up the values displayed in the rows on the screen. Add them to the table, and you can see the same values are displayed on each row which tally to other data in the table
Right, we have all the data fields we need, let’s start building.
Building the BANs
On a new sheet, add the fields as below
We want to turn this into 1 row.
Create a field called
Index
INDEX()
and add this to the Rows shelf and change to be discrete (blue pill). Each row should be numbered from 1 to 17.
Now drag the Index field from the Rows shelf onto the Filter shelf and when prompted select 1.
You’ve now got 1 row displayed, but the data associated to all the rows is being included in the calcs (if you’d filtered just to Sub Category = Accessories for example, the date would be just related to the rows with the Accessories value).
Now we can tidy this to look how we want
Add Measure Names to Text shelf (change display to entire view if need be).
Hide Sub-Category from displaying (uncheck Show Header)
Right Click on each column title and Edit Alias to remove the ‘along…’ text
Format the text appropriately and align centrally
Adjust the tooltip to remove the Sub-Category info
Hide the Measure Names from displaying (uncheck Show Header).
Format the Row Dividers to be thick
Building the Viz
Add Category and Sub-Category to the Rows shelf on a new sheet. Change the sort of the Category field to sort by data source order, descending. Change the sort of the Sub-Category field to sort as below
Add SALES YTD by Sub Cat to Columns, then drag SALES LY YTD by Sub Cat onto the canvas and drop onto the SALES YTD by Sub Cat axis when you see the 2 columns appear
Drag Measure Names from the Rows shelf to the Size shelf. Add Measure Names to the Colour shelf. Adjust sizes and colours accordingly. Add a white border to the bars on the colour shelf.
Turn stack marks off (Analysis > Stack Marks > Off) to allow the bars to overlay each other.
Add both SALES YTD by Sub Cat and SALES LY YTD by Sub Cat to the Tooltip shelf, so you can reference both values regardless which bar you’re hovering over.
Add YEAR-END GOAL by Sub Cat to the Columns shelf and set to dual axis and synchronise axis. Reset the mark type of the Measure Values card to bar, and of the YEAR-END GOAL by Sub Cat card to Gantt.
Remove the Measure Names from the Size and Colour on the YEAR-END GOAL by Sub Cat card. Set the colour of this mark to black.
On the All marks card, add YEAR-END GOAL by Sub Cat to the Tooltip shelf so once again it’s value can be referenced by all marks.
Adjust the tooltip on the All marks card to match.
‘Type in’ MIN(0) to the Columns shelf (double click in the space next to the pills to enable the text edit feature). On this MIN(0) marks card, change mark type to Text and add Value to Goal to the Text shelf. Adjust the text to include the additional wording and adjust size too if need be. Adjust tooltip too.
Repeat this process for the % of Goal field too.
For the circles, create another MIN(0) column in the same way, but this time change mark type to circle, and add Use Last Year with Increase set to the Colour shelf and adjust. Adjust tooltips.
Format axes/gridlines/text/row banding accordingly and rotate text of the Category field.
Adding the interactivity
Add the sheets to a dashboard. Add a set action to add Sub-Categories to the set on click (Dashboard > Action > Add Action > Change Set Values). Set the action to run on select (on click) of the chart viz, to add values to the Use Last Year with Increase set.
We then need another set action to remove the Sub-Categories, but this needs to work by clicking a link in the tooltip which displays. The name of this action will display on the tooltip.
And fingers crossed.. that should be it! My published viz is here.
Donna + DataViz 