This week’s challenge, set by Lorna, made use of the newly released ‘dotted line’ format in v2023.2, so you’ll need that version in order to achieve the dotted line that spans the 2 marks being compared. If you don’t have this version, you can still recreate the challenge, but you’ll just have a continuous line.
Building the calculations
This challenge involves table calculations, so my preferred startig point is to build out all the fields I need in a tabular format.
So start by adding Category to Columns and Order Date at the discrete (blue) week level to Rows. Add Sales to Text (I formatted Sales to $ with 0 dp, just for completeness).
Apply a Moving Average Quick Table Calculation to the Sales pill, then edit the table calculation to it is averaging over the previous 5 values (including the current value – ie 6 weeks in total), and it is computing by Order Date only.
Add another instance of Sales back into the table, so you can check the values.
The ‘moving average’ Sales pill is what will be used to plot the main line chart.
But we need to identify 2 points on the chart to compare – the values associated to a start date determined by the user clicking on the chart, and the values associated to an end date determined by the user hovering on the chart. We’ll make use of parameters to store these dates
pDateClick
date parameter defaulted to 27th Dec 2020
pDateHover
date parameter defaulted to 28 Nov 2011
We can then determine what the moving average Sales values were at these two dates
Sales to Compare
IF DATETRUNC(‘week’, MIN([Order Date])) = [pDateClick] OR DATETRUNC(‘week’, MIN([Order Date])) = [pDateHover] THEN WINDOW_AVG(SUM([Sales]), -5, 0) END
Add this to the table, and the values for the moving average will only be populated for the two dates stored in the parameters
This is the field we will use to plot the points to draw the lines with.
But we also need to work out the difference between these values so we can display the labels.
If the date matches the pDateHover (end) date then return the moving average and then spread that value over every row.
Add these into the table, and you can see how the table calculations are working
The moving avg value for the start date is displayed in every row against the Sales to Compare Start column, while the moving avg for the end date is displayed in every row for the Sales to Compare End column.
With these values now displayed on the same row, we can calculate
Difference
[Sales to Compare End]-[Sales to Compare Start]
formatted to $ with 0 dp
and
% Difference
[Difference]/[Sales to Compare Start]
formatted to a custom number format of ▲0.0%;▼0.0%;0.0%
Popping these into the tables, the values populate over every row, but when it comes to the label, we only want to show data against the comparison date, so I created
Label Difference
IF DATETRUNC(‘week’, MIN([Order Date])) = [pDateHover] THEN [Difference] END
formatted to $ with 0 dp, and
Label Difference %
IF DATETRUNC(‘week’, MIN([Order Date])) = [pDateHover] THEN [% Difference] END
formatted to a custom number format of ▲0.0%;▼0.0%;0.0%
With all these fields, we can now build the chart
Building the viz
On a new sheet, add Order Date set to the continuous (green) week level to Columns and Category to Rows. Add Sales to Rows and apply the moving average quick tablecalculation discussed above, adjusting so it is computing over 5 previous weeks and by Order Date only
Add Category to Colour and adjust accordingly, then reduce the opacity to around 40%
Add pDateClick to Detail then add a reference line to the Order Date axis the references this field. Adjust the Label and Tooltip fields, and set the line format to be a thick grey line.
Format the reference line text so it is aligned top right.
Then add pDateHover to Detail and add another reference line, but this time set the line so it is formatted as a dashed line.
Next, add Sales to Compare to Rows. Adjust the colour of the associated marks cards so it is 100% opacity.
Right click on the Sales to Compare pill and Format. On the Pane tab on the left hand side, set the Marks property of the Special Values section to Hide (Connect Lines). Click on the Path shelf and choose the dashed display option.
Set the chart to be Dual axis and synchronise the axis. Remove Measure Names from the All marks card. Hide the right hand axis.
Add Label Difference and Label Difference % to the Label shelf. Set the font to match mark colour and adjust font size and positioning. I left aligned the font but added a couple of spaces so it wasn’t as close to the line. I also added a couple of extra carriage returns so the text shifted up too.
Finally adjust tooltips, remove column dividers and hide the Category column title. Adjust the axis titles.
Adding the interactivity
Add the sheet onto a dashboard, then add 2 parameter actions
Set Start on Click
On select of the Viz sheet, pass the Order Date (week) into the pDateClick parameter. Leave the current value when selection is cleared (ie when user clicks elsewhere in viz, but not on another mark).
and
Set Comparison on Hover
On select of the Viz sheet, pass the Order Date (week) into the pDateHover parameter. Leave the current value when selection is cleared (ie when user clicks elsewhere in viz, but not on another mark).
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.
From interacting with Luke’s published solution, I figured I needed to start by working out some core calculated fields, before I even attempted to build the viz.
Defining the calculations
Firstly, we just need to determine how many distinct products (ie Product Names) in total were ordered per Category and Sub-Category. As the viz we’re building is already at the required level of detail, the calculation we need is simple
Count Products
COUNTD([Product Name])
That’s the easy bit :-)…. we then need to determine how many products are profitable (for the relevant Category & Sub-Category) every year, in each of the 4 years. This is a bit more complex, and requires several steps (at least it did for me).
We need to know what the Profit is for each Product Name in each year. We can use an LoD for this.
Popping this into a table view so we can check what’s going on…
We now need an indicator to know whether each value is profitable (ie +ve) or not. I want to return a 1 if it is and 0/null if not, and then I want to be able to ‘sum up’ all the 1’s, so I can conclude if the total is 4, the product is profitable every year. I need another LoD for this
Is Profitable per Year?
{FIXED YEAR([Order Date]), [Product Name]: SUM(INT([Profit per Product & Year]>0))}
[Profit per Product & Year]>0 returns a true or false, and so when true and wrapped within an INT, will return 1. When this field is added to the above view, it is further aggregated by SUM.
At the Product Name & Year level, it just returns the 1’s, 0’s or <nothing> as expected
but when we remove Year from the table (which is necessary for the viz we’ll be building), this field aggregates….
…and scrolling down we’ll find some rows where the value is 4, which means the Product Name has been profitable for every year.
Using this information, we can then create a Set of these products – Right click Product Name > create > set
Profitable Products
contains the set of Product Names where SUM(Is Profitable per Year?) = 4
Now we can identify the profitable products, we need to count how many there are
Count Successful Products
COUNTD(IF ([Profitable Products]) THEN [Product Name] END)
If the Product Name is in the Profitable Products set, then capture the Product Name, and count the distinct number of them.
And now we can compute the percentage of successful products
Pct. Successful Products
ZN([Count Successful Products]/[Count Products])
ZN means I’ll get a 0 for those cases when there aren’t any successful products. Format this to % with 0 dp.
Let’s see all these values in a different table now to verify we’re getting what we expect :
Great! So this has given us the data we need to build the bar chart part of the viz, but what about the barbell? This is based on profit ratio and all we actually need for this is the standard profit ratio calculation
Profit Ratio
SUM([Profit])/SUM([Sales])
formatted to % with 1 dp.
Building the viz
On a new sheet create a basic bar chart with Category and Sub-Category on Rows and Pct. Successful Products on Columns. Sort descending, label the bars and colour accordingly.
Now add Profit Ratio to Columns, and on the Profit Ratio marks card only..
change mark type to circle
remove labels
add Profitable Products set to Colour and adjust
edit the alias of the values in the colour legend (right click > edit alias)
To create the bar between the circles, add another instance of Profit Ratio to Columns, and on this marks card…
change mark type to line
remove labels
add Profitable Products to Path
change colour to grey
Now right click on the 2nd instance of the Profit Ratio pill in the Columns shelf and select Dual Axis.
(note – if you lose your bars at this point, change the mark type of the Pct. Successful Products mark cards to bar).
Right click on the Profit Ratio axis at the top and synchronise axis, then right click again and move marks to back, then finally, right click again and uncheck show header.
And that’s the core of the viz really. It needs some final formatting to remove column headers, column gridlines and to add row banding. I also chose to make the tooltips more relevant, so in my solution there are some additional fields on the marks cards to provide the relevant details and commentary.
It was Sean Miller’s turn to set the challenge this week, where the primary focus was to find the highest number of consecutive months where the monthly sales value was higher than the previous month.
This was a table calculations based challenge, and I always tackle these by building out the data required in a tabular format. The challenge was also reminiscent of a previous challenge Sean has set, which I’ve blogged about here, and admit I used as a reference myself.
So let’s get started.
To start with, we need the month date, the Sub-Category, the Sales value and the difference in Sales from the previous month. For the month date, I like to define this explicitly
Order Date Month
DATE(DATETRUNC(‘month’,[Order Date]))
This aligns all Order Dates to the 1st of the relevant month.
Add Sales Category, Order Date Month (set to discrete exact date blue pill), and Sales into a view, then set a Quick Table Calculation of Difference on the Sales pill
Edit the table calculation to compute by Order Date Month only, so the previous calculation restarts at each Sub-Category.
Then drag this pill from the marks card into the left hand data pane to ‘bake’ the calculated field into the data model. Name the field Sales Diff. The re-add Sales back into the view too, so you can double check the figures.
Identify whether there is an increase with the field
Diff is +ve
IF [Sales Diff]>0 THEN 1 ELSE 0 END
Add this into the view too, and verify the calculation is computing by Order Date Month only again.
Now we need to work out if the row matches the previous value
Match Prev Value
LOOKUP([Diff Is +ve],-1) = [Diff Is +ve]
The LOOKUP is looking at the previous row (identified by the -1) and comparing to the current. If they match then it returns True else False.
Again add into the view, and again double check the table calc settings. In this case there is nested calculations so you need to double check the settings against each calc referenced in the drop down
Now we need to work out when there are consecutive increases, and how many of them there are
Increase Streak
IF (NOT([Match Prev Value])) AND ([Diff Is +ve] = 1) THEN 1 ELSEIF [Diff Is +ve] = 1 THEN ([Diff Is +ve]+PREVIOUS_VALUE([Diff Is +ve]))
END
If the current row has a +ve difference and the previous row wasn’t +ve, then we’re at the start of an increase streak, so set to 1. Else, if the current row has a +ve difference then we must be on a consecutive increase, so add to the previous row, and this becomes a recursive calculation, so builds up the values..
Add this onto the view, set the table calc settings, and you can see how this is working…
So now we’ve identified the streaks in each Sub-Category, we just want the maximum value.
Longest Streak
WINDOW_MAX([Increase Streak])
Add this and set the table calc setting again. You’ll see the max value is spread across every row per Sub-Category.
Finally we need to identify Sales values in the months when the streak is at its highest.
Sales of Month with Longest Streak
IF [Longest Streak]=[Increase Streak] THEN SUM([Sales]) END
Add this into the view again (don’t forget those table calc settings), and you’ll notice that for some Sub-Categorys there are multiple points with the same max streak
With all this we can now build the viz, which is relatively straight forward….
Add Order Date Month (exact date, continuous green pill) to Columns, Sub-Category to Rows and Sales to Rows. Edit the Sales axis to be independent, then change the line type of the Path to stepped
Add Sales of Month with Longest Streak to Rows and set to dual axis, and synchronise. Make sure the mark type of the 2nd axis is set to circle, and remove Measure Names from the colour shelf of both marks.
Manually set the colour of the line chart to grey. Add Longest Streak to the Colour shelf of the circle marks card. Adjust the colour to use the green palette, set to stepped of 5 value and ensure the range starts at 0 and ends at 5 (don’t forget to edit the table calc settings!).
Now add Longest Streak as a discrete blue pill to the view too.
This is all the core components. The last thing we need to do is sort the list. I wasn’t entirely sure how it had been sorted, apart from the largest Longest Streak at the top. I created a new field for this
Sort
[Longest Streak]*-1
and added this as a blue discrete pill in front of Sub-Category….
…, then hid the column.
Then just apply the tooltip and relevant formatting on the chart.
For the legend, I created a new field
Legend
CASE [Sub-Category] WHEN ‘Art’ THEN 0 WHEN ‘Chairs’ THEN 1 WHEN ‘Labels’ THEN 2 WHEN ‘Paper’ THEN 3 WHEN ‘Phones’ THEN 4 ELSE 5 END
and added this into a new sheet as below
The components then just need to be added to the dashboard. My published version is here.
Luke Stanke provided his last challenge of #WOW2020 for the year with this viz to utilise Measure Names & Measure Values. This plots Cost against Sales per Sub-Category (the circles), which are then bounded by a lozenge. The Sales circle and the lozenge are coloured based on profitability, and the lozenges are labelled by Profit, although the position of the label is also based on profitability.
Obviously as the title of the challenge suggests, using Measure Names & Measure Values is key to this. As usual I’ll try to just pull out the main points I think you might find most tricky.
Building the core chart
Aligning the Sub-Categories against a horizontal line
Colouring the circles & lozenge
Positioning the labels
Formatting the labels
Creating a border around the white Cost circle
Building the core chart
You’ll need to create a calculated field to store the cost
Cost
SUM([Sales]) – SUM([Profit])
Once done, then add Sub-Category to Rows and sort by descending by Profit. Add Measure Values to Columns and filter just to Sales and Cost. Change the mark type to circle.
Add a 2nd instance of Measure Values to Columns and change the mark type to Line. Add Measure Names to Path.
Then make this chart dual axis and synchronise the axis. This will give the base chart.
Aligning the Sub-Categories against a horizontal line
Whenever you add a blue discrete pill to the rows or columns, you’ll get a ‘header’ pane, which means the label for the pill is by default positioned in the centre of a row, as demonstrated below if I add row dividers to the chart
The requirement is to show the row label aligned with a horizontal line. To achieve this, we need to add a MIN(0) field the Rows. Adding this green pill, creates an axis which has a 0 horizontal 0 line, which is positioned exactly in alignment with the row label
Removing the row dividers, and uncheck Show Header on the MIN(0) pill, and the right aligning the row label, we get the desired result.
Colouring the circles & lozenge
The circles are either coloured white for Cost or dark red or green for Sales depending on the profitability of the Sub-Category.
So we need a new calculated field
Is Profitable?
SUM([Profit])>0
This returns true or false.
On the circles Measure Names mark card, add Is Profitable to the Colour shelf. If it’s not already there, then add Measure Names to the Detail shelf, then select the detail icon and choose the colour icon to also add Measure Names to the Colour shelf
This has the effect of giving you 4 colour combinations
which can then be edited to set the 2 combinations relating to Cost to white, Sales & True to dark green and Sales & False to dark red (at this point, you probably want to be setting the background colour of the whole worksheet to light grey (right click on sheet, format, select the Shading icon)
For the lozenge, you just need to add the Is Profitable field the the Colour shelf of the line Measure Namescard, and adjust the size of the to make it slightly bigger than the circles (you may need to move marks to back to get the lozenge to sit behind the circles).
Positioning the labels
So the tricky thing about the labels is where they are positioned – they are to the right for the profitable sub-categories, and to the left for the non-profitable sub-categories. When you add a label, you can’t provide that sort of conditional logic to control the positioning. However, we happen to have 2 sets of marks cards (as dual axis), and so can use different fields to apply the labelling.
Label Profit is +ve
IF [Is Profitable] THEN SUM(Profit) END
This will only return a value for profitable sub-categories, otherwise the field will store blank.
Label Profit is -ve
IF NOT([Is Profitable]) THEN SUM(Profit) END
Conversely, this will only return a value for the non-profitable sub-categories.
Add the Label Profit is +ve field to the Label shelf of the Circle marks card. Adjust the positioning to be middle right, and adjust the settings as below (tbh this was just a bit of trial and error to find this worked :-)). You may want to additionally add a few <spaces> to the start of the label text to add a bit of breathing room.
Then on the Line marks card, add the Label Profit is -ve field to the Label shelf, but this time choose to Label Line Ends, and select Label end of line. Note – you can try to apply the same settings as above, but I found for some reason it increased the size of the mark around the line end… weird…
Formatting the labels
Right click on the Label Profit is +ve/-ve fields created and custom format with
+”$”#,##0;-“$”#,##0
Creating a border around the white Cost circle
Finally, a sneaky requirement was to add a border around the white circle that represented Cost only. This implied that using the formatting options on the Colour shelf that allows a border to be added to the mark wasn’t going to work, as this will apply to all marks and not just to the Cost circle.
I handled this by creating a 2nd instance of the Cost field (right click Cost and select duplicate).
I then added this Cost (copy) field to the Measure Values section of the chart on the left hand side. This doesn’t appear to do much, since the mark is directly on top of an existing mark, but you will see the Colour legend associated to the Circle marks card change and add 2 new entries.
I changed the colours of the 2 new entries in the Colour legend to a dark grey, then added Measure Names to the Size shelf of the Circles marks card. I then adjusted the range and size of the circles so that the Cost measure is slightly smaller than the Cost (copy) measure. Ultimately all 3 circles are slightly different sizes, but the range is so narrow, it’s hard to tell.
And that’s it. A lovely looking viz with a fair bit going on under the bonnet, but nothing too complex! My published version is here.
At first glance it looked like it would be tricky, and it was! There were many head-scratching moments as I made my way through this, and I couldn’t complete it all without having to look at Ivett’s solution – more on that later.
Understanding the data
First up, a word on the data provided, as the requirements weren’t as clear as I’d have liked.
Ivett provided a small custom data set to build this challenge on
It contains 2 rows for each Sub-Category. The value to be plotted for Review is evident on the challenge – it’s an average. But the aggregation for Revenue & Budget isn’t that obvious, as there is no direct indication on a label or in the tooltip. Should the values be summed or averaged, or a combination of both?
The Profit value in the tooltip is the only clue you have. The Profit value for Tables is -$60, the value for Chairs is $50. From examining the values in the data, the assumption is Profit = AVG(Revenue) – AVG(Budget), so the values to plot for Budget and Revenue need to be averaged.
The assumption is this data set is a combination of a two datasets; one containing the Revenue & Budget per Sub-Category
the other containing the customer reviews
So when it comes to the viz, we’re looking to plot based on the following data
Understanding what numbers I need to be aiming for when doing these challenges is crucial to me to ensure I’m heading down the right path 🙂
Duplicating the Data
The key thing to realise about this chart is that the right hand line is a fake axis; ie not an axis at all, just a vertical line located so that it looks like it is an axis.
What we actually need to do is build a triangle for each Sub-Category, where each point of the triangle is a different (x,y) coordinate. This means we need 3 rows of data per Sub-Category, 1 row to represent each point being plotted.
So for this we need to Union the data twice, so there are 3 instances of the data – drag another instance of the table into the data pane and drop when the Union option appears. Do this twice.
The union duplicates the rows of data, and each set is identifiable by an automatically generated Table Name field containing values WW, WW1,WW2
Defining the points of the triangle
As I said above, we need to build a triangle for each Sub-Category, where each point of the triangle is a different (x,y) coordinate.
At point 1, we’re going to plot our Review value. At point 2, we’re going to plot our Revenue value, and Budget will be at point 3.
From the diagram above, you can see our x coordinates are one of two numbers, either 0 or x1 (ie the x coordinate for the Revenue & Budget points is the same).
The y coordinates vary per measure, and need to be ‘normalised’ to a common scale, as otherwise, the Revenue & Budget figures would be plotted significantly higher than the Review values (you might find this blog by Zen master Jonathan Drummey useful at this point).
When normalising, you’re typically looking to convert your values to a scale from 0-1; this means the values aren’t plotted at their absolute values, but are still plotted in the same relative order to each other ie lowest value at the bottom, highest at the top.
When normalising a set of values from 0 to 1, your lowest value would typically be at the 0 position , with your highest value at the 1 position. To calculate where your value would sit on this scale, you need to know 3 numbers; the value to convert, the maximum value in the range of values to plot and the minimum value in in the range of values to plot. The normalised value is then :
(Current value – min value) / (max value – min value)
ie the difference between your value and the lowest as a proportion of the difference between the whole range.
However, in this instance, I chose to simplify the normalisation, and my method only works due to the values in the example data provided.
Side Note I consider Revenue & Budget to be values that are directly related to each other ie if Budget = Revenue I’d expect them to be plotted at the same point. I therefore chose to normalise these values across the combined range. This gave me different results from the solution, where Budget and Revenue were normalised independently of each other.
The maximum average review value is 5, the maximum value for budget and revenue combined is 100. As 100 is exactly 20 times bigger than 5, I simply chose to normalise the revenue/budget values to be on a scale of 0-5 instead, rather than normalising all values (Review, Budget & Revenue) to be on a 0-1 scale.
First up, we want to identify a position for each point
Path
IF [Table Name] = ‘WW’ THEN 1 ELSEIF [Table Name] = ‘WW1’ THEN 2 ELSE 3 END
Then we’ll create the x coordinate for each point
x
IF [Path] = 1 THEN 0 ELSE 20 END
I’ve just chosen an arbitrary value to plot the 2nd & 3rd points at – could easily have been 1.
Then we’ll create the y coordinate for each point, which is where the normalisation comes in
y
IF MIN([Path]) = 1 THEN AVG([Review]) ELSEIF MIN([Path]) = 2 THEN AVG([Revenue])/20 ELSEIF MIN([Path]) = 3 THEN AVG([Budget])/20 END
Dividing by 20, normalises the values to a scale of 0-5 as discussed above.
Let’s put these values all out in a table, so we can see what’s going on
Building the Polygon Viz
You can see we have 3 points per Sub-Category, so we can plot the x & y measures on a sheet as follows :
Add Min(x) to Rows
Add y to Columns
Add Sub-Category to Detail
Add Table Name (or Path) to Detail
Change Mark Type to Polygon
Change Colour to #666666 with 30% opacity
Tooltips
We need 2 new calculated fields for the Tooltip
Profit
AVG([Revenue]) – AVG([Budget])
formatted to $ with 0 dp
Margin
[Profit]/AVG([Budget])
formatted to % with 1 dp.
Note this will differ from the solution, where I think Ivett inadvertently used SUM(Budget) rather than AVG.
Add these to Tooltip field and adjust text accordinly.
Colouring the Line
To make the line, I created a second instance of y
y2
IF MIN([Path]) = 1 THEN AVG([Review]) ELSEIF MIN([Path]) = 2 THEN AVG([Revenue])/20 END
which just plots 2 points rather than 3.
Add this to Columns next to y, make dual axis and synchronise axis. Things might have disappeared – you need to remove Measure Names from both marks cards. Change the mark type of the y2 card to Line.
You won’t see much difference at this point (or you shouldn’t). We need a field to define the colour
Colour : Line
IF [Profit] < 0 THEN ‘Non-Profitable’ ELSE ‘Profitable’ END
Add this to the Colour shelf of the y2 marks card and adjust to suit.
Labelling the line
On the y2 marks card, add Review (set to AVG) to Label shelf, and move Sub-Category to Label shelf. Set Label to only label Start of Line. Adjust format/layout of Label to suit.
Set the format of Review to Number Standard – this is a format little used, but will display a whole number or a decimal. I discovered this through an Andy Kriebel WoW from a long time ago and is a real gem!
Finalising the viz
To tidy up and get the viz looking like the solution
Format to remove all gridlines
Hide the y2 axis
Hide the ‘4 nulls’ indicator if you have it
Edit the y axis
Fix the axis from 0.5 to 5.25
Add a title
Set axis ticks to none
On the y2 marks card, edit the Colour shelf and change the markers to show to All (this gives the circles on each end of the line)
Change the Min(x) pill to be FLOAT(Min(x)) using the ‘type in pill’ function
Edit the x axis to be Fixed from -0.3 to 19.9
cutting off the end of the axis makes the end circle disappear. This is very sneaky, and I had to see the solution for this!
Hide the x axis
Remove the row divider lines
So we’ve now got the core viz – hooray! But we’re not quite done – boo!
Expand /Collapse
Ivett set the viz to expand to show a column by Sub-Category on click.
This is another requirement I couldn’t get however hard I tried. I duplicated my viz and created a version with Sub-Category on Columns and tried to use Parameter Actions to set a parameter that would control which viz would display using a sheet swap techinque. However when I added the necessary field to the Detail shelf, all the polygons disappeared and I don’t understand why…. I therefore published my first instance of this challenge with a parameter the user controlled to decide which view to show. This is here.
So I had to look at Ivett’s solution to see how she had achieved this, and it involved sets.
Right click on Sub-Category and Create – > Set
Selected Sub-Cats
Don’t select any values at this point.
Create a field
Expand
IF [Selected Sub-Cats] THEN [Sub-Category] ELSE ‘Click to Expand’ END
If there are values in the set, then the set values will be returned, otherwise the text ‘Click to Expand’ will display.
Add this to the Columns shelf, and ‘Hide field labels for columns‘ so the text ‘Expand’ doesn’t show.
A dashboard action will ultimately drive the behaviour, but we can test the display by editing the set and selecting all values.
The view should expand as expected, showing a column per Sub-Category
But the name of the Sub-Category is still displayed on the label, and in the example this isn’t the case. To fix this I created another calculated field
Label: SubCat
IF [Selected Sub-Cats] THEN ” ELSE [Sub-Category] END
which I added to the Label shelf, and adjusted the display to suit.
‘Reset’ the view to show the collapsed version by re-editing the set and removing all values.
How to Read this chart legend
I simply duplicated the main viz, and filtered by Sub-Category = Chairs.
I removed the label, and then used the Annotate Point functionality to add the relevant labels against the points.
Profitable Legend
The Profitable / Non-Profitable legend makes use of our trusted friend MIN(0), with pills arranged as below.
Applying the Set Action
Create a dashboard to the size specified, and just use floating objects to position the text and various sheets.
To drive the expand / collapse function, create a Set Action on the main viz as below, that targets the Selected Sub-Cats set.
Revenue / Budget Label
This is just managed using Text objects on the dashboard, carefully positioned in the right locations. You might need to add additional objects to get the layout required.
The background of the whole dashboard is set to light grey and the sheets and objects need to be set to the same grey or white to get the same presentation.
So fingers crossed, you should have a complete solution now.
My final version (after I peeked at Ivett’s) is here.
Luke set the #WOW challenge this week, stating it had a difficulty rating of 10/10, so it was with some trepidation when I sat down to tackle it. Was it going to be as difficult as Luke’s radial bar challenge from 2018 week 10….?
Looking at the viz, and reading through the requirements, it didn’t seem as bad to me – it’s a table calculations challenge, and I’m OK with those. I started using Tableau before LoDs were invented, so table calculations don’t scare too much. I’m no expert though, and it still often takes a bit of trial & error to get the settings right.
Building out the Calcs
With a lot of challenges, I often start by just trying to build a tabular view of the data to sense check I’m getting the right numbers, which I then publish onto Tableau Public along with my viz. With a table calculation challenge, building the table of data is crucial.
The 3 key pieces of existing data needed for this challenge are Customer Name, Order Date & Order ID.
To start we want to put these on the rows, but given this will initially generate a lot of rows of data, I chose to arbitrarily filter to a random set of 20 or so customers (include Noel Staavos, as he’s a slight exception, which may catch you out later).
You can see from the above, Noel has multiple orders on the same day, which we need to handle.
The first requirement states we need to order the data by total number of orders per customer, so for this we need the 1st of many calculated fields (this one isn’t a table calculation, though it could be….):
Total Orders
{FIXED [Customer Name]: COUNTD([Order ID])}
Add this as a discrete pill on the Rows (since the final viz needs to show it this way too). I’ve placed it after the Customer Name but it doesn’t have to be there at this point.
We need to sort the customers by this field, so click on Customer Name and select Sort to bring up the Sort dialog. Choose to Sort By Field, Sort Order Descending, Field Name Total Orders. Aggregation Sum
We’ve now got our data ordered in the way we need. The next step is to work out the 90-day reorder rate per customer, which we will tackle in several steps. In much of what follows, some of the calculations could well be done in one calculation, but I like to see the ‘building blocks’ to help verify the calcs are correct.
First up we need to work out how many days between each order, and to figure this out we need to compare the Order Date on each line to the Order Date of the previous line.
I want to display the date of the previous order on the same line as the current order, so create
Previous Order Date
LOOKUP(ATTR([Order Date]),-1)
This ‘looks up’ the Order Date on the previous row. If I wanted to look at the next row, the second attribute would be 1 rather than -1. Or if I wanted to look at the data in the row 2 rows before the current one, I’d use -2.
Add Previous Order Date to the Rows as a discrete field. By default it’ll probably show as ‘Null’, but this is because the table calculation is computing across the table (so is looking for a previous column containing Order Date which doesn’t exist), whereas we want to look down it (ie by Row).
Click on the triangle against the Previous Order Date pill (the triangle indicates its a table calculation), and select Edit Table Calculation
We need to change the settings so they calculate for each Customer Name. Set to Specific Dimensions, and uncheck Customer Name
You can see from the above, that for Noel Staavos’ multiple order on the same day, one of the orders is comparing against an order on a different date, while the other is comparing to the other order made on the same date.
Ok, so now we have the current date & previous date on the same row, we now need to work out the number of days between the dates.
Days Since Previous Order
DATEDIFF(‘day’, [Previous Order Date],ATTR([Order Date]))
Add this to the Text shelf. Hopefully it should automatically use the same table calculation settings defined for Previous Order Date, but if the numbers look off, double check the settings. They should match.
Now we can work out if the order is within 90 days of the previous order
Reorder Within 90 Days
IF ISNULL([Previous Order Date]) THEN NULL ELSEIF [Days Since Previous Order]<=90 THEN 1 ELSE 0 END
Note, I am purposely choosing to output 1 or 0 (ie a number) rather than true or false (a boolean), as it will make the next calculation easier.
Again add Reorder Within 90 Days to the table, and again sense check the table calculation settings if things don’t look right.
Now we have the information we need to work out the reorder rate per customer, which is the number of records where Reorder Within 90 Days is 1 as a proportion of the number of records with a Reorder Within 90 Days value of either 0 or 1, since the requirements state the first order for each customer shouldn’t be included in the metric.
90-Day Reorder Rate
ZN(WINDOW_SUM([Reorder Within 90 Days])/WINDOW_COUNT([Reorder Within 90 Days]))
Set this to be a percentage with 0 dp.
By choosing to set Reorder Within 90 Days to an integer, the WINDOW_SUM() is simply summing up the 1s & 0s in the column. WINDOW_COUNT() is just counting the number of 1s & 0s there are in the column. Wrapping with a ZN means any Customers without any reorders will report as 0% rather than NULL.
Let’s add that to the table now too. You’ll see it reports the same value down the whole table across all customers, whereas we need it to show a different value per customer (although still show the same value for all the rows of the same customer).
We need to once again adjust the table calculation settings for this field.
What you’ll notice this time though, is there are Nested Calculations within this field, so the settings need to be checked for both the Previous Order Date calculation and the 90-Day Reorder Rate calculation
Both need to be identical with Order Date and Order ID being checked.
At this point, we have enough that we could start building the main viz, but I’m going to continue building out the ‘check sheet’ with the data I need for the KPIs too.
For the Overall Reorder Rate < 90 days we need to get a count of all the reorders within 90 days across all customers, as a proportion of all reorders (ie not the 1st order for each customer).
Count Reorders < 90 days per customer
IF FIRST()=0 THEN WINDOW_SUM([Reorder Within 90 Days]) END
I’m choosing to output the value only against the 1st row for each customer. This is because I’m going to be adding up this column shortly, and if I outputted the value against each row, I’d be double-counting.
Add this field to the table, and once again verify the table calculation settings are applied to each nested calculation. If done right, you should get the correct number against the first row for each customer
Now I want a sum of this column
Total Reorders < 90 Days
IF FIRST() =0 THEN WINDOW_SUM([Count Reorders <90 days per Customer]) END
Again, I’m choosing just to show this value against the 1st row, but this time it will be the first row in the whole table, as this time the table calculation needs to considering all the customers.
Add this field to the view. It’ll probably automatically give you the right number, but it’s worth having a look at the table calculation settings.
It contains 3 nested calculations this time : Total Reorders < 90 days, Count Reorders < 90 days per Customer, Previous Order Date.
The settings for Count Reorders < 90 days per Customer and Previous Order Date should be as before, with Order Date & Order ID both checked.
The setting for Total Reorders < 90 days is different though. By default it like shows Table(down), which gives the right result, but to ensure things don’t go awry if the pill gets moved around for some reason, I like to explicitly set the computation, by changing to Specific Dimensions and selecting all 3 fields
So this gives me one of the values I need to help me work out the overall rate; I now need the other
Count All Reorders
IF FIRST()=0 THEN WINDOW_COUNT([Reorder Within 90 Days]) END
This again is a nested calculation. Previous Order Date should be set with Order Date & Order ID as usual, and Count All Reorders should be set across all 3 fields.
Now we have the numbers to work out
Overall Reorder Rate
[Total Reorders <90 days]/[Count All Reorders]
which is set to a Percentage to 0 dp.
Add this onto the table and check the calculation settings again. This time there are 4 nested calculations
Total Reorders < 90 days : set to all 3 fields
Count Reorders < 90 days per Customer : set to Order Date & Order ID
Previous Order Date : set to Order Date & Order ID
Count All Reorders : set to all 3 fields
Remember, you may have filtered the customers in your view, so you may get a different value from the solution at this point!
Right onto the final KPI – average number of reorders per customer. For this we need the total number of reorders, which we’ve already got- Count All Reorders, and number of customers.
There’s probably a simpler way of doing this, but I’m continuing down the same route I’ve been working on.
First I want a count of the customer for each customer (which is 1), then I want to sum that up.
Count Customers
IF FIRST()=0 THEN COUNTD([Customer Name]) END
then
Total Customers
IF FIRST()=0 THEN WINDOW_SUM([Count Customers]) END
When added to the table, this is again a nested calculation with Count Customers set to Order Date & Order ID, and Total Customers set to all 3.
Now we can work out
Average Reorders Per Customer
[Count All Reorders]/[Total Customers]
which is set to be a number to 1 decimal place.
Adding this to the table, and verify the nested calculations are all set properly
Count All Reorders : set to all 3 fields
Previous Order Date : set to Order Date & Order ID
Total Customer : set to all 3 fields
Count Customers : set to Order Date & Order ID
Yay! We’ve now got all the values we need to build the KPI table and the main Viz.
I would recommend naming this sheet as Check Data or similar.
Building the KPI Viz
First step, duplicate the Check Data sheet.
For the KPIs, we only need 2 of the measures we’ve created, so remove all the pills from the Measure Values section except Overall Reorder Rate and Avg Reorders per Customer.
Then remove Total Orders and Previous Order Date from the Rows.
Due to the way table calculations work, we need to keep Customer Name, Order Date & Order ID in the view, but we only need the 1st row in the whole table, as that is where the data we want has been stored.
Create a new field
First Row
FIRST()=0
Add this to the Filter shelf and select True
Hide Customer Name, Order Date & Order ID by unchecking Show Header from each pill
We need to create a fake axis to get the display we need. In the Columns type in Min(0) twice, then remove Measure Names
Expand one of the Agg(Min(0)) cards on the left, and then drag the Overall Reorder Rate pill from the Measure Values section at the bottom, so it sits directly on top of the Measure Values pill
The Avg Reorders per Customer will now be on the Label shelf of the other AGG(Min(0)) card.
Change the mark type of both cards to Text.
Uncheck Show Header against the MIN(0) pills, and format to remove all columns/row/grid & zero lines.
Now you can format the text for each mark to be the appropriate size and with the relevant wording.
Then finally turn all tooltips off, and remove the Customer Name filter if it’s still there.
Hopefully you should get the numbers that now match the solution! Rename the sheet KPIs.
Building the main Viz
Once again start by duplicating the Check Data sheet we built.
Move Order ID and Previous Order Date onto the Detail shelf.
Move 90-Day Reorder Rate onto the Rows, and set to discrete
Move Order Date to Columns and change to continuous. Remove Measure Names from Columns.
Change the mark type to shape and move the Reorder Within 90 Days pill from the Measure Values section onto the Shape shelf. Change the shapes so NULL is the triangle and 0 and 1 are both diamonds.
Add Reorder Within 90 Days to the Colour shelf too, by clicking on the one on the Shape shelf, holding down Ctrl and dragging onto Colour. This will have the affect of duplicating the pill and will retain the table calculation settings. If you can’t get this to work, then drag from the Measures pane on the right, but make sure you set the table calc settings to match.
Change the colours so Null & 0 are grey (#CAC4BE) and 1 is purple (#5557EB).
The Tooltip for each shape also needs to be coloured accordingly, based on whether the mark represents the First order, a reorder over 90 days, or a reorder under 90 days (I chose to add the logic for first). I created 3 calculated fields for this:
Tooltip: First
IF FIRST()=0 THEN ‘First Order’ END
Tooltip:Over 90
IF [Reorder Within 90 Days]=0 THEN ‘Over 90 days’ END
Tooltip:Witin 90
IF [Reorder Within 90 Days]=0 THEN ‘Over 90 days’ END
I placed all 3 of these fields on the Tooltip shelf which was then formatted as:
while all 3 fields are in the tooltip, only 1 will ever actually display a value,
Make sure the table calculation settings for all 3 tooltip fields are set with Order Date & Order ID checked.
Now we need to create the line to join the marks up.
Duplicate the Order Date pill on Columns to create 2 instances of the pill sitting side by side. Set to Dual axis, synchronise and s’move marks to the back’ so the lines are behind the shapes.
Change the mark type on the Order Date (2) card to line. The requirements state
“Make sure the “line” color matches with the following mark. It must be a single color“.
Hmmmm… I don’t think that last sentence was there when I tackled the challenge… I just read the line colour ‘matches’ with the following mark, so to do this I added Previous Order Date to the Path shelf. The line graduates in colour, but from the colour of preceding mark to the colour of the following mark.
Looking at Luke’s solution now that I’ve seen this statement, he chose to use a Gantt mark type to represent the line, which meant each gantt block was coloured fully. Oh well… I didn’t quite get this then 🙂
Stop the tooltips from showing on the line, by deleting the text in the tooltip, then its just a case of re-sizing the marks, and formatting to suit. Set the background colour of the worksheet to grey.
Adding the red header on the dashboard
To get the red header displayed above the table, add, a container onto the dashboard and add the viz to sit inside it. With the container selected (identified by the blue border), set the background of the container object to red (#D81159).
Then select the viz itself (identified by the grey border), and adjust the outer padding settings, to have 0 left, bottom & right, and 4 at the top.
Hopefully this should now be pretty much everything needed to get this challenge sorted. If I’ve missed anything, please comment. My published viz is here.
It’s a pretty lengthy read, so if you’ve got this far – well done & thank you!
For Week 37 2019 of #WorkoutWednesday, Luke Stanke challenged us to create a rounded bar chart, displaying the % of sales by sub-category in a selected region as a proportion of the whole.
In the challenge (here), Luke hinted that this could be achieved by unioning the data set together, but I recalled doing something similar before, and so didn’t think I’d need this.
Before tackling the bars though, I needed to set up the data required, which was pretty straightforward. I needed a parameter to store the region, and then a couple of calculated fields
Region Sales
If [Region] = [Region Param] THEN [Sales] END
which stored the value of the sales for the region selected and
Region % of Sales
SUM([Region Sales]) / SUM ([Sales])
which stored the % value required.
Having set that up, I then went to my stored ‘go to’ references as a refresher:
I started with Andy’s technique first, which uses MIN(0) plotted on the same axis as Region % of Sales, displayed as a line chart with Measure Names on the Path shelf to force the points to join up. Increasing the size of the line produces rounded bars.
However, I then spent some time puzzling over how to get the 2nd bar, which needed to be on a second (dual) axis, and ideally also needed to be created in a similar technique using MIN(0) to MIN(1) instead. I tried a few things, but couldn’t crack it this way, so used the technique Ryan adopts which overlays bars and circles on a dual axis.
For this, MIN(0), MIN(1) and Region % of Sales are all added to the same axis, so Measure Values is plotted against Sub-Category and Measure Names added to colour
However if you look closely, you’ll see the values go beyond 1, as the marks are all stacked. We don’t want this, so we need to turn stacking off…
.. and then move Region % of Sales to the top of the list in Measure Values, to bring it to the front, and adjust the colours to suit
This gives our bars. Now, for the rounded bit.
Duplicate Measure Values, by clicking on the Measure Values pill in the columns shelf, holding down Ctrl and dragging your mouse to the right. This will create a copy of the Measure Values pill which you can drop next to it
Make the chart dual axis, synchronise the axis and change the mark types to be a bar for one set of Measure Values and a circle for the other. Adjust the sizing so the marks appear as one ‘lozenge’ shape
Labelling the end of the bar with the % values, needed a little bit of creativity. Just adding Region % of Sales to the label of the ‘bar’ marks card showed the label a bit too close to the display, even when right-aligned, as the circle mark was taking up the space
Labelling the circles instead would have meant a bit of trickery to only label the last circle.
So the quickest & easiest thing to do, was to simply create a label field to make the spacing work :
Donna + DataViz 