This week’s challenge by Lorna was to deliver some functionality without using LODs or table calculations. She hinted that parameters and parameter actions would be your friends.
Setting up the parameters
Create a parameter to capture the month selected
pMonth
date parameter defaulted to 01 April 2023
and then create one that will store the value associated to the month selected
pMonthSales
float parameter defaulted to 0
Building the Viz
On a new sheet add Order Date as a continuous (green) pill at the month-year level to Columns. Add Sales to Rows.
Create a new calculated field
Difference from Selected Sales
SUM([Sales]) – [pMonthSales]
and add this to Rows.
Change the mark type of this second marks card to bar and add Difference From Selected Sales to the Colour shelf. Adjust the colour to a diverging scale and centre at 0
Set the Size of the bars to be Manual rather than Fixed and adjust the slider to suit.
Add a reference line to the Order Date axis, that references the pMonth parameter.
Adjust the Tooltips, remove gridlines and add a title.
Adding the interactivity
Add the sheet to a dashboard. Add a dashboard parameter action
Set Date
On select of the viz, update the pMonth parameter with the value from the Month([Order Date]) field.
Add another dashboard action
Set Value
On select of the Viz, update the pMonthSales parameter with the value from the SUM(Sales) field that is aggregated at the SUM level.
Now if you click on a point on the line chart, the bottom bars should alter, but they’ll all appear ‘faded’ initially.
To resolve this, create a new calculated field
HL
“HL”
and add this to the Detail shelf of the All marks card. Then create a dashboard highlight action
Highlight
On select of the viz, highlight the HL field only
When you now click, the bottom marks aren’t faded as they essentially are all ‘highlighted’ too.
This week’s #WOW2024 challenge was set by Yusuke, challenging us to create a filter for the line chart, using selections from the bar chart. The main aim was to make it as easy to select months with low sales as it is to select months with higher sales.
Building the bar chart
Create a new field
Monthly Sales
[Sales]
and format to $ Thousands (K) with 0 dp.
Also create
Order Date Month
DATE(DATETRUNC(‘month’, [Order Date]))
Add Order Date Month to Columns at the continuous month level (green pill) and add Monthly Sales to Rows. Change the mark type to Bar and set the size of the bar to as wide as possible. Edit the date axis, and remove the title, then fix the tick marks to start on 1st Jan 2021 with an interval of every 1 year.
Create a set call Order Date Month Set, based off of the Order Date Month field (right click the field > create > set, and select a set of dates). Add Order Date Month Set to the Colour shelf and adjust the colours accordingly. Add a dark grey border to the bars too. Modify the Tooltip to suit.
Create a new field
Max Monthly Sales
{MAX({FIXED [Order Date Month]: SUM([Sales])})}
(for each month, get the sum of sales, then return the maximum of all these).
Add this field to Rows. On the Max Monthly Sales marks card, reduce the opacity to 25% and remove the border (all on the Colour shelf)
Set the char to Dual Axis and synchronise the axis. Remove the Measure Names field from the All marks card.
Hide the right hand axis, remove all row and column dividers. Darken the row gridlines slightly, and add the instructional text as the title of the sheet.
We are ultimately going to make use of set actions to define the dates selected by the user. For this we will need to pass the exact date selected, so add Order Date Month to the Detail shelf of the All marks card as a continuous exact date (green pill).
We’re also going to not want the bars to be ‘highlighted’ when selected, so create fields
True
TRUE
and
False
FALSE
and add both to the Detail shelf of the All marks card.
Building the Line Chart
Create a new field
Selected Sales
IF [Order Date Month Set] THEN [Sales] END
and format to $ Thousands to 2dp.
On a new sheet add Order Date to Columns at the continuous day level (green pill), add Region to Rows and add Selected Sales to Rows.
Change the colour of the line and set line markers (via the colour shelf) and reduce the size of the line. Show mark labels, and set to just label the maximum value per pane.
Adjust the row banding so the band size is set to 1
Remove the column dividers, but set the row dividers to be darker grey. Adjust the row gridlines to be a slightly darker grey. Adjust the title of the Selected Sales axis, and remove the title from the date axis. Format the data axis, so it displays a custom date format of mmm dd. Right click the Region label at the top of the chart and hide field labels for rows.
Create fields
Min Date
{MIN(IF [Order Date Month Set] THEN [Order Date Month] END)}
which will return the date of the earliest month selected in the set and
Max Date
DATE(DATEADD(‘day’, -1, DATEADD(‘month’, 1, {MAX(IF [Order Date Month Set] THEN [Order Date Month] END)}) ))
which finds the maximum month selected in the set (which will be 1st of the max month), adds on a month, and takes off a day to get the last day of the maximum month.
Add these to the Detail shelf as continuous exact dates, and then update the Title of the sheet to reference the fields.
Then create
Tooltip: Date
[Order Date]
and add to the Tooltip and adjust the Tooltip to suit.
Finally, depending how the user selects the dates, there may end up being a break in dates. Right click on the Order Date MonthSet and select Show Set. Adjust the dates, so there is at least 1 unselected value between the dates.
To make a continuous line between the dates, click the context menu against the Selected Sales pill on Rows and select Format. On the options on the left hand side, select Pane and at the Special Values option, select Marks: Hide (Connect Lines).
Adding the interactivity
Put the 2 sheets onto a dashboard. Create a dashboard set action
Select Months
On select of the bar chart, target the Order Date Month Set by assigning values to the set when the action is run, and keeping set values when the selection is cleared.
To stop the bars from highlighting on selected, create a dashboard filter action
Deselect Marks
On select of the bar chart on the dashboard, target the Bar Chart sheet, passing in the fields True = False.
And this should now complete the challenge. My published viz is here.
As the Paris 2024 Olympic Games continues, then #WOW continues with another Olympic themed challenge, this time set by Kyle.
Defining the core calculations
There’s a lot of LoD calcs used in this, so let’s set this up to start with.
Avg Age Per Sport
INT({FIXED [Sport]: AVG([Age])})
Avg Age Per Sport– Gold
INT({FIXED [Sport]: AVG(IF [Medal]=’Gold’ THEN [Age] END)})
Avg Age Per Sport– Silver
INT({FIXED [Sport]: AVG(IF [Medal]=’Silver’ THEN [Age] END)})
Avg Age Per Sport– Bronze
INT({FIXED [Sport]: AVG(IF [Medal]=’Bronze’ THEN [Age] END)})
Oldest Age Per Sport
INT({FIXED [Sport]: MAX([Age])})
Youngest Age Per Sport
INT({FIXED [Sport]: MIN([Age])})
Format all these fields to use Number Standard format so just whole numbers are displayed.
Let’s put all these out in a table – add Sport to Rows and then add all these measures
Kyle hinted that while the display looks like a trellis chart, it’s been built with multiple sheets – 1 per row. So we need to identify which row each set of sports is associated to, given there are 5 sports per row.
Row
INT((INDEX()-1)/5)
Make this discrete, and add this to Rows, and then adjust the table calculation so that it is explicitly computing by Sport, and we can see how each set of sports is ‘grouped’ on the same row.
Building the core viz
The viz is essentially a stacked bar chart of number of medallists by Age. But the bars are segmented by each individual medallist, and uniquely identified based on Name, Medal, Sport, Event, Year.
Add Sport to Columns, Age to Columns (as a continuous dimension – green pill) and Summer Olympic Medallists (Count) to Rows.
Change the mark type to Bar and reduce the Size a bit. Add Year, Event and Name to the Detail shelf, then add Medal to the Colour shelf and adjust accordingly. Re-order the colour legend, so it’s listed Bronze, Silver , Gold.
For the Tooltip we need to colour the type of medal, so need to create
Tooltip: Bronze
UPPER(IF [Medal] = ‘Bronze’ THEN [Medal] END)
Tooltip: Silver
UPPER(IF [Medal] = ‘Silver’ THEN [Medal] END)
Tooltip: Gold
UPPER(IF [Medal] = ‘Gold’ THEN [Medal] END)
Add all these to the Tooltip shelf, then adjust the Tooltip accordingly, referencing the 3 fields above and colouring the text.
The summary information for each sport displayed above each bar chart, is simply utilising the ‘header’ section of the display. We need to craft dedicated fields to display the text we need (note the spaces)
Heading – Medal Age
“Avg Medalling Age: ” + STR([Avg Age Per Sport])
Heading: Age per Medal
“Gold: ” + STR(IFNULL([Avg Age Per Sport – Gold],0)) + ” Silver: ” + STR(IFNULL([Avg Age Per Sport – Silver],0)) + ” Bronze: ” + STR(IFNULL([Avg Age Per Sport – Bronze],0))
Heading: Young | Old
“Youngest: ” + STR([Youngest Age Per Sport]) + ” Oldest: ” + STR([Oldest Age Per Sport])
Add each of these to Rows in the relevant order. Widen each column and reduce the height of each header row if need be
Adjust the font style of all the 4 fields in the header. I used
Sport – Tableau Medium 14pt, black, bold
Heading – Medal Age – Tableau Book 12pt, black, bold
Heading: Age per Medal – Tableau Book 10pt, black bold
Heading: Young | Old – Tableau Book 9pt, black
The title needs to reflect the number of medallists above a user defined age. To capture the age we need a parameter
pAge
integer parameter defaulted to 42
Then we can create
Total Athletes at Age+
{FIXED:COUNTD( IF [Age]>= [pAge] THEN [Name] END)}
Add this to the Detail shelf, then update the title of the viz to reference this field. Note the spacing to incorporate the positioning of the parameter later.
The chart also needs to show background banding based on the selected age. Add a reference line to the Age axis (right click axis > add reference line), which references the pAge parameter, and fills above the point with a grey colour
Ultimately this viz is going to be broken up into separate rows, but we need the Age axis to cover the same range. To ensure this happens, we need
Ref Line – Oldest Age
{FIXED: MAX([Oldest Age Per Sport])}
Add this to the Detail shelf, then add a reference line which references this field, but doesn’t actually ‘display’ anything (it’s invisible).
Tidy up the visual – remove gridlines, zero lines, row & column dividers. Hide the y-axis (uncheck show header). Remove the x-axis title. Hide the heading label (right click > hide field labels for columns). Hide the null indicator.
Filtering per row
Add the Row field to the Filter shelf and select the only option displayed (0). Then edit the table calculation and select all the fields to compute by, but set the level to Sport.
Show the Row filter and select the 0 option only. – only the first 5 sports are now displayed.
Name this sheet Row1. Then duplicate the sheet, set the Row filter to 1 and name the sheet Row2. Repeat so you have 7 sheets.
Building the dashboard
Use vertical containers to help position each of the 7 rows on the dashboard. For 6 of the sheets, the title needs to be hidden. I used a vertical container nested within another vertical container to contain the 6 sheets with no title. I then fixed the height of this ‘nested’ container and ‘distributed” the contents evenly. But I did have to do some maths based on the space the other objects (title & footer) used up on my dashboard, to work out what the height should be.
You’ll then need to float the pAge parameter onto your layout, and may find that after publishing to Tableau Public, you’ll need to edit online to tweak the positioning further.
This week’s challenge focuses on allowing the user flexibility in both how they want to ‘slice’ the measure being reported (actual headcount), and how they want the results displayed to meet the needs of the question they are trying to answer. Whilst the above could be built using multiple sheets and ‘sheet swapping’ using dynamic zone visibility, the aim is to try to build within a single sheet.
Let’s get cracking.
Enhancing the data
Not all the fields we need to slice the data by are included initially in the data provided, so we need to define some additional calculated fields, as stated in the requirements.
Contract Type
IF [FTE] = 1 THEN ‘Full Time’ ELSE ‘Part Time’ END
To determine the Age Bracket an employee belongs to, we first need to know their age. Usually this would be based on today’s date (using the TODAY() function), but as this data is static, we’re going to pretend today is 01 Dec 2022, by capturing this in a parameter.
pToday
date parameter defaulted to 01 Dec 2022
We can then work out how old the employee is (in complete years) on 01 Dec 2022
Employee Age
IF DATEPART(‘dayofyear’, [Birth Date])<=DATEPART(‘dayofyear’,[pToday]) THEN DATEDIFF(‘year’, [Birth Date], [pToday]) ELSE DATEDIFF(‘year’, [Birth Date], [pToday])-1 END
The ‘dayofyear’ parameter of the DATEPART function returns as it suggests the day of the year, so is a number from 1 to 365 (or 366 in a leap year). So this calculation is basically saying, if the birthday falls before ‘Today’, then they’ve had their birthday in that year, so a simple difference between the years suffices, otherwise the birthday hasn’t happened yet, so take one off the difference in years.
We can then create
Age Bracket
IF [Employee Age] < 20 THEN ‘Under 20’ ELSEIF [Employee Age] < 30 THEN ’20-29′ ELSEIF [Employee Age] < 40 THEN ’30-39′ ELSEIF [Employee Age] < 50 THEN ’40-49′ ELSEIF [Employee Age] < 60 THEN ’50-59′ ELSE ‘Over 60’ END
Setting up the parameters
All the user interactivity is driven through selection of parameters.
pDisplayType
string parameter defaulted to Butterfly Chart with a list of values Stacked, Side by Side, Butterfly which each have a slightly different Display As value (up to you whether you want to do this).
pDisplayRowsBy
string parameter defaulted to Age Bracket with required options listed.
pSplitBarsBar
string parameter defaulted to Contract Type with required options listed
Building the Viz
The pDisplayRowsBy parameter defines the field that we want to display on the Rows shelf. The parameter is just a string value though which we need to map to a proper field from the data set
Dimension – Rows
CASE [pDisplayRowsBy] WHEN ‘Age Bracket’ THEN [Age Bracket] WHEN ‘Contract Type’ THEN [Contract Type] WHEN ‘Department’ THEN [Department] WHEN ‘Gender’ THEN [Gender] WHEN ‘Nationality’ THEN [Nationality] END
The pSplitBarsBy parameter defines the field that will be used to colour the bars. Again we need to map this to fields in the data set
Dimension – Colour
CASE [pSplitBarsBy] WHEN ‘Gender’ THEN [Gender] WHEN ‘Contract Type’ THEN [Contract Type] END
The existing Employees(Count) field that is automatically included is just a counter for the number of rows in the data set, so is essentially our headcount number. However, we need to think about how the display needs to look for the different types of chart, specially the butterfly chart.
When building a butterfly chart, the bars to the right of the middle axis are plotted as positive numbers, while the bars to the left are plotted as negative numbers (so the middle is actually 0). So in the event the display type of Butterfly Chart is selected, we need to make some adjustments, so that some of the values are plotted on the negative axis. This means our calculation looks like
Headcount
IF [pDisplayType] = ‘Butterfly’ THEN IF MIN([Dimension – Colour]) IN (‘Part Time’, ‘Female’) THEN COUNT([Employees]) * -1 ELSE COUNT([Employees]) END ELSE COUNT([Employees]) END
If it’s a Butterfly chart, then if the values associated to the field we’re colouring the bars by are ‘Part Time’ (pSplitBarsBy = Contract Type) or ‘Female’ (pSplitBarsBy = Gender), then negate the count of employees, otherwise just return the count of employees.
Let’s start putting some of this into a viz to see what we’ve got…
On a sheet, add Dimension-Rows to Rows and Headcount to Columns. Add Dimension-Colour to Colour. Show the 3 core parameters and adjust the colours to suit.
Change the pSplitBarsBy to Gender, and adjust colours again. Change to ‘N/A’ and adjust again, so all possible options for the Dimension-Colour field have been set. Change pSplitBarsBy back to Contract Type.
Now change pDisplayType to Stacked Bar.
We want Full Time segment of the bar to appear first against the zero line of the axis, so manually re-order the options in the colour legend, so Part Time is listed first.
Change pSplitBarsBy to Gender and verify that the Male segment of the bars are displaying first against the zero line of the axis. Change pSplitBarsBy back to Contract Type.
For the side by side bar chart, we need another dimension on Rows that is essentially the Dimension -Colour field. However, we only want the value when the pDisplayType is Side by Side. So we need
Dimension – Side by Side
IF [pDisplayType]=’Side By Side’ THEN [Dimension – Colour] ELSE ” END
Add this to Rows after the Dimension-Rows field.
This adds an additional column to the display. While we’re in side by side or butterfly ‘mode’ , nothing is displayed. Change pDisplayType to Side by Side and we get the separation required.
Since the Colour legend is defining what the bar relates to, we can hide this field (right click on the Dimension – Side by Side pill and uncheck show header.
We want to also show the % of Total headcount split for each row. Create a new field
Total Employees Per Row
{FIXED [Dimension – Rows] : COUNT([Employees])}
then create
% Total Headcount
[Headcount]/SUM([Total Employees Per Row])
and format to a custom number format of 0%;0%. This is a % with 0 decimal places, but the custom format is used as we want negative numbers (caused by the butterfly chart type) to be displayed as +ve percentages.
Add % Total Headcount to Columns and verify the display for the different display types. Hint – if the calc is working as expected, the Stack Bar option should show 100% 🙂
Labelling the Viz
We can’t simply add labels using the usual method (show mark labels) and adjusting the positioning, as it won’t always show what we want, where we want for all the different display types. So we need to employ different techniques based on the chart being displayed.
Let’s start with the stacked bar chart. For the Headcount bars, we want to display the total number of employees at the end of the bar, and for the % Total Headcount, we want the % displayed in the middle of the bar.
Create a new field
Ref Line – Stacked
IF [pDisplayType] = ‘Stacked’ THEN [Headcount] END
Add this to Detail shelf of the Headcount marks card, then add a reference line to the Headcount axis (right click axis > Add Reference Line) that for each pane references the Sum of the Ref Line – Stacked field, and just displays the Value on the Label. No line or tooltip is displayed.
Format the reference line (right click the ‘invisible’ line) to adjust the colour and alignment of the font.
Add % Total Headcount to the Label shelf of the % Total Headcount marks card. You may need to adjust the width of the bars so you can see the values. Adjust the font to be white.
Change the pDisplayType parameter to Side by Side. No labels should be displayed. This is because the label we used for the headcount bar was based on the display type, and the label we used for the % total has been coloured white and is therefore invisible on a white background (set it to black and you’ll see it displayed). So again we need more fields
Ref Line – Side By Side
IF [pDisplayType] = ‘Side By Side’ THEN [Headcount] END
Add this to the Detail shelf of the Headcount marks card, and again add a reference line to the Headcount axis, this time setting the scope at the cell level.
Once again format the reference line to set the font and alignment.
Create
Label – % of Total Side by Side
IF [pDisplayType] = ‘Side By Side’ THEN [% Total Headcount] END
Format to a % with 0 dp and add this to the Label shelf of the % Total Headcount marks card, and then adjust the Label so this field is appropriately coloured and listed before the other label (which is coloured white).
Change the pDisplayType parameter to Butterfly chart. The % labels should already display. We just need to sort the labelling for the actual headcount. For this we need
Ref Line – Butterfly Pos
IF [pDisplayType] = ‘Butterfly’ THEN IF [Headcount] >= 0 THEN [Headcount] END END
and
Ref Line – Butterfly Neg
IF [pDisplayType] = ‘Butterfly’ THEN IF [Headcount] < 0 THEN [Headcount] END END
Add both these fields to the Detail shelf of the Headcount marks card. Then add reference lines for each of the two fields
Then format both the reference lines so the font and alignment is set as required (in the case of the Ref Line – Butterfly Neg reference line, it needs to be left aligned)
Add Headcount and % Total Headcount to the Tooltip shelf of the All marks card, and then adjust the tooltip. Finally tidy up by
Adjusting format of the Dimension – Rows values and aligning middle left.
Hide field labels for rows
Remove column dividers
Adjust row dividers to be more subtle
Remove gridlines, zero lines, axis ticks & rulers
Remove the Headcount & % Total Headcount axis.
Add the viz to a dashboard. I used a horizontal container placed above the viz to add text objects to represent the headings. The first text box just the referenced the pDisplayRowsBy parameter value, so it was dynamic.
For this week’s challenge, Kyle aimed to solve a problem that he discussed with a #TC24 attendee while waiting in line for a session. How to show solid (categorical) and graduating (sequential) colours within the same chart.
For this we’re going to create 2 sheets, one displaying Sales and one for Profit Ratio.
Data Preparation
As the requirements referenced the use of the Manufacturer field, I connected to the Superstore Sales.tds file I had stored locally, rather than the .xls file, as Manufacturer doesn’t exist in the xls file.
Once connected, I also added a data source filter where Sub-Category = Storage (right click on data source > edit data source filter).
Building the Sales bar chart
Add Manufacturer to Rows and Sales to Columns and sort descending. Format Sales to be $ with 0 dp, and show mark labels.
Create a new field
Colour – Sales Bracket
IF SUM([Sales]) > 15000 THEN ‘>$15k’ ELSEIF SUM(Sales) >= 4000 THEN ‘$4k-$15k’ ELSE ‘<$4k’ END
Then create another field
Colour – Sales Range
IF SUM([Sales])<4000 THEN SUM([Sales])*-1 END
By default, this will be a continuous field as it returns a numeric value. But when having multiple fields on the Colour shelf, which is what we’re going to do, the fields need to be discrete. So convert Colour – Sales Range to discrete (right click on field).
You also might be wondering why we’re multiplying the value by -1. This is to ensure the values when listed in the colour legend are sorted in the way we want. This will become clearer shortly.
Add Colour – Sales Range to the Detail shelf, then click on the icon to the left of the pill, and select the Colour icon, to add this pill to the Colour shelf along with the Colour – Sales Bracket field.
Re-order the pills so Colour – Sales Range is listed first.
Your viz will look something like this
To change the colours without having to go through each legend option individually, edit the colour legend and choose a sequential colour palette that suits. In my case I used a custom one I had installed, but you could just opt for the Red-Gold that should be installed by default.
Click the Assign Palette button, and Tableau will automatically assign the colours in the graduated sequence, which is why the ordering of the entries matters. The colour legend lists the entries in ascending order, and as we want to display the values in descending order, multiplying by -1 reverses the order for the colour legend.
Manually set colours for the NULL, >$15k and NULL, $4k-$15k options. Add a pale grey border around the marks (via the Colour shelf). Then adjust the tooltip, remove the Manufacturer column heading label, hide the axis and remove all gridlines, zero lines, axis rulers, row/column dividers, and update the title.
Note – I chose to re-order how the two colour pills were listed on the Colour shelf so that there was a more noticeable difference between the colour chosen for the $4k-$15k range vs the first entry for the <$4k range.
Building the Profit Ratio bar chart
For this you will need similar fields
Colour – PR Bracket
IF [Profit Ratio] > 0.15 THEN ‘>15%’ ELSEIF [Profit Ratio] >= 0 THEN ‘0-15%’ ELSE ‘<0%’ END
and
Colour – PR Range
IF [Profit Ratio]<0 THEN [Profit Ratio]*-1 END
and Profit Ratio should be formatted to % with 1 dp.
You then just need to go through similar steps to that described above.
Building the dashboard
I used a horizontal container to position the two sheets in, side-by side. I set the outer padding of each sheet to 0 and inner padding to 5 and set to fit entire view. Between the sheets I added a blank object which I set to have outer padding of 0. I set the background colour of this blank object to a mid grey, then set the width to be 2, which produces the thin divider line.
To enable the highlighting between the sheets ‘on click’ I simply selected all fields from the highlight menu button.
A relatively short blog this week, but a great concept that’s worth knowing. My published viz is here.
My #WOW2024 challenge this week, was to recreate this bar chart which displayed the total value in conjunction with the values split by another dimension, in this instance year. It was inspired by Sam Parsons’film franchises viz.
The viz is built on a single sheet, and only uses 3 fields from the data set, the Order Date the Sub-Category and the Sales value.
Let’s build…
On a new sheet, add Sub-Category to Rows and Sales to Columns and sort by Sales descending. Add Order Date to Colour to build a basic stacked bar chart.
We need to be able to show two bars directly ‘side by side’, but with different properties, so we need to end up with 2 marks cards. The ‘heights’ of the bars need to differ, so we’re going to create a ‘fake axis’ on Rows to help.
Double click into the Rows shelf and manually type MIN(0.5)
Change the mark type to bar. Each bar is now positioned based on it’s specific value (ie not stacked).
To get the bars to be positioned where we want them, apply a Running Total Quick Table Calculation to the Sales pill in the Columns shelf. Then edit the table calculation so that is computes by Year of Order Date only.
Add Sales to the Size shelf, and edit the size to be Fixed and aligned Right. You should now have a stacked bar effect.
Add Sales to Tooltip, then add a Percentage of Total quick table calculation. Edit the calculation so it is computing by Year of Order Date.
Adjust the Tooltip to reference the relevant details (note in the above gif, and my original published version, at some point the labelling went awry and the label was showing the cumulative sales value, and not the value for that year).
So now we’ve managed to build a segmented bar chart in a ‘different way’ by using an axis and the size field. We build on this to get the total bar. As this bar needs to be wider than the segmented bar, double click into the Rows shelf and type MIN(1.5), then from the MIN(1.5) marks card, remove the Order Date field from Colour. We now have 2 bars – 1 segmented and 1 not.
Make the chart dual axis and synchronise the axis, then right click on the right hand axis and move marks to back.
On the Min(0.5) marks card, move Order Date from Colour to Detail. Then on the All marks card, click on the Colour shelf and adjust the colour to suit (I set to #dec7b6) and add a white border.
Adjust the Tooltips on both marks cards so the MIN(1.5) is just showing the Sub-Category and Sales value, while the MIN(0.5) card is showing the Order Date year and % of sales value as well.
Narrow the width of the rows. Hide the right hand and the bottom axis (uncheck show header).
We want to make each pair of bars more distinctive, by providing more space between them. Edit the axis to fix the axis from -0.3 to 1.5.
Now hide the axis, and the Sub-Category column heading (right click label and hide field label for rows). Remove all row/column divider lines, zero lines and gridlines. Set the worksheet background colour (I used #fefaf1). Adjust the colour/style of the Sub-Category labels (I used bold, brown) and align top right.
And ta-dah! this should be your finished viz. My published version is here.
We’re going to building on this in a later challenge (I felt putting it all together in a single challenge might be a bit much), so look out for Part 2 in a few weeks time.
This week, Kyle set the challenge of recreating area charts combined with a tile map / small multiple. He’d been inspired by a viz he uses at work, which he then realised had actually been inspired by a previous #WorkoutWednesday challenge from 2020 which I actually blogged about here.
So some of the steps for this solution guide, I’ll lift from my existing blog :-), but there are no table calcs in this instance.
Building the basic tile map
As per Kyle’s instructions, I started by building a new field that I could then format to millions
Pop
[Population (Population)] * 1000
I formatted this to be a custom number with 2 dp and displayed with Millions unit.
Put this into a table where State and Date on Rows and Pop on Text, just so we can validate what we’re up to…
We need to standardise/normalise the display, so the population for each State is ranging from 0 to 1. For this we need to determine
Min Pop Per State
{FIXED [State]:MIN([Pop])}
Max Pop Per State
{FIXED [State]:MAX([Pop])}
and we can then work out
Standardised Pop
(SUM([Pop]) – SUM([Min Pop per State]))/(SUM([Max Pop per State]) – SUM([Min Pop per State]))
Format this to display as a number with 2 dp. Add all 3 fields to the table. For each state the Standardised Pop should have a year when then value is 0 (equivalent to when the population is lowest for the state) and a year when the value is 1 (equivalent to when the population is highest for the state).
For the tile map, we also need fields Rows and Cols which are calculations that map each State to a number (and can be copied straight from the challenge sheet).
On a new sheet, add Rows to Rows (as a discrete dimension) and Cols to Columns (also as a discrete dimension). Add State to Detail. Your initial ‘map’ layout should start to take shape.
Add Date to Columns and set to be a the continuous Year level (green pill) and add Standardised Pop to Rows. Set the display to Entire View, so all the tiles are visible.
Change mark type to Area and add Pop to the Tooltip. Update the Tooltip as required.
Adding the State label and max value
For this we’re going to plot a single point that will be at the centre of the Date axis and slightly higher than 1. For this we need
This looks a bit complex, so I’ll break it down. What we’re doing is finding the number of years between the minimum year in the whole data set (1900) and the maximum year in the data set (2023). This is
We’re then halving this value ( /2) and rounding it down to the nearest whole number (FLOOR).
We then add this number of years to the minimum date in the data set (DATEADD), to get our central year – 1961.
Now we need a point where we can plot a mark against that date. It needs to be above the maximum value in the area chart (which is 1). Based on what I did before, I decided 1.75 worked
Plot State Label
IF YEAR([Date]) = YEAR([Centre Date]) THEN 1.75 END
Add Plot State Label to Rows between Rows and Standardised Pop. Change the mark type of this axis to shape and use a transparent shape (see here for info). Note you can use a circle and reduce size to smallest and opacity to 0% if you wish. However, this will show a small dot when hovering, which you don’t get with a transparent shape.
Add Max Pop per State to Label and change State from Detail to Label. Adjust the Label accordingly
Remove all text from the Tooltip dialog of the Plot State Label marks card, and hide the Nulls indicator label. Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card.
Then remove all gridlines, zero lines, axis lines and row/column dividers. Hide all the axis and the Cols and Rows pills (uncheck show header).
Building the bar chart
Create a new field
Latest Pop per State
IF [Date] = {FIXED [State]:MAX([Date])} THEN [Pop] END
If the date is the maximum date for the state, then get the population. Format this to be 2dp in Millions.
Add State to Rows and Latest Pop for State to Columns and sort descending. Adjust the colour to suit. Show mark labels, and remove all gridlines etc and row/column dividers and hide the axis. Adjust the Tooltip.
Creating the highlight action
Add the two sheets to a dashboard. Add a dashboard highlight action
Highlight State
On hover of the bar chart, target the map, via the State field only.
It was my turn this week to set the challenge which I based on an example of a viz that I’d seen at work.
I built the viz using 3 sheets and using techniques similar to that described in a previous challenge documented here where I used parameter actions. It’s likely set actions can be used too, although I haven’t tested this out.
Building the bar chart
Format Sales to $ with 0dp, then add State/Province to Rows and Sales to Columns and sort descending. Add State/Province to Filter and filter by Top 5 based on Sales.
Double click on the Columns shelf and manually type MIN(0) to create a second axis. Make the chart dual axis and synchronise the axis. Change the mark type of the Sales marks card to Bar and the mark type of the MIN(0) axis to Gantt. Add Measure Names to Rows.
Remove Measure Names from the Colour shelf of both marks cards. Then add State/Province and Sales the Label shelf of the MIN(0) marks card. Arrange the text to match.
Reduce the size of the MIN(0) gantt bar to as small as possible, and reduce the opacity of the mark (via the Colour shelf) to 0%.
Update the Tooltip on the Sales marks card to match, but delete all text from the Tooltip of the MIN(0) marks card.
Add State/Province to the Colour shelf of the Sales marks card, and adjust accordingly. I chose to set the opacity to 50% based on the colours I chose.
Hide both axis, and both row heading (uncheck show header). Remove all gridlines, row/column dividers, axis ruler etc.
Name the sheet Bar or similar. We’ll come back to this later.
Building the trend line
On a new sheet, add Order Date to Columns set to a continuous monthly level (green pill) and Sales to Rows. Add State/Province to Colour (and reduce opacity if need be).
Go back to the bar chart sheet, and apply the State/Province filter to the trend line sheet too (right click on the pill in the filter shelf > apply to worksheets > selected worksheets).
Add a Running Total quick table calculation to the Sales pill (right click > quick table calculation > running total).
Add State/Province to the Label shelf, and set the font of the label to match mark colour. Adjust the text in the Tooltip. Format the Running Total Sales pill on the Rows shelf to be in $k with 0 dp. Delete the titles of both axis, and reduce the font size on the axis. Remove axis rulers/tick marks, zero lines, row/column dividers. Name the sheet Trend or similar.
Now, we need to be able to capture which States are being excluded. We need a parameter for this.
pUnselectedStates
string parameter defaulted to empty string/nothing
Show the parameter and manually type ‘Texas’ into the field. Based on what is in this parameter, will determine whether the state is unselected or not.
State is unselected
CONTAINS([pUnSelectedStates], [State/Province])
This returns True if the associated State/Province is listed in the parameter (so shouldn’t be visible) and False if it’s not listed (so should display).
Add State is unselected to the Filter shelf and set to False. The line for Texas will now disappear
Experiment by typing multiple states into the parameter eg Texas California
Building the selection control
On a new sheet add State/Province to Rows and sort by the Sales descending
Set the State/Province filter from another sheet to also apply to this sheet.
Double click into Columns and type MIN(0). Add State/Province to Colour (and apply opacity if required – I set this to 75%). Set the mark type to Shape and add State is unselected to shape. Use the Ratings shape palette to set the options.
We want the Tooltip to say something different, depending on whether the State/Province is selected or not.
Tooltip Include/Exclude
IF [State is unselected] THEN ‘include’ ELSE ‘exclude’ END
Add this to the Tooltip shelf, then update the text
We want to align this sheet with the bar chart, so we need to add another instance of MIN(0) to Columns. Make the chart dual axis and synchronise the axis. Remove Measure Names from the Colour shelf of both marks cards. Add Measure Names to the Rows shelf.
On the first MIN(0) marks card, remove all the fields from the card, reduce the size to as small as possible, and the opacity to 0%. Set the Shape to be a transparent shape (see this post for more details).
Now, we need a way to populate the pUnSelectedStates parameter with values from the selector sheet itself. Given the set of ‘unselected’ states can ‘build’ up, we can’t just pass in a single state from the State/Province field itself. We need to capture a delimited list of the states as they are unselected & reselected. We will use the | character as the delimiter, and will modify the string as follows
Action
pUnSelectedStates
All states selected
<empty string>
1 state unselected eg Texas
|Texas|
2 states unselected eg Texas then California
|Texas||California|
1 state then reselected eg Texas then California unselected, Texas reselected
|California|
1 state reselected again eg Texas then California unselected, Texas reselected, then California reselected
<empty string>
This is captured within this logic
States for Param
IF CONTAINS([pUnSelectedStates], [State/Province]) THEN //state already in parameter, so remove it REPLACE([pUnSelectedStates],’|’ + [State/Province]+ ‘|’,”) ELSE //add state to string [pUnSelectedStates] + ‘|’ + [State/Province] + ‘|’ END
If the State is already stored in the parameter, then remove it, by replacing the text with ” (empty string), otherwise append the state to the existing text in the parameter.
Add this field to the Detail shelf of the first MIN(0) marks card.
Hide all the axis and the row headings. Remove all row/column dividers, gridlines, zero lines, axis rulers etc. Reset the parameter back to an empty string.
Name the sheet Selector or similar.
Identifying if the bars are selected or not
Before we build the dashboard, we can now tweak the bar chart, so the colour of the bars reflects whether the State/Province is selected or not.
Back onto the bar chart sheet, add State is unselected to the Detail shelf of the Sales marks card. Then use the icon next to the pill and change it from Detail to Colour to add a secondary pill to the Colour shelf
Swap the pills around, so the State is unselected pill is above the State/Province pill, and the colour legend should list entries as False, <State>. Readjust the colours if need be.
Now enter ‘Texas’ into the pUnselectedStates parameter. The legend will update so that True,Texas is now listed. Adjust the colour of this option to white, and set a light grey border (via the Colour shelf), so the outline of the bar is visible.
Go through each listed State/Province adding it to the parameter and the adjusting the True, < State> colour legend to white each time.
Once done, set the pUnselectedStates parameter back to empty string.
Building the dashboard and adding the interactivity
Using containers, add the objects to a dashboard. I used a horizontal container to store the 3 charts, but the selector and the bar chart were also then contained within their own horizontal container. All charts were set to fit entire view. This allows for a border to be set around both objects. If everything is set right, you should be able to get the selector circles aligned with the bars.
I also used background colours and padding to get everything displayed as required.
Add a dashboard parameter action to capture the user interactivity
Unselect State
on select of the Selector sheet, update the pUnselectedStates parameter by passing the value of the States for Param field. Keep current value when unselected.
Finally to prevent the selected option from remaining ‘highlighted’ and all the other options becoming ‘faded’ out’, we can use the True/False trick described here.
It was Kyle’s turn to set the challenge this week, and took inspiration from a work based scenario he had encountered. As a result 3 fictitious data sets were provided, and a viz was required to be built against each of them.
Building the Marketing Campaigns Gantt chart
All 3 vizzes are tied together by 2 parameters – the highlight date and number of days, so lets’ start with defining those
pHighlightDate
date parameter defaulted to 01 Dec 2023
and
pDays
integer parameter defaulted to 120
On a new sheet, show both of these parameters.
Using the Campaign data source, add Campaign to Rows and Start Date to Columns as a continuous exact date (green pill).
To define the width of each mark, we need
Duration
DATEDIFF(‘day’, [Start Date], [End Date])
Add this to the Size shelf, then add Category to the Colour shelf and adjust accordingly.
Add Start Date as a discrete exact date (blue pill) to Rows and position in front of the Campaign pill. This will order each row based on the Start Date ascending.
Hide the Start Date pill on Rows (uncheck Show Header). Add End Date as a continuous attribute to Tooltip, and amend the tooltip as required.
Add pHighlightDate to Detail, then right click on the Start Date axis and Add Reference Line. Add a dotted line for the entire table based on the pHighlightDate field.
Remove all row and column dividers; delete the axis title (right click > edit axis) and hide the Campaign column heading (right click > hide field labels for rows). Format the Campaign numbers so they are aligned right.
Update the title of the sheet, and include the details for the legend title within
Finally, we need to filter the rows shown based on whether the campaign was running within the window based on the number of days before/after the highlight date. We need
Add Campaigns to Include to the Filter shelf and set to True. Test the viz changes as the parameters are changed.
Building the Experiments Gantt Chart
This is built in very much the same way as the above using the Experiments data source instead. In this case, Start Time and Experiment ID will be on Rows, Start Time on Columns and Status on Colour,
A Duration field should be on Size, but the calculation needs to be slightly different to handle those records where there is a start but not an end. In this case, we assume the experiment is still ongoing, so set the end to ‘today’.
should be added to the Filter shelf, and set to True – at this point all the experiments that didn’t have a start date either will disappear. Add a title and the reference line and format as before
Building the Emails bar chart
On a new sheet, using the CRM data source, add Date as a continuous exact date (green pill) to Columns and Sent to Rows. Change the mark type to bar chart. Click on the Size button, and change to be Fixed.
Add Email Type to Colour and adjust. Update the Tooltip.
Create fields Window Start and Window End as before, then create
Emails to Include
[Date] > [Window Start] AND [Date] < [Window End]
and add to the Filter shelf, set to True.
Add pHighlightDate to the Detail shelf, and add a reference line as before.
Remove all row/column dividers, gridlines & axis lines; delete the axis titles; add a sheet title including the legends.
The add all 3 sheets to a dashboard – I placed them all in their own vertical container, so I could then distribute contents evenly.
For the penultimate challenge of 2023, Erica set this fun Christmas themed challenge to visualise the toy production in Santa’s workshop. It was a collaboration with the #PreppinData crew, where you were encouraged to complete their challenge to prep the data for this one. I did do that, but to ensure no discrepancies or field name differences, I used the outputs from the challenge itself as the source for my viz.
Building the Line Chart
This needs to show the quota vs the cumulative number of toys produced for each production manager/toy and uses the data from the Output 1 of the Prep challenge.
Add Week to Columns and change to exact date. Format the Week pill on the Columns to show as custom format yyyy on the axis
then edit the axis and set the tick marks to be fixed from 01 Jan 2023 with an interval of 1 year. This will result in just 2 axis labels displayed, one for 2023 and one for 2024
Add Production Manager and Toy to Rows and then add Quota to Rows too. Then drag Toys Produced onto the Quota axis and drop it when the double green column icon appears.
This will convert the viz to have Measure Values on the Rows instead, and the Quota and Toys Produced pills sitting in the Measure Values section on the left.
Add a Running Total quick table calculation against the Toys Produced pill. Then edit the Value axis, so that the axis are independent axis ranges for each row & column.
The colour of the running total line needs to change based on whether the overall value is above or below the quota. Erica asked us not to use LODs in this challenge, so to determine this, we need
Colour – Over | Under
IF WINDOW_MAX(RUNNING_SUM(SUM([Toys Produced]))) > WINDOW_MAX(SUM([Quota])) THEN ‘Over’ ELSE ‘Under’ END
The WINDOW_MAX function is taking the highest value of the measure and essentially ‘spreads’ that across every row of data being plotted (in this case every week).
Add this field to the Detail shelf and then click on the 3 dot symbol to the left of the pill and change it to the Colour symbol. This allows multiple pills to be on the Colour shelf – Measure Names and Colour – Over | Under, resulting in 4 different colours in the colour legend.
Adjust the legend colours, so the two relating to the Quota are the same colour and the others coloured based on whether the value is Over or Under.
On the Label shelf, check the show mark labels option, and then select most recent. Adjust the font to be bold and match mark colour. Format both the pills sitting in the Measure Values section to be Millions with 1 dp.
Add Week to the Tooltip shelf and format to be in the <day of week>, <day> <month> <year> style. Adjust the tooltip accordingly.
Hide the Production Manager and Toy fields (uncheck show header). Edit the title of the Value axis and the Week axis. Remove all gridlines, zero lines, row & column dividers, but ensure the axis are displayed. Change the worksheet background colour.
Update the Title of the sheet to reference the Toy, then name the sheet Line or similar.
Building the KPIs
We’re still using the data from Output 1. We’re going to do this in 2 sheets, as we want to format the text of the PM name differently. To start, we need some additional calculated fields.
Rate of Production
AVG([Toys Produced])
Then we need to work out for those Production Managers who were under their quota, how far off they were and how long, based on their production rate, it would take for them to fulfil that difference. So first we need
Difference
AVG([Quota]) – SUM([Toys Produced])
This gives us how far under (or over) the PM was from their target quota.
We can then calculate
Weeks Needed to Meet Quota
IF MIN([Over or Under Quota?]) = ‘Over’ THEN 0 ELSE CEILING([Difference] /[Rate of Production]) END
If the PM has exceeded their quota, then 0, as there’s nothing to build, otherwise determine the number of whole weeks. The CEILING function ensures even if the result is only a fraction over a number, the result is ’rounded up’ the next whole number so 12.1 weeks and 12.9 weeks are both reported as 13 weeks.
Add Production Manager and the 2 fields above onto a new sheet and display in tabular form.
Set the sheet to Entire View and adjust the text to be larger (I used bold 18pt font). Format the column headings to be larger too (I used 12pt). Stop the tooltips from displaying, remove row/column dividers and row banding. Set the background colour of the worksheet and hide the Production Manager column (uncheck show header).
Name this sheet KPI or similar.
On a new sheet, add Production Manager to Rows and add Production Manager to the Text shelf too. Double click in to the Columns shelf and type ‘Production Manager’ to create a heading for the text column.
Set the sheet to Entire View, then adjust the font of the Text shelf. I chose a handwriting script font and set to 18pt and bold. The hide the Production Manager field on Rows, and hide the ‘Production Manager’ column label heading (right click – hide field labels for columns). Adjust the font of the column heading and remove all row/column dividers and row banding, Set the background colour. Hide the tooltip.
Name the sheet PM Name or similar.
Building the bar chart
For this, we’re now using the data from Output 2.
We’re plotting 2 measures for the bars – the amount under or over the quota which is a +ve (over) or -ve (under) number which will be plotted either side of a zero line as you would expect. The Toys Over/Under Quota field has this value.
We also need to plot the amount of toys produced, but while this is a positive number, it is displayed on the bar chart on the negative side of the zero line. So to enable this we need
Toys Produced to Plot
-1 * [Toys Produced]
ON a new sheet, add List and Toy to Rows. Then add Toys Produced to Plot to Columns, and then drag Toys Over/Under Quota onto the axis and drop when the 2 green column icon appears. This will result in the following display where Measure Names and Measure Values are automatically added.
Move Measure Names from Rows onto Colour, then change the order of the pills listed in the Measure Values section, so Toys Produced to Plot is listed first.
Create a new field
Colour – Over | Under
IF [Toys Over/Under Quota] < 0 THEN ‘under’ ELSE ‘over’ END
and add to the Detail shelf, then adjust the symbol to add this field to the Colour shelf as well to give you 4 colours on the legend. Adjust accordingly. Add Quota, Toys Produced and Toys Over/Under Quota to Tooltip and adjust.
For the label to display against each bar, we need to plot another measure, which is either 0 for those which were under production, or the value of the over production.
Label Value to Plot
IF SUM([Toys Over/Under Quota]) < 0 THEN 0 ELSE SUM([Toys Over/Under Quota]) END
Add this to Columns. On the Label Value to Plot marks card, change the mark type to circle and remove Measure Names from colour.
Create a new field
% Difference
SUM([Toys Over/Under Quota]) / SUM([Quota])
and apply a custom number format of 0%;0% which means -ve numbers will display as +ve.
Add this to the Label shelf along with Colour – Over | Under. Adjust the label text so the labels are displayed on a single line, are aligned middle right and the font matches mark colour and is bold. Make the chart dual axis and synchronise the axis (set the mark type of Measure Values to a bar if the display changes). On the Label Value to Plot marks card, reduce the opacity of the circle colour to 0% and reduce the size to the smallest possible. Remove all the text from the Tooltip.
To ensure the label text doesn’t overlap the bars, we can extend the axis by creating
Ref Line
WINDOW_MAX([Label Value to Plot]) *2
Add this to the Detail shelf of the Label Value to Plot marks card. Then right click on the top axis and Add Reference Line that refers to the maximum of the Ref Line field. Apply settings as below so the line is invisible.
Finally hide both axis, remove all gridlines, zero lines, axis and column dividers. Adjust the row dividers to be thick grey dashed lines. Update the title of the sheet.
Name the sheet Bar or similar.
Adding the interactivity
Using layout containers, add the sheets to a dashboard so they are arranged in the format required. Then add a dashboard filter action
Select PM
on select of the Bar sheet, target the KPI, Line and PM Name sheets. When the selection is cleared, keep filtered values Only allow 1 selection to be made at a time.
Click the bar against Barbie Doll to set the other charts to filter just to that toy, then unclick the bar again. The remaining charts should stay filtered.
And that should be it. Obviously you can add imagery as you wish but I didn’t go down that route – I just chose to set coloured borders on the layout containers.
Donna + DataViz 