I built an initial version of the heatmap only, which I’ve published here, but I couldn’t get the display to match Valerija’s when I clicked on it. (Note – I wasn’t bothered about Viz in Tooltip part at this point). I used 2 dimensions to display the yellow header section, and you can see below, when clicking, this is noticeable. But Valerija’s solution treats the header as a single entity.
I couldn’t figure out how she’d managed this, so I had to have a look at the solution, and then I built my own instance, which is what I’ll now blog about.
Defining the core fields
Connect to the datasource and add a data source filter to restrict the data by the latest year (as I used Superstore 2025, I filtered to 2025). Doing this meant I didn’t have to worry about adding worksheet level filter fields.
On a new sheet add Category and Sub-Category to Rows and add Sales to Text and then sort descending so you can see the data we need to work with. Format Sales to be $ to 0 dp.
Create a new field
Sales by Cat Rank
RANK(SUM([Sales]))
change it to be discrete and then add to Rows. Adjust the setting of the table calculation so it is computing by Sub-Category only, so the ranking restarts for each Category
We will also need to display the Category in upper case, so create
Category Upper
UPPER([Category])
and add to Rows.
Having this tabular layout just lets us clarify how the table calculation will be working.
Building the Heatmap Table
On a new sheet, add Category to Columns and Sub-Category and Sales to Text and align centrally (Note – I originally put Category Upper on the columns, and then changed later after taking all the screen shots)
Add Sales by Cat Rank to Rows. Change it to a continuous (green) pill and edit the axis to be reversed. Adjust the table calculation so it is computing by Sub-Category only.
Create a new field
One
1
Change the mark type to ganttbar and add One to the Size shelf, setting the aggregation to Avg. Increase the size of the mark via the Size shelf to as large as possible. Add Sales to Colour then add a white border.
We need the axis numbers to be central to the ‘row’, so to fix this, double click into the Sales by Cat Rank pill and change it to subtract 0.5 ( [Sales by Cat Rank] -0.5 )
Re-edit the axis to reverse it again.
So our ‘table’ is now displaying data on a axis from 0.5 up to 9.5. To add the header, we’re going to plot another mark at -0.5, so we can display a section of the same size as the existing ones. For this, double click into Rows and type MIN(-0.5).
This has the effect of creating a second marks card
Remove Sales from Colour and Sub-Category from Label. Add Category Upper to Label. Adjust the label text alignment and formatting and change the Colour to yellow.
Make the chart dual axis and synchronise the axis and we now have the required display.
Tidy up by
Remove row & column dividers
Remove gridlines, zero lines & axis ticks
Hide the right hand axis (right click > uncheck show header)
Hide the Category Upper column labels (right click pill > uncheck show header)
Remove the left hand axis title
Fix the left hand axis from -0.5 to 9.5
Format the axis with a custom formatting of #,##0;-#,##0;TOTAL so that 0 is displayed as the word TOTAL (this is very sneaky by the way, and took me a while to work out)
Then format the font to be bold
Adjust the Tooltip on the Sales by Cat Rank marks card to display the Sub-Category and Sales value.
Delete the text from the Tooltip on the MIN(-0.5) marks card
Name the sheet Table or similar
Building the Viz in Tooltip
On a new sheet add Order Date to Columns at the discrete month level (blue pill) and add Sales to Rows. Add Sub-Category and Category to Detail. Format the date axis so the dates are just using First Letter.
Create a new parameter
pSubCat
string parameter defaulted to Bookcases
Then create a field
Is Selected SubCat
[Sub-Category] = [pSubCat]
and add to the Colour shelf. Adjust accordingly, then make sure True is listed first in the legend, so the line is ‘on top’
Create a new field
Label Line
IF [Is Selected SubCat] THEN [Sub-Category] END
and add to the Label shelf and update to allow marks to overlap. Hide the Order Date label heading (right click > hide field labels for columns). Title the chart Line or similar
Remove gridlines and row/column dividers
Go back to the Table sheet, and update the Tooltip of the Sales by Cat Rank marks card to reference the Line chart and update the filter to pass as just <Category>
Adding the final interactivity
Create a dashboard and add the Table sheet. Then add a parameter action
Set Sub Cat Param
On hover of the Table sheet, set the pSubCat parameter with the value from the Sub-Category field, setting the value to <empty string> when the selection is cleared.
If all has been applied, then the line chart should just display the lines associated to the Sub-Categories in the same Category
A guest post this week from Hannah Bartholomew asked us to show a filtered and unfiltered view of data in the same viz.
Building the calculations
We need to show the total sales and the total sales by Sub-Category which won’t be impacted by any filters. We can use a level of detail (LODs) for this.
Sales per Sub Cat
{FIXED [Sub-Category]:SUM([Sales])}
format to £ with 0 dp
Pop this and Sales into a table. The total value is identical.
Create a new field
Order Date (Year)
Year([Order Date])
Add Region, Customer Name, Manufacturer and Order Date (Year) to the Filter shelf and show all the filters (display as multiple value drop down to save space). If you now adjust any of the filter values, the Sales value will change, but Sales per SubCat won’t.
We need to show the % of total, so create
% Total
SUM([Sales]) / SUM([Sales per Sub Cat])
and format to % with 0 dp. Add to the table.
This essentially are the measures needed for the overall bar at the top of the display.
Now add Sub-Category to Rows. and you can see the data is now simply broken down to this level without any adjustments to the calculations.
If you reset all the filters back to ‘All’ the Sales per Sub Cat and Sales columns should match with 100% .
Lastly, we need to be able to filter by a start and end date. We need some parameters and additional calculated fields for this.
Create new fields
Max Date
{MAX([Order Date])}
Min Date
{MIN([Order Date])}
Create a new parameter
pStartDate
date parameter that refers to the Min Date field when workbook opens, and displays a range of values based on the values in the Order Date field when the workbook is opened.
Create another parameter pEndDate which is similar except it refers to the Max Date field when opened.
Show these 2 parameters. We then need
Filter – Order Date
[Order Date]>=[pStartDate] AND [Order Date]<=[pEndDate]
Add this to the Filter shelf and set to True.
Then set all the filters to ‘apply to worksheets > all using this data source‘so that when you create new sheets, they will get automatically added. Test changing the dates and the Sales and %Total will adjust.
Building the Total Sales Bar
On a new sheet, add Sales to Rows and add Sales per Sub Cat to Rows. Show the relevant filter controls and the two parameters.
Add % Total to the Tooltip shelf of the All marks card, then adjust the tooltip on each of the Sales and Sales per SubCat marks cards as required.
Make the chart dual axis and synchronise the axis. Change the mark type on the All marks card to be bar. Adjust the colours of the Measure Names legend to suit. Click the top axis and select move marks to back. Reduce the size of the Sales bar.
Hide the top axis, edit the title of the bottom axis, remove all column and row dividers, make the column axis rules & tick lines slightly more prominent. Edit the title of the sheet.
Building the Sub-Category by Sales Bar
The simplest way for this is to duplicate the above sheet. Then add Sub-Category to Rows, and apply a sort to sort by the Sales per Sub Cat field descending.
Then update the title on the sheet.
Building the ‘Filtered Options’ List
There is a section on the display that shows the values selected in the filters. For this, create a new sheet and then double click in to the area below the marks card and type ‘dummy’ (including quotes) and press enter/return. At this point a mark should display and all the filters get added.
Change the mark type to polygon. Then update the text of the sheet title to reference the filters. Format the background of the whole worksheet to be pale blue.
Now the sheets all need to be arranged on a dashboard. Use layout containers and outer/inner padding to help build the design. I just took a screen snip of the logo Hannah used and then added that as an image to my right hand vertical container. I also created another sheet using a similar ‘dummy’ technique but changed the mark type to shape and added a ‘help’ icon shape I already had.
For this week’s #WOW2023 challenge, Lorna asked us to recreate this small multiple (or trellis) chart which organises the time series charts per Sub-Category into a grid format, where the number of columns is determined by the user.
Whenever I need to build these types of charts, I often end up referencing this blog post by Chris Love from 2014, as this has the basis for the calculations required.
To get started, we need to capture the number of columns based on a parameter
pCols
integer parameter ranging from 1 to 5 with a step size of 1, that is defaulted to 5
On a new sheet, display the parameter, and add Sub-Category to Rows. Apply a sort to Sub-Category based on the field Sum of Sales descending.
Based on the pCols parameter, we need to determine which column and subsequently which row each Sub-Category should be positioned in. We will make use of the index of each entry in the list. Double click into the Rows shelf and manually type in INDEX(). Change the field to be discrete (blue). This will number every Sub-Category row from 1 upwards. To be explicit, edit the table calculation, to explicitly set it to compute using the Sub-Category dimension.
To determine the column for each sub-category
Column
(INDEX()-1)%[pCols]
the % symbol, is the modulo and returns the remainder when the INDEX()-1 is divided by pCols – ie if INDEX() = 12, then 12-1 = 11 and 11 divided by 5 is 2 with 1 left over, so the result is 1.
Add this to the sheet, set it to be discrete (blue) and also edit the table calculation to compute using Sub-Category. You can see that Chairs and Machines are in the same column. If you adjust pCols, the values will adjust too.
To determine which row each Sub-Category will be positioned in we need
Row
INT((INDEX()-1) / [pCols])
This divides INDEX()-1 by pCols and just returns the whole number. ie if INDEX() = 8, then 8-1 = 7, and 7 divided by 5 = 1.4. The integer part of 1.4 is 1.
Add this to Rows and set to be discrete, and adjust the table calculation as before. You can see Chairs and Phones are in the same row (but different columns), which Chairs and Machines are in the same column, but different rows.
Let’s rearrange – Move Column to Columns, Sub-Category to Text and remove INDEX() altogether, and you’ll get the basic grid layout we need.
Create a new field to store the date part we’re going to present
Month Order Date
DATE(DATETRUNC(‘month’,[Order Date]))
Add this to Columns, and set as exact date and add Sales to Rows and move Sub-Category to Detail. At first gland this may look ok, but if you look closely, you’ll notice that there are multiple lines on some of the charts.
This is because there are some states that didn’t sell some of the sub-categories on the month, and this affects the index() calculation when the Month Order Date is set to be a continuous (green) pill (the viz below highlights this better – Accessories is now indexed with 6 and 7…
So to resolve this, add Month Order Date as a discrete (blue) exact date to the Detail shelf underneath the Sub-Category field. Then change the Month Order Date field in the Columns shelf to be a Continuous (green) attribute. Then adjust the table calculation on both the Column and the Row fields, so they are computing over both Sub-Category and Month Order Date, but at the level of Sub-Category.
Format the ATTR(Month Order Date) field on Columns to be the custom format of yyyy, so the axis just display years
and then format the Month Order Date field on the Detail shelf, to be the custom format of mmmm yyyy, so the information in the Tooltip will display the date as March 2001 etc. Adjust the Tooltip to match.
The label for each Sub-Category needs to be positioned based on the y-axis at the maximum sales across the whole display, and on the x-axis at the last point in the date scale ie December 2023. For this we need
Max Sales in Table
WINDOW_MAX(MAX([Sales]))
Label Position
IF LAST()=0 THEN [Max Sales in Table] END
Add Label Position to Rows and adjust the table calculation so the Max Sales in Table nested calculation is computing by both Sub-Category and Month Order Date, and the Label Position nested calculation is computing by Month Order Date only. This should result in a single mark per Sub-Category displaying.
Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card. On the Label Position marks card, change the Mark type to shape and select a transparent shape (see this post for details on how to get this set up). Move Sub-Category to Label and align top right.
Finalise the display by hiding the Column and Row fields (uncheck show header), hiding the right hand axis (uncheck show header). Format to remove all gridlines & zero lines and hide the null indicator. Remove the axis title.
You should then be able to just add this to a dashboard. My published viz is here.
Ann Jackson made a special guest appearance this week, setting this challenge to introduce the newly released 2022.3 feature of dynamic zone visibility. As a consequence, a pre-requisite to completing this challenge is to install v2022.3 🙂
I used 6 sheets to build my solution, and I’ll step through each one, and then what’s required to put it all together.
Building the Sub-Category Picker
Building the Sub-Category Counter
Building the Bar Chart
Building the Line Chart
Building the Viz in Tooltip
Building the Dot Plot
Hiding & Showing the Charts
Adding & Removing Sub-Categories
Building the Sub-Category Picker
On a new sheet, add Sub-Category to Rows and change the mark type to circle. Right click on the Sub-Category field in the Data pane, and Create -> Set. Select the entries from Accessories down to Copiers. This will create a new field in the data pane called Sub-Category Set. Add this field to the Colour shelf, and adjust colours according to whether the values are In or Out of the set.
Add a grey border around the circles (via the Colour shelf) and increase the size a bit. Format the text of the sub-categories so its larger and right aligned. Remove all row/column dividers and hide field labels for rows to remove the Sub-Category column title (right-click on the column title). Adjust the Tooltip. Add a title to the sheet and then name the sheet Sub Cat Picker or similar.
Building the Sub-Category Counter
Create a new calculated field
Count Sub Cats Selected
COUNTD(IF [Sub-Category Set] THEN [Sub-Category] END)
If the Sub-Category is in the set, the return the Sub-Category and count the number of distinct entries.
Then create
Count Total Sub Cats
COUNTD([Sub-Category])
This just counts them all.
On a new sheet, add both fields to the Text shelf, and adjust the text accordingly.
Remove the tooltip so it doesn’t display. Name the sheet Set Count Label or similar.
Building the Bar Chart
Create a new field
Profit Ratio
SUM([Profit])/SUM([Sales])
and format to a % with 0 dp.
On a new sheet, add Profit Ratio to Rows and Sub-Category Set to Columns and also to Colour.
Right click on the text ‘In’ either on the colour legend or at the bottom of the bar, and Edit Alias. Change the text to SELECTED. Do the same thing for the text ‘Out’ and change to OTHER.
Add a row grand total (Analysis menu -> Totals -> Show Row Grand Totals). Adjust the colour of the grand total bar. Right click on the text ‘Grand Total’ and select Format. In the pane on the left hand side, change the Grand Total Label to read ALL PRODUCTS.
Create a calculated field
Overall PR
{SUM([Profit])}/{SUM([Sales])}
The { } make this a FIXED Level of Detail (LoD) calculation, so calculates over the complete data set.
Then create
PR Difference from Overall
[Profit Ratio]-SUM([Overall PR])
and format this to a custom number format of +0.0%;-0.0%;
Adding the second semi-colon implies there is a format for +ve numbers, -ve numbers and zero. In this instance we want zero difference to be displayed as blank.
Add both these fields the the Label shelf and adjust the font/layout accordingly, and match mark colour. Adjust the tooltip too.
Remove the profit ratio axis, remove all gridlines and row/column dividers. Add an axis ruler to the columns. Adjust the colour/size of the column labels. Hide the In/Out of Sub-Category Set column label (hide field label for columns). Add a title to the sheet and name the sheet Bar Chart or similar.
Building the Line Chart
On a new sheet, and Order Date to Columns and set to be a continuous (green) pill at the Quarter-Year level. Add Profit Ratio to Rows and Sub-Category Set to Colour.
Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card, and remove the In/Out Sub-Category Set pill from the colour shelf of the PR Per Quarter marks card. Manually adjust the colour of this line to the appropriate shade.
On the All marks card, click the Label shelf, and check the Show mark labels option and select line ends. Adjust the font of the labels to be smaller, bold and to match mark colour.
Right click on the PR Per Quarter axis on the right hand side and select move marks to back, the right click again and uncheck Show Header to hide that axis.
Right click on the bottom axis, and Edit Axis and remove the axis title. Edit the left hand axis and amend the title so its capitalised.
Format the font of both axis, so the text is smaller, and then remove all row/column dividers, and all gridlines and zero lines. Add axis rulers for both the rows and columns.
Then add a sheet title and subtitle and name the sheet Line Chart or similar. I use this site to get the circular symbols used in the subtitle.
Building the Viz in Tooltip
The line chart shows another chart on hover.
On a new sheet, add Order Date as a blue discrete pill set to the Quarter-Year level, to Rows then add Sub-Category Set to Rows too. On the Columns shelf, double click and manually type in MIN(1). Add Sub-Category Set to Colour, then edit the MIN(1) axis to fix it from 0 to 1.
Add subtotals (Analysis menu -> Totals -> Add all subtotals). This will add a Total row to each section. Manually adjust the colour of the Total bar if need be via the colour legend.
Right click on the text ‘Total’ in the chart, and format. Amend the Total label to read ‘ALL PRODUCTS’ instead.
Add Profit Ratio to the Label and ensure the font matches mark colour. You may need to adjust the font size and boldness, and expand the row height a bit to see the text.
Hide the Order Date column, adjust the font of the Sub-Category Set column to be darker/bolder and right aligned, and adjust the column width so all the text is displayed.
Hide the MIN(1) axis, remove all row/column dividers and hide the Sub-Category Set column label. Then set the sheet to Entire View, and name the sheet VIT or similar.
Return the Line Chart sheet, and on the Tooltip shelf of the All marks card, adjust the tooltip to display the Order Date and insert a reference to the VIT sheet via the Insert -> Sheets -> VIT option
Adjust the height and width to suit.
Building the Dot Plot
On a new sheet, add Sub-Category Set to Rows and Profit Ratio to Columns. Change the mark type to circle. Then add Product ID to the Detail shelf and Sub-Category Set to Colour. Add column grand totals and adjust the colour of the grand total if need be. Format the ‘Grand Total’ text so it reads ALL PRODUCTS.
To get a clearer idea of how many products there are, we are going to randomly spread the dots across a vertical y-axis. For this we create
Jitter
RANDOM()
This just returns a number between 0 and 1.
Add this field to Rows and change it to be a Dimension. Adjust the opacity of the Colour to 50%.
Hide the Jitter axis. Make the header column wider, so the text doesn’t wrap, and adjust the text to b bigger and bolder and right aligned. Hide the Sub-Category Set column heading. Adjust the size and title of the Profit Ratio axis. Remove all gridlines and column dividers.
Right click on the Profit Ratio axis and add a reference line, which is set per pane to the the Total of the Profit Ratio. Use the Value as label and set the line to be a dotted black line at 100% opacity.
Add Product Name to the Tooltip and adjust accordingly. Add a title and name the sheet Dot Plot or similar.
Hiding & Showing the Charts
We’re going to control which sheet displays by use of a parameter, so I created
pChartSelector
an integer list from 1-3 which are mapped to the 3 display values
Then create a dashboard sheet and using layout containers build out the dashboard. I used a horizontal container in the centre of my dashboard. Within that I used a vertical container to house the Sub-Category Picker and the Sub-Category Counter. Then the 3 charts (bar, line and dot plot) were arranged next to that. I fixed the width of the vertical container with the picker and counter. The pChartSelector parameter is then added at the top right. I made use of both inner and outer padding and background colours of pale grey and white to get the look as reqiured.
To make the hide/show functionality, I created the following fields
Show bar
[pChartSelector]=1
Show line
[pChartSelector]=2
Show dot plot
[pChartSelector]=3
I added Show bar to the Detail shelf of the bar chart sheet, Show line to the Detail shelf of the line chart sheet and Show dot plot to the Detail shelf of the dot plot sheet.
Then back on the dashboard, I selected the bar chart sheet (so it’s surrounded by a dark grey border), and on the Layout tab on the left hand side, I checked the Control visibility using value checkbox and selected the Show bar field
I then repeated this process, this time selecting the line chart sheet, and when I checked the Control visibility checkbox, I selected the show line field instead. this made the line chart disappear, since my parameter was set to ‘Compared to the Total’ which was equivalent to the parameter = 1 and not 2. Changing the parameter to ‘Over Time’ and my line chart showed and the bar disappeared.
Repeat the process again for the dot plot, selecting the show dot plot field instead. Now only 1 chart should display at a time.
Adding & Removing Sub-Categories
The final step is to add the interactivity to allow selection and removal of a sub-category when clicking on the circles of the Sub-Category Picker sheet.
First, you need to add a dashboard action which changes set values
Add Sub-Categories
Uses the Sub Cat Picker sheet as source and on Select targets the Sub-Category Set by Adding values to the set. The values are retained when the selection is cleared.
Then add another dashboard action to change set values. This one is called
REMOVE
Uses the Sub Cat Picker sheet as source and via the Menu targets the Sub-Category Set by Removing values from the set. The values are retained when the selection is cleared.
The title of REMOVE is what is then displayed in the text of the tooltip when a circle that has been added is the clicked again.
Phew!
Quite a lengthy post this week, but there’s a lot going on. My published viz is here.
It was Sean Miller’s turn this week to set the challenge, which had an added twist – to complete only using the web authoring feature in Tableau Public, something I’d never tried before.
Most challenges use a version of Superstore Sales which I usually already have on my laptop, due to the multiple instances of Tableau Desktop installed, but this time, I had to get the data from data.world. I saved this locally then logged into Tableau Public online and started the process to build a viz online, by clicking the Create a Viz (Beta) option.
When prompted I then chose to upload from computer, the data source file I’d downloaded, and once done I was presented with the online canvas to start building
Unsure as to whether there was any auto save facility online, I decided to save the workbook pretty much immediately, although I was first prompted to navigate to the Data Sourcetab and create an Extract before I could save.
Now I was ready to start building out the requirements.
I started with the trend chart. The grain of the chart was at the month level, so I first created a specific field to store the Order Date at this particular level
Order Date Month
DATE(DATETRUNC(‘month’,[Order Date]))
I then added this to the Columns shelf as a continuous (green) exact date, and added Sales to the Rows shelf. I changed the mark type to Area, set the Colour to a blue, dropping the opacity to 50% in an attempt to match Sean’s colouring and adjusted the tooltip.
I then added another instance of Sales to the Rows shelf next to the existing one, changing the mark type of this instance to Line and setting the Colour to a darker blue at 100% opacity. I then used the context menu on this pill to set a Quick Table Calculation of type Moving Average.
By default this sets the average to the 3-month rolling average as per the requirement. I adjusted the tooltip, made the chart dual axis and synchronised the axis, then removed Measure Names from the Colour shelf that had automatically been added.
My next step at this point was to work on the filtering requirement. To help with this, the first thing I did was to Duplicate as crosstab (right click on the chart tab). This created a tabular view of the data so I could work on getting the calculations I need for the filtering. I flipped the columns and rows, so my starting point was as below.
I chose to use parameters to capture the min and max dates that the user selects on the dashboard.
pMinDate
Date parameter defaulted to 01 Jan 1900
And I also created pMaxDate exactly the same way.
I then needed fields to store the relevant dates depending on whether a selection had been made or not
Min Date Selected
IF [pMinDate]=#1900-01-01# THEN {FIXED : MIN([Order Date Month])} ELSE [pMinDate] END
The FIXED statement sets the date to the minimum month in the data set, if the parameter is set to its default. The below doed similar to get the maximum date in the data set.
Max Date Selected
IF [pMaxDate]=#1900-01-01# THEN {FIXED : MAX([Order Date Month])} ELSE [pMaxDate] END
Using these dates, I then created a field to determine whether the month was within the min & max dates
Is Month Selected?
[Order Date Month]>=[Min Date Selected] AND [Order Date Month]<= [Max Date Selected]
Pop this field onto the table view, and show the parameters and set them to some other values eg Min Date = 01 Jun 2018 and Max Date = 01 Sept 2018. You’ll also need to adjust the table calc setting of the Moving Average Sales pill to compute by Is Month Selected as well.
You’ll see True is stamped against the rows which match the date range. Now, if you make a note of the Moving Average values against these rows, and then add Is Month Selected? = True to the Filter shelf, you should get just the True rows displaying, BUT the Moving Average values are now different. This is because, with this type of field as a filter, the filter is being applied BEFORE the table calculation is being applied – the table calculation essentially has ‘no knowledge’ of the rest of the rows of data as they have been filtered out. Solving this issue is the crux of this challenge.
Remove Is Month Selected? from the filter shelf. We’re going to create a new field to filter by instead
FILTER
LOOKUP(MIN([Is Month Selected?]),0)
This field uses the LOOKUP table calculation, and is basically looking at the value of the Is Month Selected? field in it’s ‘own row’ (denoted by the 0 parameter).
Pop this onto the table view, and you can see that it’s essentially a duplicate of the Is Month Selected? field.
Now add the FILTER =True field to the Filter shelf instead, and this time the rows will once again filter as required, but the Moving Average values will be the same as those in the unfiltered list, which is what we need.
This works because the field we’re filtering by is a table calculation. When added to the filter shelf, these types of fields are applied at the ‘end’ of the processing; ie the table calculation is computed against all the rows in the data AND THEN the filter gets applied. As a result the Moving Average values are retained.
This is all part of Tableau’s Order Of Operations and is a fundamental concept when working with filters.
Armed with this knowledge, we can now apply the FILTER=True to the Filter shelf of the chart we built.
Now we can start to work on the bar charts, which will show Sales along with Average Monthly Sales. For this 2nd measure, I need to work out the number of months captured between the min & max dates.
No. Months
DATEDIFF(‘month’, [Min Date Selected],[Max Date Selected])+1
And with this I can now create
Avg Monthly Sales
SUM([Sales])/MIN([No. Months])
The bar chart viz can then be quickly created by adding Sub-Category to Rows and both Sales and Avg Monthly Sales to Columns, and sorting by Sales descending. The colours of each bar are adjusted to suit.
The bars also need to react to the dates selected, but we can simply add the Is Month Selected? = True to the Filter shelf for this, as none of the values displayed on this chart are reliant on table calculations.
To remove the gridlines from both charts, select Format > Workbook from the menu, scroll down the Format dialog displayed on the right and set Gridlines to Off
Finally, title both sheets appropriately, and then add to a dashboard. We need to add 3 actions to this dashboard – 2 parameter actions and 1 filter action.
Create a parameter action which will on Select, set the pMinDate parameter by passing the MinimumOrder Date Month, and will reset back to 01 Jan 1900 when unselected.
Then create another instance, which sets the pMaxDate parameter instead, passing the Maximum Order Date Month.
Finally, create a Filter Action which on Select of the bar chart, filters the trend chart
And with that, the challenge should be complete. My published viz is here.
Luke Stanke returned for week 43 of #WOW2020 with a challenge focussed on building KPIs for mobile consumption.
In general this looked to be (and was) less taxing than some from previous weeks, but Luke did throw in some very specific requirements which did prove to be a bit tricksy.
To deliver this solution I built 8 sheets, 1 for each KPI heading and 1 for each bar chart. The dashboard then uses a vertical layout container to arrange the 8 objects in. A filter control on each bar chart determines whether the bar chart should ‘show’ or not. When a particular bar chart is displayed it fills up the space, which makes the display look to ‘expand’. Parameter actions are used to drive the ‘expand/collapse’ functionality.
The areas of focus for this blog are
Building the KPI chart
Formatting the Bar chart
Expand / Collapse function
Ensuring the KPI isn’t highlighted on selection
Making the display work for mobile
Building the KPI Chart
Because we have text on the left and the right, then I built this as a dual axis chart.
I’m going to build the Sales KPI.
I used MIN(1) on Columns, with Mark Type of bar, and fixed the axis to range from 0 to 1. SUM(Sales) is then added to Label, right aligned and formatted appropriately.
For the 2nd axis, we’re going to use MIN(0) positioned alongside MIN(1) on Columns, and this time, set the Mark Type to Gantt. I type the word ‘SALES’ into the Label field.
We also need to display a + or – icon on this label too. This will rely on a parameter that is going to be set.
Create a string parameter Selected Measure which is just empty.
For the icon, then create
Sales – Icon
IF [Selected Measure] = ‘SALES’ THEN ‘▬’ ELSE ‘✚’ END
I use this site to get the characters I use for these types of things.
Add Sales – Icon to the Label shelf of the MIN(0) axis, and position in front of the ‘SALES’ text.
If you show the Selected Measure parameter on the screen, and enter the word SALES, you’ll see the shape change to ‘-‘. Make this a dual axis chart and synchronise axis, and you should have the basis of the KPI (once all the axis have been hidden of course).
We also need to add an additional field onto the chart that we’ll need to use with the parameter action later.
Sales – String to pass
IF [Selected Measure] <> ‘SALES’ THEN ‘SALES’ ELSE ” END
When the Selected Measure parameter is empty (or contains another value), then this field will contain ‘SALES’ otherwise it’ll be blank.
Add this onto the Detail shelf of the ‘All’ Marks card (ie it needs to exist on both axes).
You essentially need to duplicate this sheet and build instances of the calculated fields for for Profit, Margin (SUM(Profit)/SUM(Sales)), and Customers (COUNTD(Customer ID)).
Formatting the Bar chart
Luke is very specific in his requirements that the axis tick for the bars displayed needs to be centred. This means there needs to be a continuous (green) pill on the date axis.
This very tiny requirement gave me a lot of grief 😦
I initially used MONTH(Order Date) (which will return numbers 1-12), but when I formatted the axis to First Letter, I get a D (for 0) and J (for 13) at each end.
Fixing the axis to start from 1-12 doesn’t work, as this chops off part of the first and last bars.
I tried a variety of mechanisms but to no avail. A chat with my fellow #WOW participant Rosario Gauna, who was also mulling over the issue eventually provided a solution, courtesy of Annabelle Rincon.
We need to use an ‘undocumented’ formatting option of mmmmm, which is the formatting for first letter. However, this doesn’t work with the above. I need to change the axis to use an actual date field (which was one of the mechanisms I’d already tried).
This is taking every date in the data set, and ‘baselining’ it to all be on the same year (I chose 2019, but this could be any year you choose). The DATETRUNC is then setting all dates within the same month to be reported as being the 1st of the month.
Adding this to Columns instead as an exact date, and then formatting the axis and changing the Dates option to mmmmm will display the 1st letter
There is also a requirement to just show a label and tick mark for every other month, starting in Feb. You need to edit the axis, and on the Tick Marks tab
Set Major Ticked Marks to Fixed
Set the Tick Origin to 01 Dec 2018, and the interval to every 2 months (2 months after 1st Dec 2018 is 1st Feb 2019, so the F displays while the J for Jan doesn’t)
Set Minor Tick Marks to None
An additional ‘formatting’ setting I used on the bar charts was to fix the vertical axis to a number high enough to ensure the label always displayed after the bar and not in it. What this needs to be set to will vary and is only really noticeable once added to the dashboard, so may require some tweaking to get it correct.
Once again, you’ll need an instance of this bar chart for each of the measures Sales, Profit, Margin & Customers.
Expand / Collapse Function
On the dashboard, you need to use a vertical layout container, and place all the sheets in order underneath each other, so you have KPI, bar, KPI, bar etc.
Each bar chart needs a filter which will determine whether it shows any data or not. Create a calculated field
FILTER : Selected Measure
[Selected Measure]
On the Sales bar chart, make sure the Selected Measure parameter contains the value ‘SALES’, then add the Filter: Selected Measure to the Filter shelf and select the value SALES (it’ll be the only option available)
Then go to the Profit bar chart, and change the Selected Measure parameter to PROFIT. Now add the FILTER : Selected Measure to the Filter shelf and select the value PROFIT. If you switch back to the Sales bar, you’ll find the display is empty. Repeat this for the MARGIN and CUSTOMERS bar charts.
If you return to the dashboard you should find you probably have the 4 KPIs displayed and 1 bar chart showing. The final step is to add parameter actions.
For each KPI sheet, create a parameter action that targets the Selected Measure parameter by passing the relevant <Measure> – String to Pass field.
Ensuring the KPI isn’t highlighted on selection
I’ve used the True = False concept which I’ve applied multiple times to previous challenges.
Create a calculated field called True that just stores the value of True and and a calculated field called False that stores the value False. Add both of these to the Detail shelf of teach KPI viz.
Then on the dashboard, add a Filter action for each KPI sheet that goes from the KPI object on the dashboard to the sheet itself, passing the values Source: True = Target: False
Making the display work for mobile
This frustrated me no end. When I click on the Phone layout option (right image below), the layout container just doesn’t behave as it does on the Default view – the space for the bar charts is retained even when there’s no data.
I tried all sorts of combinations of containers to try to resolve this, and just couldn’t get it, and I struggled to find anything online that would help. I published to Tableau Public to test what the result would actually look like on my mobile, in the vain hope it might ‘just work’ but it didn’t 😦
When Sam Epley posted his solution, and I found it worked on mobile, I had to look. and found that you could just ‘delete’ the phone layout.
A couple of gems in this one that I need to store away for future! My published viz is here.
Donna + DataViz 