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
For #WOW2025 Week 25, Kyle challenged us to make use of Set Actions to recreate a viz where the ‘viz in tooltip’ updates based on the option the user interacts with on the base viz.
Modelling the data
The excel file provided contained 3 sheets which needed to be combined to use for this challenge. My data model looks like this
Attendance is related to Divisions on the fields Tm = Team
Attendance is also related to Record on the fields Tm = Tm.
Building the Base Bar Chart
On a new sheet, add Division to Rows and Attend/G to Columns changing the aggregation to AVG.Sort the data descending.
Change the Colour of the bars to dark grey and show mark labels to display the average attendance per game value. Add W-L% and Est.Payroll fields to the Tooltip shelf and set both to be AVG. Format Est. Payroll to be $ with 0dp. Format W-L% to be formatted to 3dp, but then adjust again and use a custom format to remove the leading 0 (,##.000;-#,##.000)
Format the label to be bold and to match mark colour. Format the row labels, remove the row label heading (right click > hide field labels for rows). Hide the axis and remove all gridlines, axis rulers etc. Update the viz title and name the sheet Bar-Division or similar.
Build the Scatter Plot
On a new sheet add W-L% to Columns and Attend/G to Rows, setting both the use an AVG aggregation, and then add Tm (from the Attendance table) to Detail. Adjust the W-L% axis so it doesn’t always include 0 (right click axis > edit axis> uncheck include zero). Adjust the title of the axis too. Adjust the title of the Attend/G axis too. Change the mark type to circle and increase the size.
We need the chart to show a difference between the marks related to a selected Division and those which aren’t. Create a set from the Division field (right click the field > create > and select NL West.
Add Division Set to Colour and adjust accordingly. Add a dark grey border to the circles. Remove all gridlines and name the sheet Scatter or similar.
Build the Attendance by Team Bar Chart
On a new sheet, add Tm (from the Attendance table) to Rows and Attend/G to Columns, setting the aggregation to AVG. Sort descending. Add Division Set to Colour.
Create a new field
Label Attendance
IF [Division Set] THEN [Attend/G] END
and add to the Label shelf. Format the label so it is bold and set to match mark colour. Hide the row label heading and the axis. Hide all gridlines, zero lines et. Name the sheet Bar-Team or similar.
Adding the Viz in Tooltip
Navigate back to the Bar-Division sheet. Update the Tooltip to reference the Division and the other top level measures. I used a double tab between the headings on one line and again on the values on the next line to make the information line up on hover (even though they look misaligned on the tooltip dialog).
To add each sheet to the tooltip, use Insert > Sheets button in toolbar and select the Scatter sheet and then press tab and select the Bar Team sheet.
Then adjust the text inserted so both filtering sections state filter=”None”. This stops the VIT filtering out by default all the data that isn’t associated to the selection you’re coming from. Adjust maxwidth and maxheight to 350 (I had it set larger, but then it didn’t display properly on Tableau Public, so had to adjust there.
Adding the interactivity
Create a dashboard and add the Bar-Divison sheet. At this point showing the tooltip on each bar will always show the information related to NL West since that was the option selected when we created the set. To fix this, create a new dashboard set action
Set Division
On hover of the Bar-Division sheet, target the Division Set, and assign values to the set. When the selection is cleared, keep set values. Only allow this to be run on single-select only
And now as you hover over different bars, the highlighted circles and bars in the viz in tooltip will change.
Sean’s #WOW2024 challenge this week was to visualise the comparisons between different entities within a limited space, by using a Viz In Tooltip (VIT) to provide additional information on hover.
Building the Treemap
Add Sales to Size, Category to Colour and Sub-Category to Detail to create the basic tree map. Tableau will automatically define the layout based on the space available, and you can’t control this, so don’t worry if it doesn’t match the final output.
Move Sub-Category from Detail to Label and add Sales to Label too. Format Sales to to be $ at 0dp, then adjust the Label as required.
Crete a new field
Rank
RANK(SUM([Sales]))
Format the field so it is formatted to be a whole number with a suffix of .)
Convert to discrete and add to the Detail shelf. Adjust the table calculation and verify it is computing by both Category and Sub-Category – this ranks every cell from 1 to 17 based on the Sales.
Adjust the Tooltip to just show the Category, Sub-Category and Sales data.
Name the sheet TreeMap.
Building the bar chart (the VIT)
On a new sheet add Sub-Category to Rows and Sales to Columns. Sort by Sales descending. Add Category to Colour and adjust the opacity to around 30%. Manually widen each row.
Add Sales and Sub-Category to Label and adjust to get the correct layout and then align left middle.
Add Rank to Rows after Sub-Category. Hide the Sub-Category field (uncheck show header).
We need to identify a row that has been ‘selected’ by the user. For this we need a parameter
pSelectedRank
Integer parameter defaulted to 0
Show the parameter on the page. We need to indicate which row is selected.
Selected Rank Indicator
IF [Rank] = [pSelectedRank] THEN ‘●’ ELSE ” END
Add this field to Rows before Rank, then update the pSelectedRank parameter to a valid number, eg 5.
Hide field labels for rows, make the Selected Rank Indicator and Rank columns narrower. Adjust alignment of columns and increase the font size of the Selected Rank Indicator column.
Hide the Sales axis, remove all gridlines, zero lines and row & column dividers. Format the Row Axis Ruler to be a black line.
We only want a subset of the rows to show – those that are 2 below and 2 above the selected rank. Create a new field
Rank in Range
([Rank] >= [pSelectedRank]-2) AND ([Rank]<= [pSelectedRank]+2)
Add this to Rows in front of Sub-Category. The bar chart will split.
We only want to see the ‘True’ rows. You can do this in 2 ways.
1 – Click on the word True and select Keep Only from the dialog box displayed. This will add Rank in Rangeto the Filter shelf
or
2 – right click on the section with the False records and select Hide from the context menu. The rows associated to False will disappear but won’t actually be filtered out of the view (this is what I did)
Hide the Rank in Range column from showing (uncheck show header). Name this sheet VIT or similar.
On the TreeMap sheet, update the Tooltip to show the VIT worksheet (via Insert > Sheets). Adjust the code snipped inserted so the filter property = “” (ie no filters are passed and the complete viz is displayed).
If you test it from the worksheet, and still have the pSelectedRank set to 5, you should see the same set of bars regardless of which part of the TreeMap you hover on.
Applying the filter
Create a dashboard of the required size and add the TreeMap sheet. Set the viz to fit entire view. It’s likely it will now rearrange itself to (nearly) match the solution.
Add a parameter dashboard action to set the selected rank
Set Rank
On hover of the TreeMap sheet, set the pSelectedRank parameter by passing in the value from the Rank field aggregated to nothing. When the selection is cleared, set to 0.
Hovering over the dashboard should now update the display in the VIT to ‘focus’ on whichever Sub-Category has been selected on the TreeMap.
As community month draws to a close, long term participant Deborah Simmonds set us a map based challenge based on TC23 data.
I chose to use the data that was associated to the solution workbook, rather than take the direct source, so bear that in mind if you’re following along.
Examining the data
As there were quite a few fields in the data set which were directly related to the solution, I just wanted to familiarise myself with what I was working with initially. Essentially we have a row per person (Names Full) with the hotel they stayed at (Label Hotels), and how many steps (Steps) they took each day (Date). The details of the convention centre (Label Convention Centre) exist against every row.
The LAT and LON fields contain the location details of the hotels, while Convention LAT and Convention LON contain the location details of the Mandalay Bay Convention Centre.
Names Full already had the logic applied to create names for users based on their User ID if the Name was NULL, so I didn’t need to do anything for that requirement.
We only want to consider records relating to those people who attended ‘In Person’ and who had provided a hotel. Consequently I added the following as data source filters (right click data source -> edit data source filters) to exclude unrequired records from the whole analysis.
Did you attend in person or virtual? : In person
Label Hotels : excludes NULL
Building the BANs
The BANs have 3 metrics – the number of attendees, the number of steps, and the distance in metres (based on the logic that 1 step = 0.75m).
Attendees
COUNTD([User ID])
Distance (m)
[Steps] * 0.75
On a new sheet add Date to Filter as a range of dates and show the filter control. Add Attendees, Steps and Distance (m) so the measures are displayed in a row.
Change the mark type to shape and add a transparent shape (see this blog for more details). Add Measure Names to Label too and then adjust label accordingly and align centrally.
Hide the header row (uncheck show header), remove row dividers and don’t show tooltips. Name the sheet BANs or similar. Adjust the date slider and the values should adjust.
Building the bar chart (viz in tooltip)
On a new sheet add Names Full to Rows and Steps to Columns. Apply the same Date filter (from the BANs worksheet, set the filter to apply to this new worksheet too).
The viz needs to display a reference line showing the overall average of the steps per person across the selected dates, regardless as to whether the records are filtered to a hotel or not.
What do I mean by this… well if I add a standard average reference line to the viz above, the average for the whole table is 78.2k steps.
If I now filter this by a Label Hotel, then the average changes, and I don’t want that – I still want to see 78.2k.
But the average does need to change if the date range changes
This took a bit of effort to get right, but I needed
Format this as a number with 1dp set to the K (thousandths) level
and I also needed to add the Date field on the Filter shelf to context.
So reverting back to the initial view of the bar chart… right click on the Date filter and Add to Context. Add Avg Steps to the Detail shelf. Then add a reference line (right click the Steps axis > add reference line) that displays the Avg Steps with a dark dashed line with a custom label.
Format the reference line to position the label at the top and adjust the font style.
To colour the bars we need
Steps above average
SUM([Steps]) >=[Avg Steps]
Add this to the Colour shelf, change the colours and adjust the opacity to about 75%.
Hide the column heading, and adjust the font size of the names and the axis. Name the sheet Bars or similar.
Building the initial map
I decided to create 2 maps – one for the initial display of all the hotels and the convention centre, and then one for the selected location and buffer.
To plot the hotels on the map I created
Hotel Locations
MAKEPOINT([LAT],[LON])
On a new sheet, add this to the Detail shelf. A map will automatically generate with Latitude and Longitude fields. Change the Mark Type to Circle, then add Label Hotels to Label and align left middle. Edit the map Background Layers (via the map menu) to add Streets, Highways etc to the display
Add Steps, Attendees and Distance(m) to the Tooltip. Add Attendees to the Size shelf and adjust the size to vary by range
Apply the Date filter from the other worksheets to this sheet too. For the tooltip, We need to know about the min and max dates in the range selected. Create
Min Date
MIN([Date])
and custom format simply as dd (the day only)
Also create
Max Date
MAX([Date])
and custom form this as dd mmm yyyy
Add both of these fields to the Tooltip shelf. Adjust the text in the Tooltip and add a reference to the Bars sheet as a viz in tooltip (Insert > sheets > ). Adjust the height of the sheet to be 900.
The viz in tooltip should now display nicely on hover
To add the mark for the convention centre, we need
Conf Location
MAKEPOINT([Convention LAT], [Convention LON])
Drag this onto the map, and drop it when Add A Marks Layer displays
This will create a 2nd marks card. Change the mark type to shape and select the Tableau sparkle image if you have it stored. If not, just use another shape or circle (coloured differently).
Add Attendees to Size and add Label Convention Centre to Label and align left middle. Add Steps and Distance(m) to Tooltip and adjust to suit. Hide all the map options (map menu -> map options -> uncheck all the selections). Name the sheet Map – Initial or similar.
Building the ‘selected’ map
This will use parameters to identify what’s been selected – a hotel or the convention centre, so we need
pSelectedHotel
string parameter defaulted to empty string
and
pSelectedCentre
string parameter defaulted to empty string, just like above
The intention is that either both these parameters will be empty or only one will be populated.
To plot on a map we need
Selected Hotel Location
IF [pSelectedHotel] = [Label Hotels] AND [pSelectedCentre] =” THEN [Hotel Locations] END
and
Selected Centre Location
IF [pSelectedHotel] = ” AND [pSelectedCentre] =[Label Convention Centre] THEN [Conf Location] END
Duplicate the initial map sheet and name it Map – Selection. Show the two parameters and verify both are empty.
On the Hotel Locations marks card, drag the Selected Hotel Location field and drop it straight onto the Hotel Locations field. On the Conf Location marks card, drag Selected Centre Location and drop straight onto the Conf Locations field. Your map shouldn’t display anything…
Manually type ‘Luxor’ into the pSelectedHotel parameter. A mark should display. Adjust the Label of the Selected Hotel marks card so it is larger font, and aligned top middle. Set the colour to orange and remove the halo.
Remove the text from the pSelectedHotel parameter and manually type ‘Mandalay Bay Convention Centre’ into the pSelectedCentre parameter. A mark should display. Adjust the Label of the Selected Centre marks card so it is larger font, and aligned top middle. Remove the halo from the Colour shelf.
To create the buffer circle, we need to define the buffer radius, which is the attendee steps in metres.
Buffer Distance (m)
{FIXED : SUM(IF [pSelectedHotel] = [Label Hotels] OR [pSelectedCentre] = [Label Convention Centre] THEN [Steps] END)} * 0.75
and then we create
Buffer
IF [pSelectedHotel] <> ” THEN BUFFER([Selected Hotel Location], [Buffer Distance (m)], ‘m’) ELSEIF [pSelectedCentre] <> ” THEN BUFFER([Selected Centre Location], [Buffer Distance (m)], ‘m’) END
Add this as another marks layer.
Reduce the opacity on the colour shelf to 0% and set the border to be orange. We don’t want to allow any interactivity with the buffer, so disable selection of the layer, and also move down so it is listed at the bottom of the 3 map layers.
Show the Date filter control and adjust the dates to see the buffer adjusting. Test the behaviour with a hotel too (you may find you want to add some more detail to the background layers of the map).
We will use dynamic zone visibility on the dashboard to decide whether to display the initial or the selected map. To control this, we need
Show Initial Map
[pSelectedCentre]=” and [pSelectedHotel]=”
and
Show Selected Map
[pSelectedHotel]<>” OR [pSelectedCentre]<>”
Adding the interactivity
Create a dashboard, add the BANs and both the map sheets.
Create a dashboard parameter action
Set Hotel
On selection of the Initial Map set the pSelectedHotel parameter passing in the value from the Label Hotels field. When the selection is cleared, reset to ”.
and another parameter action
Set Conv Centre
On selection of the Initial Map set the pSelectedCentre parameter passing in the value from the Label Convention Centre field. When the selection is cleared, reset to ”.
Select the Initial Map object, and from the Layout tab, set the visibility to be controlled by the Show Initial Map field
Then select the Selected Map object, and set the visibility to be controlled by the Show Selected Map field. Only one of the maps should display based on the interactivity.
This is all the core functionality of the map, but Deborah threw in a couple of extra asks…
Building the Distance Legend
We’re using map layers again for this. Create a new field
Zero
MAKEPOINT(0,0)
Add it to the Detail shelf of a new worksheet to create the 1st map layer, then immediately add another map layer, by adding another instance of Zero to the sheet.
Switch the axis, and the map will disappear, and you’ll have axis displayed instead. Change the mark type of the first map layer to circle and colour orange.
Change the mark type of the 2nd map later to circle and colour pale grey with an orange border. Increase the Size of this circle so it appears as a ring around the filled orange circle. Move the 2nd marks layer down to the bottom and disable both marks from being selectable.
Hide the axis and gridlines/zero lines.
Right click on the central circle and annotate point. Don’t enter any text into the annotation box, just click OK. You should get the annotation box with a line.
Move the box so the connector line is horizontal, then format the annotation so the shading is set to none and the line is formatted to be a darker dashed line. Update the title of the sheet.
Building the Size Legend
Apply a similar process to that described above, but this time create 3 mark layers where the mark type is an open circle shape which is coloured blue, and for each layer, the size of the circle is slightly bigger. This time show the zero lines.
Set the background of both the legend sheets to be none (ie transparent), then add them as floating objects onto the dashboard. Use the control visibility feature to only display the Size legend when Show Initial Map is set, and only display the Distance legend when Show Selected Map is set. Set a background against each object of light grey, that is then set to 80% transparency.
With this you should have a completed challenge. My published version is here.
The first part of the challenge involves modelling the data. Since I’d blogged a solution guide to the original 2021 challenge here, I thought I’d refer myself to my own blog. Re-reading it though, I found I originally had some issues getting all the data sources pivoted in the way I needed, and ended up having to create the csv files as extracted hyper files separately before putting them together. I encountered the same issues again (I was hoping that ‘maybe’ it had been a version problem).
However, I then watched the solution guide that was posted on the old blog page that Lorna had provided, and I found where the problem was.
When I was trying to add connections to the additional data sources into Tableau, I was using the Add option, browsing for the file, and then dragging it into the pane, where I was then unable to pivot it
However, as the csv files I needed are all located in the same directory, the files were already listed on the left hand pane, and dragging from there allowed me to do what I needed, and I’ll talk through that now. Why, adding via the Add button does not work, I don’t know….
The data provided consists of 4 files
Life Expectancy csv
Population csv
Income csv
Geographies (Region Mapping) excel file
The first 3 files are stored as a matrix of Country (rows) by Year (columns) with the appropriate measure in the intersection. This isn’t the ideal format for Tableau so the data needs to pivoted to show 3 columns – Country, Year and the relevant measure (life expectancy, population or income depending on which file is referenced).
In Tableau Desktop, connect to the Life Expectancy file and check the Use Data Interpreter checkbox, so the top row of the file is understood to be the column headings.
Now we need to pivot this data; click on the 1800 column, then scroll across to the end, press shift and click on the final column to select all the year columns. While highlighted, right-click and select Pivot. Your data will be reshaped into 3 columns.
I then renamed each column as
Country
Year
Life Expectancy
Change the datatype of the Year column to be a number (whole), as we’ll need to relate the data on this field later, and working with numeric data is more efficient than strings.
Now from the left hand Files pane, drag in the Population file. By default it will pick up a relationship based on the Country field in each file
Once again, multi-select the columns from 1800 across to 2100, and pivot. Rename the fields to Year – Population and Population and change the datatype of the Year – Population field to a whole number. Add an additional relationship on Year = Year – Population
Next, drag in the Income file from the left hand pane and link to the Life Expectancy file. By default it should pick up a relationship based on the Country field in each file (if not add it). Once again pivot the date fields, and rename the fields Year – Income and Income. Change the data type of the Year – Income field to a whole number, and add an additional relationship on the Year fields.
Finally, using the Add option, add a connection to the Geographies/Region excel file and drag the list-of-countries-etc sheet onto the canvas and link to the Life Expectancy file. Add a relationship from County to Name.
Now the data is modelled, we can build out the viz.
Building the Scatter Plot
We only need to show information for the years up to the ‘current’ year. I created a parameter to represent ‘Today’, essentially hard coding a date.
pToday
date parameter defaulted to 16 Jan 2024
I then created a field
Year <= Current Year
[Year] <= YEAR([pToday])
and added this to the Filter shelf of a new worksheet and set the value to True.
Change the Year field to be discrete (right click > convert to discrete),then add to the Filter shelf, select All Values and then select 2024 from the list. Show the filter on the canvas, and change to a single value (dropdown) that displays only relevant values. Also Customise so the ‘All’ value does not show. Only options from 1800 – 2024 should be listed.
Create a new field to get the regions in the correct format
Region
UPPER(REPLACE([Eight Regions],’_’, ‘ ‘))
Now add Income to Columns and Life Expectancy to Rows and add Country to Detail and Region to Colour and adjust accordingly. Change the mark type to circle. Add Population to Size and adjust. Set the opacity of the colour to around 70%.
If you examine the Income axis on the solution, you’ll see the scale isn’t uniform. This is because it’s using a logarithmic scale instead, which you can set by right-clicking on the Income axis -> Edit Axis and selecting the relevant checkbox. Also, untick the Include zero checkbox, and the display should now start looking more like what’s expected.
Add Year to the Tooltip shelf and update the tooltip. Format the Year field so it’s a number with 0dp that does not include the thousand separators.
Also format the Population field to be a number with 1dp displayed in millions, and format the Life Expectancy field to be a number to 1dp.
Adjust the display of the Life Expectancy axis so that it is displayed without the decimal place. Right click on the axis > format, and on the axis tab on the left hand side, format the numbers to be Number standard
We need to be able to adjust the axis based on selection, so need to set the axis to be able to adjust. For this we will need parameters.
pIncomeMin
integer parameter defaulted to 500
pIncomeMax
integer parameter defaulted to 100,000
pLifeExpectancyMin
integer parameter defaulted to 0
pLifeExpectancyMax
integer parameter defaulted to 100
Right click on the Income axis and edit and set the range to be custom, selecting the pIncomeMin and pIncomeMax parameters
Do the same for the Life Expectancy axis, selecting the relevant parameters.
Hide the null indicator and name the sheet Scatter or similar.
Building the Viz in Tooltip
On a new sheet, add the Year <= Current Year field to Filter and set to True.
Then add Year as a continuous dimension field (green pill) to Columns and Country, Income, Life Expectancy and Population to rows. Add Region to Colour.
Edit each of the Income, Life Expectancy and Population axis in turn and select the Independent axis range for each row or column option.
Hide the Country column (uncheck show header) and remove all gridlines, zero lines, axis lines. Set the display to Entire View and name the sheet VIT or similar.
Back on the Scatter worksheet, edit the Tooltip and add a reference to VIT sheet, adjusting the height and width of the sheet to suit (after a bit of trial and error I used 700 x 450) and setting the filter to Country.
When you hover over a mark, the VIT chart should also be displayed, filtered to the country related to the mark hovered on.
Building the Legend
Create a simple ‘table’ with Region on Columns, Colour and Text. Hide the column heading (uncheck show header) and remove all row & column dividers. Align the text centrally and adjust the font to suit.
Name the sheet Regions or similar.
Adding the interactivity
Arrange the sheets on a dashboard and ensure the Year filter is displayed as a single value drop down that only shows relevant values and doesn’t show the All option.
To filter the chart by the Region, add a filter dasboard action
Filter Region
On select of the Region sheet on the dashboard, target the Scatter sheet on the dashboard, passing the selected fields of Region only. Show all values when the region is unselected.
To allow the chart to zoom in, we need to set the parameters referenced in the axis by using parameter actions.
Income-MinSelected
On selection of marks on the Scatter sheet, update the pIncomeMin parameter using the Minimum value of the Income field. When the selection is cleared, reset the field to 500.
Income-Max Selected
On selection of marks on the Scatter sheet, update the pIncomeMax parameter using the Maximum value of the Income field. When the selection is cleared, reset the field to 100,000.
Create 2 further parameter actions similar to above but referencing the pLifeExpectancyMin and Max parameters and resetting to their defaults of 0 and 100 accordingly.
Once done, the viz should be complete. My published version is here.
Note – I found that after publishing from Desktop to Tableau Public, the ‘zoom’ interactivity was lost, and when I edited my viz on Tableau Public the axis had lost their references to the parameters. I updated and republished the viz from Tableau Public. I don’t know why this happened, and whether it’s a known issue, but thought worth noting in case you encountered the same issue.
This week’s #WOW2023 challenge was inspired by Sam Parson’s TC presentation where he demonstrated the concept of an interactive Viz in Tooltip (the workbook he presented is here).
I was aware when I set this challenge, that this was likely to be on the higher end of the difficulty scale, but WOW challenges to me have have always provided a source of inspiration and ideas to take forward into my day job. And by blogging the solutions, I provide myself with a guide to refer to when the need arises. When Sam presented the concept, I immediately wanted to understand how he’d done it, and by setting it as a challenge it provided me with the opportunity to dig into it, and get that documented ‘how to’ guide 🙂
As mentioned in the requirements, I built this using multiple sheets, so we’ll start by just building out most of those sheets.
Building the Scatter Plot
We’re only concerned with data over the last 2 years, so we need to define some measures relevant to these years
Current Year
ZN(IF YEAR([Order Date]) = YEAR({MAX([Order Date])}) THEN [Sales] END)
{MAX([Order Date]} is a Fixed Level of Detail calculation which returns the maximum date in the data set. This calculation is then comparing the Year associated to that date with the Year of each Order Date, and if they match, return the Sales. Wrapping in a ZN ensures a value of 0 in the event there are no Sales.
Prior Year
ZN(IF YEAR([Order Date]) = YEAR({Max([Order Date])}) -1 THEN [Sales] END)
YEAR({Max([Order Date])}) -1 returns the year associated to the latest date then decrements by 1 to get the value of the previous year.
We also need to calculate the difference between the sales across the 2 years and categorise based on the difference
Sales Performance
IF SUM([Current Year]) / SUM([Prior Year]) > 1.1 THEN ‘Increasing’ ELSEIF SUM([Current Year]) / SUM([Prior Year]) < 0.9 THEN ‘Decreasing’ ELSE ‘Static’ END
If the current year sales > 10% of the previous year sales then flag as ‘increasing’, else if current month sales < 90% of the previous year sales then flag as ‘decreasing’ else flag as ‘static’.
Add Prior Year to Columns and Current Year to Rows. Add Manufacturer, Sub-Category and Category to Detail. Change the mark type to Circle. Add Sales Performance to Colour, adjust colours to match and reduce opacity to around 80%. Re-order the colour legend to display Increasing at the top and Decreasing at the bottom. Name the sheet Scatter.
Building the bar chart
On a new sheet add Order Date at the discrete (blue) Month level to Columns. Add Current Year to Rows. Change the mark type to bar and add Sales Performance to Colour. Reduce the opacity of the colour to 80%.
Add Prior Year to Rows. Change the mark type on the Prior Year marks card to gantt bar. Remove the Sales Performance pill from the colour shelf on this marks card. Adjust the colour to black.
Make the chart dual axis and synchronise axis.
Hide the axis, remove all gridlines & row/column dividers. Format the months to be abbreviated to the first letter. Right click on the Order Date label at the top and hide field labels for columns. Set the sheet to Entire View. Name the sheet Bar.
Building the KPI
On a new sheet add Current Year to Text. Adjust the format (size & colour of font) and align middle centre. Set the sheet to Entire View. Name the sheet KPI.
Building the % Change Indicator
Firstly we need to capture the value of the % change in sales
On a new sheet, add Sales Performance % Change and Prior Year to Text. Change the mark type to square and increase the size to as large as possible. Set to Entire View. Adjust the font size of the text to match the display and align middle centre. Add Sales Performance to Colour. Name the sheet % Change
Identifying the data to filter
The final sheet we need is one to serve the title of the Viz in Tooltip (ViT). It includes references to information related to the mark selected on the scatter plot, namely the Category, the Sub-Category and the Maufacturer. Before building this title sheet though, we need to understand how we’re going to identify the mark selected so we can filter other sheets based on it.
Typically, for most use cases, when you add a worksheet to the tooltip of another sheet, you want to ‘filter’ what’s displayed in the ViT based on the mark you’re hovering/clicking on.
So by default, when you add a worksheet via the Viz in Tooltip functionality (see Tableau KB here for more info), the markup that’s automatically added looks like below
<Sheet name=”My ViT Sheet” maxwidth=”300″ maxheight=”300″ filter=”<All Fields>”>
where the filter property is set to <All Fields> which is the instruction to pass information from the ‘parent’ sheet through to the ViT sheet.
For this challenge however, when a user first hovers on a mark on the scatter plot (the parent sheet), the information displayed in the Viz in Tooltip (ViT) sheets is for the whole unfiltered data set (ie display information related to the Current Year and Prior Year Sales across all manufacturers, sub-categories & categories). But once selected (clicked on) the information displayed in the ViT should be filtered just to that mark.
For this to work, we can’t use the filter property of the ViT markup. That property needs to be set to “” to ensure the resulting sheets aren’t filtered ‘on hover’. So we need another way to drive the filtering behaviour.
We need to use sets.
We’re going to use sets to capture the Manufacturer, Sub-Category and Category of the mark that is clicked on. So to start, right click on Manufacturer > Create > Set and select all values.
Manufacturer Set
Repeat the same steps to create a Category Set and a Sub-Category Set.
Create a dashboard and add the Scatter sheet to the dashboard. Then create dashboard set actions (Dashboard Menu > Actions > Add Action > Change Set Values)
Select Manufacturer
On select of the Scatter sheet on the dashboard, target the Manufacturer Set, assigning values to the set when the action is initiated, and adding all values to the set when the action is cleared.
Note – ‘assign value to set’ will replace any values already in the set (ie all values) with the relevant value based on the selection made, whereas ‘add values to set’ just appends the selected value to the values already in the set.
Repeat the above steps to create set actions for the Category Set and the Sub-Category Set
Navigate to the Bar sheet. Add Category Set, Sub-Category Set and Manufacturer Set to the Filter shelf, and click on each pill and Show Set to list the sets and their selected values on the left hand side (this is just so you can see what’s going on).
Initially you can see all values in all the sets are selected. Now navigate back to the dashboard and click on a single mark. Then come back to the bar sheet and check the results…
You should see a change to the bars as they are now being filtered by only the values which have been assigned to the set via the ‘on click’ action.
Building the title sheet
Now we have the sets established, we can build on these to generate the information needed for the title sheet.
To start with, we need to understand how many values have been captured in each set.
Count Categories
COUNTD([Category])
Count Sub-Categories
COUNTD([Sub-Category])
Count Manufacturers
COUNTD([Manufacturer])
Then we need to build up a title based on what’s been selected
Title
IF [Count Manufacturers] = 1 THEN MIN([Manufacturer]) ELSEIF [Count Sub-Categories] = 1 THEN ‘All ‘ + MIN([Sub-Category]) ELSEIF [Count Categories] = 1 THEN ‘All ‘ + MIN([Category]) ELSE ‘All Manufacturers’ END
and a sub title
Sub Title
IF [Count Manufacturers] = 1 AND [Count Sub-Categories] = 1 AND [Count Categories] = 1 THEN ‘(‘ + MIN([Category]) + ‘ > ‘ + MIN([Sub-Category]) + ‘)’ ELSE ‘* ‘ + STR([Count Manufacturers]) + ‘ Manufacturers across ‘ + STR([Count Sub-Categories]) + ‘ Sub-Categories’ END
On a new sheet, add Title and Sub Title to Text. Format the background of the worksheet to be orange, set to Entire View, then adjust the text and font format and align top left. Name the sheet Title.
Now navigate back to the Bar sheet, and for each of the fields in the Filter shelf (the set ones), make them apply to selected worksheets, KPI, % Change, and Title
As a result, across all 4 sheets (not the Scatter one), you should have the 3 set fields as filters with the ‘multiple worksheet’ symbol indicating a shared filter.
If you go back to the dashboard and click on a mark then check the Title sheet, the information displayed should update.
Building the Viz In Tooltip
Now we have (most of) the components we need, let’s start to put together the actual ViT.
On the Scatter sheet, click on the Tooltip button to open the Edit Tooltip dialog.
Start by deleting all the text.
Then from the toolbar, click Insert > Sheets > Title to add the Title sheet to the tooltip. You should have something like
The key to getting the tooltip to display ‘nicely’ is to consider the height and widths, and align the markup text. Sometimes this does take a bit of trial & error and can also look differently when published to Tableau Public.
Adjust the above, so the maxwidth =500 and maxheight = 100, filter = “” and the whole line of text is centred.
Then add the other 3 sheets, using carriage returns to add space between the sheets as required, and adjusting the heights and widths.
If you go back to the dashboard and hover on a mark, you should see the display below for All Manufacturers
and if you then click on a mark, the display should adjust to filter
Making the ViT interactive
Edit the Tooltip on the Scatter sheet, and add a section at the bottom that references the Category , Sub-Category and Manufacturer fields (add via the Insert menu again). Style the font as you wish
Now if you go back to the dashboard and click on a mark, you can then also click on one of the links added at the bottom. In this instance I clicked on the Chairs link and all the marks in the scatter plot related to chairs were highlighted and the ViT data all updated to show the values associated to the Chairs Sub-Category
This is happening ‘automatically’ due to the fact the Allow selection by category option on the Tooltip is checked. This is a feature (along with Include command buttons) I personally often switch off.
Now ideally, we’d be finished at this point, but we just need to add a final feature, due to the fact that some Manufacturers exist across multiple Sub-Categories. For example, below while I have clicked a mark that is related to the Global Manufacturer in Chairs, clicking Global in the links at the bottom highlight all the Global Manufacturers across all Sub-Categories, so we can’t get back to seeing the information just about the selected mark.
Adding the ‘Current Mark’ selection
We need to capture the ‘product hierarchy’ for each mark into a single field
Add this to the Detail shelf of the Scatter sheet.
We will need a parameter to then capture the ‘product hierarchy’ for the selected mark
pSelectedMarkIdentifier
string parameter set to ”” ie empty string
On the dashboard, add a parameter action
Identify Current Mark
On hover of the scatter sheet on the dashboard, set the pSelectedMarkIdentifier parameter to the value stored in the Category | Sub Cat | Manu field. Keep the current value when the selection is cleared.
Finally, we need to have a link to select in the tooltip, so we need
Current Mark Identifier
IF [pSelectedMarkIdentifier] = [Category | Sub Cat | Manu] THEN ‘Current Mark’ ELSE ” END
Add this to the Detail shelfof the Scatter sheet, and then update the Tooltip and add a reference to the Current Mark Identifier field
If you now go back to the dashboard and test by clicking on a mark associated to the Global Manufacturer, you should be able to click on Current Mark using the link in tooltip after clicking other links, and get back to what you would have seen in the tooltip when you first clicked on the mark.
It should just now be a case of sorting out the layout on the dashboard.
Congratulations on getting this far! My published viz is here.
Lorna’s challenge this week involved the use of nested LoDs (level of detail calculations) and ViT (viz in tooltip). The requirements were very brief (just 4 bullet points), but that didn’t mean this would be simple!
Each bar in the bar chart represents the number of states where the labelled sub-category had the most sales. ie there were 13 states where Phones was the sub-category with the most sales.
Let’s just look at the data : on a sheet add State/Province and Sub-Category to Rows and Sales to Text. Sort the list descending. From the screen shot below, we can see that for Alabama, Chairs has the most sales, in Alberta it’s Fasteners and for Arizona it’s Phones.
We need to get to a point where we can display a single row for each State/Province and the most popular Sub-Category.
We’ll start by creating a field that stores the sales for the State/Province & Sub-Category combination
Sales for State & Sub Cat
{FIXED [State/Province], [Sub-Category]: SUM ([Sales])}
On the table view above, add this field to Text instead of the Sales pill. The data displayed should be the same.
What we then need to do is to identify the maximum sales value for each State/Province. We do this with another Fixed LoD which references the above Fixed LoD (ie a nested LoD expression)
Max Sales for State & Sub Cat
//This returns the value of sales for the subcategory with the largest sales per state {FIXED [State/Province] : MAX([Sales for State & Sub Cat])}
Pop this into the table and you should see that the value for every row in this field is the same for each State/Province and matches the value of the first row in each pane
We can then use this to identify the Sub-Category where the values in the two columns match
Sub Cat with Max Sales for State
IF ([Sales for State & Sub Cat]) = ([Max Sales for State & Sub Cat]) THEN ([Sub-Category]) END
Add this onto Rows and the Sub-Category with the largest sales per State/Province is only listed once per pane.
Now remove Sub-Category from the view and you get 2 rows per State/Province – one Null, and one with the Sub-Category name we want.
Filter out the Nulls, by adding Sub Cat with Max Sales for State to the Filter shelf and excluding NULL. We’ve now got 1 row per State/Province with the appropriate Sub-Category
Let’s shift the data around – remove the fields from the Text shelf, and swap the order of the fields in the Rows so Sub Cat with Max Sales for State is listed first.
You can now see that for each Sub-Category we have the list of State/Provinces which we can just count using a new field.
Count States
COUNTD([State/Province])
Add this to Text and remove State/Province from Rows and sort descending and we have the data we need to build the bar chart.
Building the bar chart
Add Sub Cat with Max Sales for State to Rows
Add Count States to Columns and Label
Add Sub Cat with Max Sales for State to Filter and exclude Null
Sort Descending
Adjust colour of bars
Hide axis, Remove all gridlines, zero line etc
Adjust format of heading labels
Hide field labels for Rows to remove the Sub-Category label
Update the title
Adjust width of each row and size of bars as desired
Building the Map
To create the map, we need to be able to identify which Sub-Category we’re hovering on in the bar. For this we’re going to capture the value into a parameter
pSelecteSub-Cat
string parameter defaulted to ” / empty string
and the we need to identify the State/Provinces where the selected Sub-Category has the maximum sales
Selected States
[Sub Cat with Max Sales for State] = [pSelectedSub-Cat]
this returns a boolean true/false value based on whether the fields match or not.
On a new sheet, double click on State/Province to which should automatically generate a map. Make sure the location(Map menu > Edit Locations) is set to reference the Country/Region field, so both the US and Canadian provinces are all picked up
Change the mark type to be a filled map. Update the washout of the background layer to be 100% (Map menu > background layers) and then set the border property on the Colour shelf to be white.
Add Selected States to the Colour shelf and exclude the Nulls (right click Null in the colour legend and exclude). Add the pSelectedSub-Cat to the sheet and manually enter a value, eg Phones. Adjust the colours associated to the True & False values.
Adding the Viz in Tooltip
Back onto the bar chart sheet and edit the Tooltip. Include some introductory text that references the Sub-Category and the number of States (or leave with the default values if you wish). Then add the map sheet via Insert > Sheets > <select relevant sheet>
Edit this text adjusting the height and width as appropriate and removing the <All values>text (we don’t want to filter the map to just show the states with the associated Sub-Category, we need all states to be visible, we’re just colouring based on the parameter).
My tooltip dialog looks like
Setting the parameter
Add the bar chart to a dashboard then add a dashboard parameter action (Dashboard menu > Actions > Add Action >Change Parameter)
Set Sub Cat
on hover of the bar chart on the dashboard, update the pSelectedSub-Cat parameter with the value from the Sub-Cat with Max Sales for State field.
Now if you hover over a bar, you should get a map with the associated States highlighted.
Kyle returned this week to set this challenge to create a bullet chart which compared Win % for baseball teams in a year (bar) to the previous year (line), along with call out indicators (red circles) if the difference was greater than a user defined threshold. To add an extra ‘dimension’ to the challenge, a viz in tooltip showing the historical profile for the selected team was also required.
Building the basic bar chart
We’re going to use a parameter to define which year we want to analyse, as we can’t just filter by the Year as we need information about multiple years
pYear
integer field defaulted to 2018, formatted so the thousands separator does not display, and populated by Add values from the existing Year field
With this, we can create the following fields
Wins – CY
IF Year = [pYear] THEN [Wins] END
Wins – PY
IF Year = [pYear]-1 THEN [Wins] END
Games – CY
IF Year = [pYear] THEN [Games] END
Games – PY
IF Year = [pYear]-1 THEN [Games] END
and subsequently
Wins per Game – CY
SUM([Wins – CY]) / SUM([Games – CY])
and
Wins per Game – PY
SUM([Wins – PY]) / SUM([Games – PY])
Both these 2 fields are formatted to a custom format of ,##.000;-#,##.000 (this basically strips off the leading 0, so rather than 0.595 it’ll display as .595.
Now we have all these, we can build a dual axis chart.
Add League and Team to Rows and Wins per Game – CY to Columns. Sort by Wins per Game – CY descending. Display the pYear parameter.
Add Wins per Game – PY to Columns, make dual axis and synchronise the axis. Change the mark type of the CY card to a bar and the mark type of the PY card to Gantt. Remove Measure Names from the Colour shelf of both cards, and change the colour of the gantt bar to black.
Show mark labels for the bar mark and align left (expand the width of each row if need be).
Colouring the bars
The colour of the bars is based on whether the CY value is greater or less than the PY value. So we need to find the difference
CY-PY DIfference
[Wins per Game – CY]-[Wins per Game – PY ]
and then work out if the difference is positive or not
CY-PY Difference is +ve
[CY-PY Difference]>0
Add this to the Colour shelf of the bar marks card and adjust accordingly.
Adding the call out indicator
The red circle is based on whether the difference is above a threshold set by the user . A parameter is required for this
pChange
float field set to 0.1 by default; the values should range from 0.05 to 0.2 in 0.05 intervals
Show this parameter on the sheet.
We then can create
CY-PY Diff Greater than Change
IF ABS([CY-PY Difference])>[pChange] THEN ‘●’ ELSE ” END
The ABS function is used, as we want to show the indicator regardless as to whether the difference is a +ve or -ve difference. The ● image I get from copying from https://jrgraphix.net/r/Unicode/25A0-25FF. I use this page a lot, so keep it bookmarked.
Add this field to Rows, and then format the field so it is red and the font size is bigger (I used 12pt)
Now the viz just needs to be tidied up by
Hide field labels for rows
Rotate label of the League field
Format the font of the Team text
Reduce the width of the first three columns
Remove gridlines and zero lines
Adjust the row divider to be a dotted line at the 2nd level
Remove column dividers
Add an axis ruler to Rows
Uncheck Show Header on the axis to hide them
Tidy up the tooltip on the gantt mark type.
Building the Viz in Tooltip line chart
For this we need the Win % for every year, so we need
Wins per Game
SUM([Wins]) / SUM ([Games])
Add Team to Rows, Year to Columns (change to be a continuous, green pill) and Wins per Game to Rows
Add pYear to the Detail shelf, then add a Reference line to the Year axis to display the value of pYear as as dotted line
Right click on the reference line > format and adjust the alignment of the value displayed to be top centre.
Show mark labels and set to just show the min & max values for each line.
Colouring the lines
The colour of the line is based on whether the current year’s value is bigger or smaller than the previous year (ie the colour of the line matches the bar).
However, we can’t just use the fields we’ve already built, as because we have Year in the view, the data only exists against the appropriate year, so the difference can’t be computed. You can see this better if you build out the table below…
We need to ‘spread’ these values across every year for each team.
Wins per Game – CY Win Max
WINDOW_MAX(SUM([Wins – CY]) / SUM([Games – CY]))
Format this as you did above.
Add this to the view and amend the table calculation so it is computing by the Year field only. You should see that the value of this field matches the Wins per Game – CY value, but the same value is listed against every year now, rather than just the one selected.
Similarly, create
Wins per Game – PY Win Max
WINDOW_MAX(SUM([Wins – PY]) / SUM([Games – PY]))
format, and add this to the view too and adjust the table calculation as you did above.
So now we have this, we can work out whether the difference between these two fields is +ve or not
CY-PY Difference is +ve Win Max
[Wins per Game – CY Win Max]- [Wins per Game – PY Win Max]>0
Add this to the Colour shelf of the line chart. Verfify the table calculation is set to compute by Year only for both nested calculations, and then adjust the colours to match.
Finally tidy up the viz, to remove all headings, axis, gridlines, row/column dividers etc Set the background colour of the sheet to a light grey, then finally set the fit to be Entire View.
Adding the Viz in Tooltip
Go back the bar chart, and on the tooltip of the bar mark, adjust the initial text, then insert the line chart sheet via the Insert > Sheets > Sheetname option.
I adjusted the maxwidth & maxheight properties of the inserted text to be 350px each
If you hover over the bar chart now, you should get a filtered view of the line chart.
Final step is to put all this on a dashboard. My published viz is here.
Ann set this week’s #WOW2021 challenge during #TC21 and chose to live stream her build. I couldn’t watch it but I did then manage to catch a snippet of Kyle Yetter who bravely chose to share his attempt at recreating the challenge via a live stream too with Ann & Luke watching. I had already completed my build by the time I watched Kyle, and it was interesting to see where our approaches differed.
The main via is a single chart, so I started by building out all the data I needed in tabular form, so I could verify the sort.
Defining all the calculations for the main viz
First up, the data needs to be compared to ‘today’ where ‘today’ is set to 01 Jan 2022. I used a parameter to store this
pToday
Date constant set to 01 Jan 2022
We also need to understand the latest order date per customer, so need
Max Order Date By Customer
DATE({FIXED [Customer ID]: MAX([Order Date])})
and so with this we can calculate the days since last order, which is one of our key measures.
Days Since Last Order
DATEDIFF(‘day’, [Max Order Date By Customer], [pToday])
The other measures we need are
#Orders
COUNTD([Order ID])
Avg. Order Value
SUM([Sales])/[#Orders]
along with Sales and Quantity.
These are the 5 main measures displayed, but there are some additional calculations needed for the display labels and tooltips.
The label to indicate the ‘time since last purchase’ is displayed in days or years, so for this I created 2 specific fields
LABEL:Days Since Last Order
IF [Days Since Last Order] <365 THEN [Days Since Last Order] END
This will only store a value when the days is less than 365. I then formatted this field to have a ‘ days’ suffix
Similarly, I created
LABEL:Years Since Last Order
IF [Days Since Last Order]>=365 THEN [Days Since Last Order]/365 END
which stores a value for records >=365 only. This was formatted to have 1 dp and the ‘ years’ suffix instead.
The colour of the first measure is based on whether the customer is considered ‘active’ or not. The threshold for this is managed via a parameter
pActiveCustThreshold
An integer parameter defaulted to 90.
We can then create
Is Active Customer?
[Days Since Last Order]<=[pActiveCustThreshold]
This is a boolean field and will return true or false, but to get the tooltip value to display appropriately, I edited the alias of this field (right click > Aliases)
For the tooltip on the ‘total products’ (ie quantity measure), we need to display the number of distinct products orders, which we can capture in
#Products
{FIXED [Customer ID]: COUNTD([Product ID])}
Pop all these fields, along with the Customer ID and Customer Name into a table and you can see validate all the values are as you expect
Sorting the Sort
We need another parameter to manage the sort.
pSort
I used an integer parameter, but set the display to the required text strings. This is because writing logic on integers is more efficient than strings
I then created a calculated field to determine the measure to sort by
Sort By
CASE [pSort] WHEN 1 THEN SUM([Days Since Last Order]) * -1 WHEN 2 THEN [#Orders] WHEN 3 THEN SUM([Sales]) WHEN 4 THEN [Avg. Order Value] WHEN 5 THEN SUM([Quantity]) END
Notice the first measure is being multiplied by -1 since this measure is to be displayed in Ascending order rather than Descending.
In the table, amend the Customer ID pill to Sort by the Sort By field descending
Show the pSort parameter and test the functionality by switching the values.
Building the main viz
Having got all the calcs defined, this viz is relatively straight-forward. It is a multiple axis viz by Customer ID and Customer Name (note I use Customer ID as well, which I hide, just in case there are multiple customers with the same name).
Customer ID and Customer Name on Rows. Apply sort to Customer ID as described above. Hide Customer ID (uncheck Show Header). Align Customer Name to right, and adjust font.
Type in MIN(1) on Columns. Edit Axis to be fixed from 0-1. Add Is Active Customer? to Colour. and adjust. Add LABEL:Days Since Last Order and LABEL:Years Since Last Order to Label. Align Centre and match mark colour. Make sure the two fields are side by side on the label, and not underneath each other. Add Max Order DateBy Customer to the Tooltip shelf. Adjust Tooltip
Add #Orders to Columns. Strip off all the fields that have been automatically added to this marks shelf. Set the Colour accordingly. Show the Label at the end of the bar. Adjust Tooltip
Add Sales to Columns. Set the Colour accordingly. Show the Label at the end of the bar. Adjust Tooltip
Add Avg. Order Value to Columns. Set the Colour accordingly. Show the Label at the end of the bar. Adjust Tooltip
Type in MIN(0) on the Columns shelf. Change mark type to Circle. Set the Colour accordingly. Add Quantity to Label. Add #Products to Tooltip. Adjust Tooltip
Remove all row & column borders and gridlines, zero lines etc.
Add a 0 constant Reference Line to the #Orders, Sales and Avg. Order Value axes.
Hide the axes, hide field labels for rows.
Building the Viz in Tooltip
The Viz in Tooltip shows the Top 10 Products per Customer by Sales.
On a new sheet, add Customer ID, Customer Name and Product Name to Rows and Sales and Quantity (via Measure Values) to Text.
Add a Rank Quick Table Calculation to the Sales pill, then edit the table calc to be unique and to compute by Product Name
Click on the Sales rank pill , press Ctrl and drag it into the measures pane. Name the field Sales Rank. Format the field to have a # prefix. Add Sales back into the view.
Now drag the Sales Rank field from the Measure Values section and add to Rows, then change to be a discrete (blue) field, then move so it is to the left of the Product Name field. This should cause everything to re-sort into the required order automatically. It’s always worth double checking the table calc is still computing as expected.
Click on the Sales Rank pill and press Ctrl and this time drag to the Filter shelf and check values 1 to 10.
Now hide the Customer ID and Customer Name fields, and format the display.
Finally, set the display to Entire View. This will make the ‘view’ unreadable, but is necessary to ensure you don’t get a ‘viz is too large to display’ message when you get to add the Viz to the tooltip.
Adding the Viz in Tooltip
Back to the chart sheet, and edit the tooltip of the MIN(0) marks card. Insert the Top 10 sheet via Insert > Sheets. Adjust the width and height values and change the filter to just use <Customer ID>
Hovering on the circle mark should then display the Top 10 Products for that specific customer.
Adding the column headings
On the dashboard, use a horizontal layout container above the main viz. Add 6 text objects, enter the relevant text and centre. Adjust the width of each text object to line up with each column.
The help icon
Add a floating text object to the sheet – it’ll appear over the top of the chart. Position the text object to the top left, and set the background to white. Enter the text. From the context menu of the object, select the Add Show/Hide Button. Position the button in the appropriate location and resize. Edit the button options to have a grey background and show ? when hidden and X when shown.
I think that’s covered all the core points. My published viz is here.
Ann Jackson set this week’s challenge inspired by a business requirement she’d encountered, which was to be able to filter the data based on a user selection, and then additionally apply another filter to narrow down the required data.
The task ‘splash’ page had already indicated the challenge would involve sets, and the requirements additionally stated you’d need version 2020.2 or higher, which added an additional hint that set controls would be involved. Examining Ann’s solution, I could also see that the tell-tale set icon displayed when hovering near the input control, which confirmed the ‘filters’ were indeed using the set control functionality.
Building the bar chart
Viz in Tooltip
BANs
Building the bar chart
Let’s start by focusing on the bar chart. The first thing to do is to rename several fields. Ann likes capital letters, so the following fields just need to be renamed so they’re all in uppercase: Sales, Quantity, Product, Order ID and Customer (Name). Sales can also be formatted to $ with 0dp.
We create the first set we need by simply right-clicking on the PRODUCT field -> Create -> Set. I named this 1st Products and set to the Use All condition.
Lets build out a basic table and then we can play with how we need to use this set. Put
ORDER ID on Rows
CUSTOMER on Rows
Add Sales to Text
Drag Quantity over the Sales column, so Measure Names gets added to Columns and Measure Values is on Text
Sort by Sales descending
If we now add the 1st Products set to the Filter shelf and choose the Show Set option, the list of products in the set will be displayed for selection.
Choose the 1st item in the list, and the data will be filtered to just those customers who have ordered that product
But, while this list matches the names if you do the same on the solution, the Sales and Quantity values differ. This is because, we have actually filtered the data just to the lines containing the selected product. If you bring PRODUCT onto Rows, you’ll see you have 1 line per customer.
Now, while you can use LOD calculations to compute the total sales/quantity at an order id level, to resolve this, it won’t solve the next step of the puzzle, to filter the data further by other products on the same orders, as we’ve already filtered out all the other data. So we need to do something else.
To demonstrate, we just want to keep a handle on these specific ORDER IDs so ctrl-click to select them all, and then Include to add them to the Filter shelf. Now remove the 1st Products set from the Filter shelf, then re-add back to the Rows.
You’ll see we now have all the product lines on each order, with an In or Out displayed against each row. The rows which match the PRODUCT selected in the set, is marked as In. This is essentially what the filter is doing if 1st Products is on the Filter shelf – it is filtering the rows to those which return as In (the set).
We basically want a way to be able to identify all the rows on an order which have at least 1 In row, so we can filter on that instead. For this we can create a new field,
Order has 1st Products
{FIXED [ORDER ID]: MAX([1st Products])}
What this is doing is saying for each ORDER ID, get the maximum 1st Products value, which is then ‘stored’ against each row for the same ORDER ID. This may look odd, as 1st Products from the display is showing an ‘In’ or an ‘Out’, so how does the MAX work? Well, whilst the display is In or Out, under the bonnet this is actually a boolean field of 1 = True = In or 0 = False = Out, so the MAX is actually returning either a 1 (true) or a 0 (false).
If you add Order has 1st Products to Rows, you can now see every row in this restricted data set is listed as True
Remove the ORDER ID from the Filter shelf, and you should observe all the other orders listed have False listed against every row.
So we can now add Order Has 1st Product = True to the Filter shelf, and we can remove 1st Products from Rows too.
So we’ve mastered the 1st product filter, time for the 2nd. Once again right-click on PRODUCT and create another set with the Use All condition. This time I called the set 2nd Product.
Just as before, we want to identify all orders that contain this 2nd product selected, so we’ll create another calculated field
Order has 2nd Product
{FIXED [ORDER ID]: MAX([2nd Product])}
Add this field to the Filter shelf and select True. Now click on the 2nd Product set in the Dimesions pane, and selected Show Set, to display the list of products
You’ll notice though that all products are listed, and not just the ones that are associated to the set of orders already filtered. To fix this, we need to add the Order Has 1st Products field to context (click on the Order Has 1st Products pill on the Filter shelf and Add to Context). This will change the pill to a grey colour, and you can now restrict the values in the 2nd Products set to only show All Values in Context.
The add to context function allows us to dictate the order in which the filtering is applied, so the data is primarily filtered based on our 1st Products set and then filtered based on the 2nd Product.
You should now be able to play around with the filtering and see how the rows for complete orders are retained.
Removing PRODUCT from the Rows, and we get back to the total order value / quantity values we’re expecting.
From this, you should now be able to build out the viz. Firstly, duplicate the sheet so we retain the original table, then move SALES & QUANTITY to the Columns, add Measure Names to Colour, add Labels to match mark colour, and format accordingly.
Viz in Tooltip
As an ‘added extra’ Ann added the ability to hover on a bar and see the details of the order in a simple table. We can use the original table we built above to demonstrate the functionality. Add PRODUCT to Row, and replace the QUANTITY field, with a new field you need to create called QTY, which is just based on QUANTITY ie
QTY
[QUANTITY]
Then hide the ORDER ID & CUSTOMER fields (uncheck Show Header) on the pill. You could remove completely, but I like to retain just in case we need to debug if things don’t work.
We will need to show some totals, but this isn’t the Grand Total. Grand Totals will work, but the background of the row will always be a fixed colour. Either white, by default, or set to grey if you choose. However I noticed that depending on the order id I hovered on, the Grand Total row may display grey or white depending on the existing banding. For this we need to use subtotals instead (Analysis menu -> Totals -> Add All Subtotals).
Because we have multiple (hidden) dimensions on the rows, multiple Total rows will display. We can remove by unchecking the Subtotals option against the ORDER ID pill.
And finally, we need to relabel the row. Right-click on the Total row and select Format. Change the label of the Totals section from ‘Total’ to ‘Grand Total’.
With additional formatting to change the font size etc, this sheet can now be added as a ‘Viz in Tooltip’ to the bar chart. On the Tooltip shelf associated to the bar chart, you can Insert -> Sheets -> <Select Sheet>
Hovering over a bar should now automatically filter the tabular display to the appropriate CUSTOMER & ORDER ID.
BANs
For the BANs, we need several additional calculated fields
# ORDERS
COUNTD([ORDER ID])
simply counts the number of distinct Order IDs in the filtered view.
Total Orders
{FIXED:COUNTD([ORDER ID])}
an LOD calculation that counts the overall number of distinct Order IDs, regardless of any filters applied.
% OF TOTAL ORDERS
[# ORDERS]/SUM([Total Orders])
formatted as a % to 1 dp.
AVG ORDER AMOUNT
SUM([SALES]) / [# ORDERS]
formatted to $ 0 dp.
AVG ORDER QUANTITY
SUM([QUANTITY])/[# ORDERS]
formatted to a number with 0 dp.
Add Measure Names to the Filter shelf, and filter to the relevant 4 measures. Add Measure Values and Measure Names to the Text shelf, and Measure Names on Columns. Add the Order Has 1st Products = true and Order has 2nd Product = true to the Filter shelf, but this time don’t add anything to context. If you do, it will affect the Total Orders measure, and the % of orders calc will be wrong. Hide the Measure Names on the columns (uncheck show header), and format the text appropriately.
To get the thick lines, format and set the Row Divider on the sheet to be a thick coloured line.
And you should now have all the core building blocks needed to add onto a dashboard.
Donna + DataViz 