With #TC21 looming next week, Candra’s set this week’s challenge, based on inspiration from past Tableau Conferences – a simple looking, but effective visualisation for understanding profit performance within some pre-established timeframes.
- Building the BANs
- Identifying Top 5 / Bottom 5 / Everything Else
- Building the Chart and Labelling the Bars
- Adding the interactivity
Building the BANs
The timeframes we need to report over need to be based on a specific date. In this case it’s the latest date in the data set. If you were using this for a business dashboard, you might be basing it on Today / 1st of the Current Month etc. Rather than hardcode the date I need, I’ve worked out the latest month I want to use by
Set all the Order Dates in the data set to be the 1st of the month, then get the maximum of these dates. So as the last date in the data set is 30th Dec 2021, that’s been truncated to 1st Dec 2021 which is then what this field stores.
I then want to capture the profit values for each month, quarter, year into separate fields, so we have
IF DATETRUNC(‘month’, [Order Date])=[Max Month] THEN [Profit] END
This only stores Profit values for rows where the Order Date is also in December
IF DATETRUNC(‘quarter’,[Order Date])=DATETRUNC(‘quarter’,[Max Month]) THEN [Profit] END
This only stores Profit values for rows where the Order Date is in the same quarter as December (ie the 4th quarter which is months Oct-Dec).
IF DATETRUNC(‘year’,[Order Date])=DATETRUNC(‘year’,[Max Month]) THEN [Profit] END
This stores Profit data for rows where the Order Date is in the same year.
All these fields are formatted to be $ with 0 dp.
A basic viz can the be built with Measure Names on Columns and Measure Names and Measure Values on Text. The Measure Names heading is then hidden, and the font and table formatting adjusted so the sheet looks as below.
Note – Naming these fields Month, Quarter, Year rather than Monthly Sales, Quarterly Sales etc, makes this display much easier and also helps with the interaction later.
Identifying the Top 5 / Bottom 5 / Everything Else
We need to be able to identify the Sub-Categories which have the best profits, those that have the worst, and the ‘rest’. We’re going to use Sets to help us with this. However the set entries could change depending on whether we’re looking by month, by quarter or by year. So first we need to create a field that is going to store the particular Profit value we need depending on what time period is being selected.
We need a parameter pDatePart to capture the time frame. This is a string field which is just defaulted to the text ‘Month’.
The interactivity later will set this parameter to the different values.
So now we know the ‘selected’ date part, we need to get the appropriate profit value
Value To Plot
WHEN ‘Month’ THEN [Month]
WHEN ‘Quarter’ THEN [Quarter]
This just uses the values from the 3 measures we created to start with.
So now we can create the sets we need. Right click on Sub-Category > Create > Set and create a set called Top 5 that is based on the Top 5 Value to Plot values
Then create another set in the same way called Bottom 5
With these sets, we can now determine the Sub-Category ‘label’ that will be displayed
IF [Top 5] OR [Bottom 5] THEN [Sub-Category] ELSE ‘Everyone Else’ END
and the grouping that will be used to colour the bars
Sub Cat Group
IF [Top 5] THEN ‘Top 5’
ELSEIF [Bottom 5] THEN ‘Bottom 5’
ELSE ‘Everything Else’
Building the Chart & Labelling the Bars
Ok, so now we’ve got the building blocks in place, we can build the chart. You will probably be tempted to build a bar chart (I did to start with), but positioning the labels then became a bit tricksy. When we get to the labels, we’re going to need to use the left and right alignment options. However, when you build a bar chart, if you right align the label, the label will be positioned outside at the end of the bar (even though this seems a little odd with negative values, as it looks to be on the left…).
Right aligned labels
But then we set the labels to be left aligned, the labels appear inside the bar instead, and not outside on the left.
Left aligned labels
So instead, rather than using the bar mark type, we need to build this chart using the gantt mark type, and base the Size on the Value to Plot field.
However, the value being plotted is actually an average value based on the number of Sub-Categories being ‘grouped’ as otherwise the value associated to Everything Else can end up bigger than all the rest. I created the following field
Avg Value To Plot
SUM([Value to Plot])/COUNTD([Sub-Category])
formatted to $ with 0dp.
So now we start building by adding Sub-Category Display to Rows and type in MIN(0) into Columns. Change the mark type to Gantt and add Avg Value To Plot to Size. Add Sub-Cat Group to Colour and adjust accordingly. Sort the Sub-Category Display field by Avg Value To Plot descending.
Now we can’t just label by a single field of the value or the sub-category, as while the ‘automatic’ label alignment option, almost puts the labels in the right positions, there is no way to define an ‘opposite’ to the ‘automatic’ alignment. We need to define some dedicated label fields based on where we want them to display.
Label – Left – Profit
IF [Avg Value To Plot]<0 THEN [Avg Value To Plot]
If we’re in the bottom half of the chart, we’re going to display the Profit value on the left side.
Label – Left – Sub Cat
IF [Avg Value To Plot]>=0 THEN ATTR([Sub-Category Display])
If we’re in the top half of the chart, we’re going to display the Sub-Category Display on the left side.
Add both these fields to the Label shelf and then adjust the label alignment to be left.
To label the other ends, we need to create two further label fields
Label – Right – Profit
IF [Avg Value To Plot]>=0 THEN [Avg Value To Plot]
Label – Right – Sub Cat
IF [Avg Value To Plot]<0 THEN ATTR([Sub-Category Display])
We then need to create another MIN(0) on Columns (easiest way is to hold down control, then click on the existing MIN(0) field and drag it next to itself to create a duplicate. Then on the 2nd marks card, remove the two Label – Left – xxx fields and add the two Label – Right -xxx fields. Change the alignment to right.
The make the chart Dual Axis and synchronise the axis.
Now you can hide the Sub-Category Display header from showing, hide the axis, remove gridlines etc.
Adding the interactivity
Once the two sheets are on the dashboard, you can add a dashboard parameter action which will on select of the KPI/BAN chart, pass the Measure Name into the pDatePart parameter. When the mark is unselected, the parameter value should stay as it is.
And hopefully, you should now have a working viz. My published version is here.
Happy vizzin’! Stay Safe!