It was Lorna’s turn to set the challenge this week, and she opted for a gentle workout to soothe us into the Christmas holidays – I certainly appreciated it as I am super busy and super stressed trying to finalise Christmas presents and preparations!
I built this with 4 calculations in total. Let’s start by just building out a tabular view of the data, so we know what we’re looking for.
Add Customer ID and Order Date set to be a discrete (blue) exact date onto Rows.
To start with, we want to capture, against each customer, the earliest order date
First Purchase Date
{FIXED [Customer ID]: MIN([Order Date])}
Add this into Rows as a discrete exact date.
You can see that for every row associated to the same customer, the First Purchase Date matches the first row.
Now I want to identify the row that represents the first order for each customer
Order is first order
[Order Date] = [First Purchase Date]
Add this onto Rows and we can see the first row against each customer is True, while the rest are False.
I can now use this to identify the date of the 2nd order for each customer – it is the earliest date where the order isn’t the first order
Second Purchase Date
{FIXED [Customer ID]: MIN(IF NOT [Order is first order] THEN [Order Date] END)}
Add this onto Rows as a discrete exact date, and you can see that every row associated to the customer, now has the date that matches the 2nd Order Date listed.
Remove Order Date and Order is first order from the Rows and we have 1 row per customer with the 2 dates we’re interested in.
Add this onto Text, so you can validate the result.
Now we can build out the matrix.
On a new sheet add First Purchase Date to Rows and set to be at the discrete (blue) quarter level. Then add Quarters Since First Purchase to Columns and change to be a discrete Dimension.
This already gives us the shape we’re after. Right click on the Null value in the column heading and select Edit Alias and update the word to Lapsed.
Add Customer ID to the Colour shelf, and the click on the pill and change to be a Measure of Count Distinct. This will change how the viz is displayed. Adjust the colours accordingly.
Finally update the Tooltip to match the required text, and adjust the formatting so there are pale grey thin dotted row and column dividers on the pane only, but not the header.
Add the viz to a dashboard, and you’re done! My published viz is here.
Let’s get stuck in, by starting with the selector sheets.
Building the Measure Selector
The measure selector will be used to set a parameter which will store the particular measure selected, so we need
pSelectedMeasure
string parameter defaulted to the value Sales
We also need a field to help us ‘draw’ the 3 selection boxes onto a viz, in such a way that we then have a measure value to pass into the parameter on selection. You could build this with its own separate dataset, but I’m going to utilise another field to ‘fake this’, and drive it off the Segment dimension as we don’t need this in the rest of the viz.
Measure Selector Alias
CASE [Segment] WHEN ‘Consumer’ THEN ‘Sales’ WHEN ‘Corporate’ THEN ‘Profit’ ELSE ‘Quantity’ END
Add Segment to Columns. Then double click into Columns and manually type MIN(1). Widen the row and then add Measure Selector Alias onto Label.
Edit the axis, and fix the range to run from 0 to 1. Adjust the label font and align centrally. Hide the axis and the Segment header.
We need to identify which measure has been selected, both through colour and an arrow indicator. So we need
Add this to the Label shelf and adjust so the fields are side by side and the arrow text is coloured red.
Finally, stop the Tooltip from displaying, then hide all gridlines, zero lines, axis and row dividers. Set the column dividers to be thick white lines and make the Size as large as possible.
Name the sheet Measure Selector.
Building the Year Selector
In order to not ‘hardcode’ the latest year, we need
Current Year
{FIXED:MAX(YEAR([Order Date]))}
format this to be a number with 0dp and not to show the thousands separator.
On a new sheet, add Comparison Year to Filter and exclude NULL. Then add Comparison Year to Columns and sort descending, so the latest year is listed first. Double click into Columns and manually type MIN(1).
As before widen the rows, and then add Comparison Year to Label. Edit the axis, and fix the range to run from 0 to 1. Adjust the label font and align centrally. Hide the axis and the Comparison Year header.
We’re going to need a parameter which will capture the year selected
pSelectedYear
integer parameter defaulted to 2022 with the display format set to not include the thousand separator
We need to identify which year has been selected, both through colour and an arrow indicator. So we need
Is Selected Year
[Comparison Year] = [pSelectedYear]
Add to the Colour shelf and adjust to suit.
Then create
Year Selected Arrow
IF [Is Selected Year] THEN ‘►’ END
Add this to the Label shelf and adjust so the fields are side by side and the arrow text is coloured red.
Finally, stop the Tooltip from displaying, then hide all gridlines, zero lines, axis and row dividers. Set the column dividers to be thick white lines and make the Size as large as possible. Name the sheet Year Selector.
Building the Current Year ‘card’
Double click into Columns and manually type MIN(1). Add Current Year to Label. Widen the row. Edit the axis, and fix the range to run from 0 to 1. Adjust the label font and align centrally. Hide the axis. Adjust the Colour to suit. Stop the Tooltip from displaying, then hide all gridlines, zero lines, axis and row dividers. Make the Size as large as possible. Name the sheet Curr Year.
Building the bar chart
Based on the measure selected, we need to get the value of the relevant measure for the current year and for the comparison year, so we need
Measure to Display – Curr Year
IF YEAR([Order Date]) = [Current Year] THEN CASE [pSelectedMeasure] WHEN ‘Sales’ THEN [Sales] WHEN ‘Profit’ THEN [Profit] ELSE [Quantity] END END
and
Measure to Display – Comp Year
IF YEAR([Order Date]) = [pSelectedYear] THEN CASE [pSelectedMeasure] WHEN ‘Sales’ THEN [Sales] WHEN ‘Profit’ THEN [Profit] ELSE [Quantity] END END
On a new sheet, add Sub-Category to Rows and Measure to Display – Curr Year to Columns. Sort descending. Then click and drag Measure to Display – Comp Year to the axis and release when the two green columns display. This will automatically add Measure Names and Measure Values into the view.
Reorder the pills in the Measure Values box, so the current year values are listed first. Add Measure Names to Colour and adjust to suit. Add Current Year, Measure to Display – Curr Year and Measure to Display – Comp Year to Tooltip, and adjust the tooltip so it is referencing those 3 fields along with the pSelectedMeasure and pSelectedYear parameters
So we have the bars, but now we need to add the ‘candlestick’, which we’re going to crate using a gantt bar. We need another ‘measure’ row to show, and need another instance of Measure to Display – Comp Year for this – we can’t use the existing measure, as it will put data on the same ‘row’. So simply duplicate the Measure to Display – Comp Year field, to get the Measure to Display – Comp Year (copy) field. Add this to Columns.
Change the mark type of this to a gantt bar. To get the size and the information we need for the labels and to colour the gantt, we need some more fields.
Difference
SUM([Measure to Display – Curr Year]) – SUM([Measure to Display – Comp Year])
custom format this to +#,##0;-#,##0 and add this field to both the Size and the Label shelf.
% Difference
IF (SUM([Measure to Display – Curr Year])>=0 AND SUM([Measure to Display – Comp Year])>=0) OR (SUM([Measure to Display – Curr Year])<0 AND SUM([Measure to Display – Comp Year])<0) THEN [Difference]/ABS(SUM([Measure to Display – Comp Year])) ELSE 0 END
If both the values are positive or both the values are negative, then calculate the difference, otherwise return 0, and then custom format to ▲0.0%;▼0.0%;- . The first section up to the ; formats the number when it’s positive, the next section formats when negative, and the last section formats when the number is 0, so in this case we’re replacing any 0 with a ‘-‘. Add this to the Label shelf.
Diff is +ve
[Difference]>0
Add this to the Colour shelf (remove Measure Names) and adjust accordingly.
Reduce the Size of the gantt bar, adjust the label so the font is smaller, organised as required, and aligned to the right. Remove all the text from the Tooltip. Then make the chart dual axis and synchronise the axis. Explicitly set the mark type of the Measure Names marks card to a bar.
Finally tidy up by hiding both axis, removing all gridlines, axis lines, zero lines and column dividers. Format the Sub-Category label headings and align middle left. Hide the Sub-Category row heading (hide field labels for rows) and hide the Measure Names field (uncheck show header). Name the sheet Chart.
Adding the interactivity
Using vertical and horizontal containers, arrange the objects on the dashboard. I used a horizonal container to align the Curr Year, Year Selector and Measure Selector sheets, adding blank objects in between. I edited the title of the 3 objects to display the required text. I then floated a blank object over the Current Year box, so it couldn’t be clicked.
To select the year and the measure, I needed parameter actions
Select Year
on select of the Year Selector sheet, set the pSelectedYear parameter, passing in the value from the ComparisonYear field
and
Select Measure
on select of the Measure Selector sheet, set the pSelectedMeasure parameter passing in the value from the Measure Selector Alias field
Finally to stop the years and measure boxes being ‘highlighted’ on click, create fields
True
TRUE
and
False
FALSE
and add both to the Detail shelf of the Year Selector and Measure Selector sheets. Then add dashboard filter action
Deselect Years
On select of the Year Selector sheet on the dashboard, target the Year Selector sheet itself, passing the values True = False.
Create another similar filter action for the Measure Selector sheet, and that should then be it!
Global Recognition month continued this week for #WOW2023, and I was able to enlist Norbert Borbas to set the challenge this week, which was published in both Norbert’s native Hungarian, as well as English.
Norbert provided a challenge based on a solution he had implemented at his company, and involved the creation of 2 dashboards with interactivity between them both. There’s a fair amount going on with this one, so let’s get cracking.
Building the Sales KPI
For this viz, we need to get information about the latest year sales in conjunction with the previous year. Rather than hardcode any years relating to the data, I created
Latest Year
{MAX(YEAR([Order Date]))}
which for the data set I was using, returns 2022. Drag this field into the ‘dimensions’ section of the left hand data pane (ie above the line).
With this I then created
LY Sales
IF YEAR([Order Date]) = [Latest Year] THEN [Sales] END
to the sales for 2022. Format this to $ with 0 dp.
To get the sale for the previous year (ie 2021) I created
Previous Year
[Latest Year]-1
Drag this field into the ‘dimensions’ section of the left hand data pane (ie above the line), and then create
PY Sales
IF YEAR([Order Date]) = [Previous Year] THEN [Sales] END
Format this to $ with 0 dp.
We then needed the % difference between these values
The line chart needs to change based on whether the Sales KPI or the Profit KPI sheet has been selected. We need a parameter to capture this ‘decision’.
pMeasureToShow
string parameter defaulted to Sales
To the determine which actual value to display we need
Line – Measure to Display
IF [pMeasureToShow] = ‘Sales’ THEN [Sales] ELSE [Profit] END
format this to $ with 0dp.
I also created
Line – Measure to Display Axis (k)
IF [pMeasureToShow] = ‘Sales’ THEN [Sales] ELSE [Profit] END
ie the same field, but this was formatted to $ with 0 dp but display units of Thousands (k).
Having the two fields means that the axis can display in one format while the tooltip can show the more detailed value.
On a new sheet add Order Date to Columns and change to the discrete ‘month’ level (blue pill – May). Add Line – Measure to Display Axis (k) to Rows. Add Order Date to Colour. This will default to the Year level, and show all years from the data set, but we only want the latest 2 years. So create
Filter Years
YEAR([Order Date]) >= [Previous Year]
and add to the Filter shelf and set to True. Adjust colours to suit.
Add Order Date to the Label shelf and change to the Year level. By default the lines should be labelled. Edit the label and set the font to match mark colour. I also set the font to be Tableau Medium and bold. Adjust the order of the years in the colour legend, so 2022 is listed first which makes the line for 2022 sit ‘on top’ of the 2021 line.
Remove all gridlines/row column dividers, and set the axis lines to be bolder. Hide the Order Date label (right click > hide field labels for columns). Adjust the formatting of the Order Date axis, to display the months in an abbreviated form. Adjust the title of the y-axis to reference the pMeasureToShow parameter (right click the axis > edit).
Add Line – Measure to Display to the Tooltip shelf.
Adjust the Tooltip to display as
Finally, to help with the interactivity later, we will need
Month Order Date
DATEPART(‘month’, [Order Date])
This returns the number of the month ie 1 for January, 2 for February etc. Move this to the ‘dimensions’ section of the left hand data pane (drag above the line), and then add this to the Detail shelf. Change the field to be a discrete attribute
Name the sheet Line Chart.
Building the Symbol Chart
On a new sheet add Filter Years to the Filter shelf and set to True. Add Order Date to Columns and change to be at the discrete month level. Double click into the Rows shelf and manually type in MIN(0). Add Month Order Date to the Detail shelf.
We need to display coloured arrows depending on whether the change is up or down. For this we need
Symbol – Difference to Display is +ve
IF [pMeasureToShow] = ‘Sales’ THEN IIF([% Diff Sales From PY]>=0,TRUE,FALSE) ELSE IIF([% Diff Profit From PY]>=0,TRUE,FALSE) END
If the measure to display is Sales, and the difference in Sales from previous year is +ve, then return true, otherwise false, Else if the measure to display is Profit and the difference in Profit from the previous year is +ve, then return true, else false.
Change the mark type to Shape and then add this field to both the Colour shelf and the Shape shelf. Adjust colours and shapes accordingly.
Edit the axis and delete the title and set the major and minor tick marks to None. We need the axis to remain as we will need to ‘line up’ this chart with the line chart, and having a left hand axis will help.
Hide the months from showing (uncheck show header against the pill on Columns. Hide all gridlines, axis lines, zero lines & row/column dividers.
Name the sheet Symbol Chart.
Building the Main dashboard
Using horizontal and vertical layout containers, position the sheets in the required locations along with the title and the instructional text. Use background colours and inner & outer padding to give space between the objects.
For the line chart and symbol chart, these were placed in a vertical container, and the width of the ‘blank’ y-axis on the symbol chart widened to be in line with the axis on the line chart. The hierarchy of objects I used is pictured.
To make the Sales display on the line chart when the Sales KPI sheet is clicked, create a dashboard parameter action
Show Sales Line
On select of the Sales KPI sheet, set the pMeasureToShow parameter, passing in the value from the Sales Label field. When the selection is cleared, keep the parameter set to the current value.
And create a similar action to show the profit
Show Profit Line
On select of the Profit KPI sheet, set the pMeasureToShow parameter, passing in the value from the Profit Label field. When the selection is cleared, keep the parameter set to the current value.
We will need to return to this later, to add more interactivity, but for now we’ll move onto the analysis/drill down sheet.
Building the drill down table
We’re going to need a few more fields to build this type of display. Firstly, for the first bar chart column we need
Bar – LY Measure to Display
IF [pMeasureToShow] = ‘Sales’ THEN [LY Sales] ELSE [LY Profit] END
Bar – PY Measure to Display
IF [pMeasureToShow] = ‘Sales’ THEN [PY Sales] ELSE [PY Profit] END
Format both these fields to $ with 0dp.
Add State/Province to Rows and Bar – LY Measure to Display to Columns. Sort the states by the measure descending. Adjust the colour of the bar to suit. Add Bar – PY Measure to Display to the Detail shelf and add a reference line per cell that displays the average of this measure.
Show mark labels and adjust the font of the labels to be size 8pt. Widen each row, and align the State labels to the left, and change the font to be bold & black. Reduce the Size of the bars. Remove gridlines, but add row dividers.
Double click into the columns shelf and manually type MIN(0) to create a ‘fake’ axis and generate a MIN(0) marks card.
Create a new field
Measure Rank LY
RANK(SUM([Bar – LY Measure to Display]))
and add this to the Label shelf of the MIN(0) marks card. Adjust the table calculation so it is explicitly set to Compute by State/Province. Remove the Bar – PY Measure to Display field from the Detail shelf. Change the mark type to shape.
To determine what type of shape and what colour to apply, we need
Measure Rank PY
RANK(SUM([Bar -PY Measure to Display]))
and then
Measure Rank Change
IF [Measure Rank LY] < [Measure Rank PY] THEN ‘Up’ ELSEIF [Measure Rank LY] > [Measure Rank PY] THEN ‘Down’ ELSEIF ISNULL([Measure Rank LY]) THEN NULL ELSE ‘N/A’ END
Add this field to both the Shape and the Colour shelf, and adjust the table calculation so it is explicitly set to Compute by State/Province.
Adjust the shapes, and use a transparent shape against the Null option (see here for details). Adjust colours to suit. Increase the size of the shape, and align the label to the left.
For the next column, create
Bar – Measure Difference
SUM([Bar – LY Measure to Display]) – SUM([Bar -PY Measure to Display])
and custom format to +”$”#,##0;-“$”#,##0
Add to the Columns shelf, and labels should automatically get added.
Create a field
Bar – Measure Diff is +ve
[Bar – Measure Difference] >=0
and add to the Colour shelf of this marks card. Adjust colours to suit.
For the final column, we need to separately identify the values when the YoY measure difference is positive from those that are negative, and then apply ranking to each of these fields. So we need
+ve Measure Diff
IF [Bar – Measure Diff is +ve] THEN [Bar – Measure Difference] END
-ve Measure Diff
IF NOT([Bar – Measure Diff is +ve]) THEN [Bar – Measure Difference]*-1 END
Note, as the difference in this instance is negative, the values returned will also be negative, but when it comes to ranking, we want the record with the biggest negative difference to be ranked 1st ie if one value had a difference of -10 and another had a value of -100, in typical ranking, -10 is ‘higher’ than -100, so -10 would be ranked 1 and -100 2. But we want -100 to be ranked 1. So by multiplying the values by -1 in the calculation we actually return values 10 and 100. So when we rank them later, 100 is ranked 1 as it is bigger than 10.
Ranke +ve Measure Diff
RANK_UNIQUE([+ve Measure Diff])
Rank -ve Measure Diff
RANK_UNIQUE([-ve Measure Diff])
We will be displaying the information for the positive and negative ranks in separate ‘columns’ which we can do with
Rank YoY X-axis
IIF([Bar – Measure Diff is +ve], 1,2)
Add this field to columns and change the mark type to Circle. Add Bar – Measure Diff is +ve to Colour. Add Rank +ve Measure Diff and Rank -ve Measure Diff to Label. Ensure the table calculations for both fields are explicitly set to Compute by State/Province. Increase the Size of the circle, and align the label to be middle centre using a bold white font.
Now we have all the information displayed, we need to sort the tooltips.
This sheet, will be accessed through interaction and will be ‘filtered’ to just a specific month. For now, we’ll ‘hardcode’ the month by adding Month Order Date to the Filter shelf and selecting 3 (for March).
On the All marks card, add Latest Year, Previous Year,Month Order Date, Measure Rank PY to the Tooltip shelf.
We will also need
TOOLTIP – Rank statement decrease
IF [Measure Rank Change] <> ‘Up’ THEN MIN([State/Province]) + “‘s ” + [pMeasureToShow] + ‘ Rank decreased vs. same month last year.’ END
TOOLTIP – Rank statement increase
IF [Measure Rank Change] = ‘Up’ THEN MIN([State/Province]) + “‘s ” + [pMeasureToShow] + ‘ Rank increased vs. same month last year.’ END
TOOLTIP – Rank YoY Statement Negative
IF NOT([Bar – Measure Diff is +ve]) THEN MIN([State/Province]) + ‘ is a negative (Rank: ‘ + STR([Rank -ve Measure Diff]) + ‘) contributor to the overall YOY increment in the selected month.’ END
TOOLTIP – Rank YoY Statement Positive
IF [Bar – Measure Diff is +ve] THEN MIN([State/Province]) + ‘ is a positive (Rank: ‘ + STR([Rank +ve Measure Diff]) + ‘) contributor to the overall YOY increment in the selected month.’ END
Add all 4 of these fields to the Tooltip shelf of the All marks card. Ensure all the table calculation fields are set to explicitly Compute by State/Province.
Now adjust the tooltip with all the relevant fields, applying colouring as required
Hide the axis and hide the null indicator. Hide the State/Province column label heading. Finally, remove the Month Order Date field from the Filter shelf. The tooltip will look a bit funny at this point, but that will get sorted later.
Name the sheet Drill Down Table.
Building the drill down dashboard
Again using vertical and horizontal containers, arrange the sheet on a dashboard along with the title. Use text boxes arranged in a horizontal container directly above the Drill Down Table sheet to display the column headings.
As I didn’t want to hardcode any years, I created the following parameters
pLatestYear
integer parameter defaulted to 2022 and with a display format that did not include thousand separators.
and
pPreviousYear
integer parameter defaulted to 2021 and with a display format that did not include thousand separators.
and
pMonth
integer parameter defaulted to 3 and with a display format that did not include thousand separators.
When building the column headings, I referenced all these parameters instead.
To ‘set’ these parameters, I added Previous Year and Latest Year to the Detail shelf of both the Line Chart and Symbol Chart sheets.
I then added 3 dashboard parameter actions to the main dashboard which on select of the Line Chart or Symbol Chart sheet, set the relevant parameter with the value from the appropriate field.
To ensure the drill down gets ‘filtered’ to the month selected on the main dashboard, add a dashboard filter action
Drill down
On select of the Line Chart or the Symbol Chart, target the Drill Down Table sheet on the Drill Down dashboard, passing the selected field of MONTH(Order Date) only. Exclude all values when selection is cleared.
The final step is to add a Navigation Button to the drill down dashboard which displays the text ‘Go back to landing page’ and navigates back to the main dashboard.
And hopefully, with all that, you have a completed interactive navigational dashboard! My published version is here.
For this week’s #WOW2023 challenge, guest poster Ervin Vinzon asked us to rebuild this visualisation based on data from his home country, The Philippines.
I have to admit, I did find this a bit tough this week – there was a lot going on and maps don’t come naturally to me. I actually wasn’t sure initially whether both the files were needed, as the requirements were a little bit sparse, and I managed to build pretty much the whole solution not using the zip file. I just couldn’t get the map label annotation to work, so ended up having to had to revisit and start again.
Modelling the data
You will need to download both the excel file and the zip file from the Ervin’s shared area.
In the data pane, connect to the Ph Pop 2020 excel file and add the Philippine Population sheet to the canvas.
Then add a connection to a spatial file and point to the zip file. Tableau will automatically identify the file it can use. Add the Provinces file to the canvas.
Create a relation that uses relationship calculations that maps from the Philippine Population sheet :
IIF([Province] = “Maguindanao del Norte” OR [Province] = “Maguindanao del Sur”, “Maguindanao”, [Province])
to the the Provinces sheet
IIF([ADM1_EN] = “National Capital Region”, “Metro Manila”, [ADM2_EN])
Thanks to Rosario Gauna for helping me with this logic, as I couldn’t figure out how the data needed to be related. I think this really needed to be included in the requirements… (Note the logic has been adjusted since I took the image below)
Building the Measure Selector
We can’t use a parameter directly for this, as the design of the ‘radio button’ is more fancy than just what you get with the basic parameter selection functionality.
So we need to ‘fake’ the selection and can use existing fields in our data set to help with this. The Island Group field contains 3 values, so we’re going to draw on these and build
Measure Selector
CASE [Island group] WHEN ‘Luzon’ THEN ‘By Population’ WHEN ‘Mindarao’ THEN ‘By Population Density’ ELSE ‘By Area’ END
Add to Columns and manually reorder. In the Rows shelf, double click and manually type MIN(0)Change the mark type to circle and add Measure Selector to the Label shelf. Resize the circles, and adjust the label to be aligned middle right. Change the view to Fit Width to see all the labels.
Create a parameter to capture the selected measure when this view is interacted with
pMeasureSelected
string parameter defaulted to By Population
Show the parameter on the sheet. Then create a calculated field
Is Selected Measure
[pMeasureSelected] = [Measure Selector]
and add to the Colour shelf. Adjust the colours to suit and add a grey border on the circles (via the colour shelf).
Stop the Tooltip from showing, hide the MIN(0) axis and the Measure Selector header and remove all gridlines/zero lines and any dividers. Name the sheet Measure Selection.
Building the bar chart
Firstly, we need to determine which measure we’re going to be displaying, so need
Measure to Display
CASE [pMeasureSelected] WHEN ‘By Population’ THEN SUM([Population]) WHEN ‘By Population Density’ THEN SUM([Density]) ELSE SUM([Area (sq km)]) END
Show the pMeasureSelected parameter on a new sheet, then add Island Group and Province to Rows and Measure to Display to Text. Sort the data descending.
Create a new calculated field
Measure Rank
RANK_UNIQUE([Measure to Display])
Change the Measure Rank field in the left hand data pane to be discrete. Add to the Rows and adjust the table calculation so it is computing by Province only. The Measure Rank should show sequential numbers from 1 upwards, but restart at the next Island Group.
Add another instance of Measure Rank to the Filter shelf. Select All intially to select all the numbers. Then adjust the table calculation to compute by Province only as above. Then re-edit the filter and just select numbers 1-10.
The bar visual displays the actual value in a coloured bar, along with the maximum value for the measure in a grey bar. So we need
Max Value
WINDOW_MAX([Measure to Display])
Add this to the table and adjust the table calculation to compute by Province.
Finally, we need some information to help with the labels
Label Strapline
CASE [pMeasureSelected] WHEN ‘By Population’ THEN ” WHEN ‘By Population Density’ THEN ‘persons per sq km’ ELSE ‘sq km’ END
Add this to Rows and then test the behaviour by adjusting the value of the pMeasureSelected parameter.
We now have the data needed to build the bars.
Move Island Group to Columns and manually reorder to be Luzon, Visayas, Mindanao. Move Province and Label strapline to Text. Move Measure to Display and Max Value to Columns. Set sheet to fit Entire View. Reduce the size of the bar to be relatively thin.
On the Measure to Display marks card, add Island Group to Colour and adjust to suit.
Set the colour of the bar on the Max Value marks card to be pale grey and remove the bar border. Remove the Label Strapline field and move the Province from label to Detail.
Make the chart dual axis and synchronise the axis. Adjust the axis (right click > edit axis) to be independent axis ranges for each row or column.
On the Measure to Display marks card, add Measure Rank and Measure To Display to the Label shelf. adjust the table calculation settings of the Measure Rank field to compute by Province only.
Adjust the label to be aligned top left, and then format the label text box, so the label is laid out as required (I used bold 8pt font). To make the label sit ‘above’ the bar, add carriage returns after the text in the label edit box (thanks to Sam Parsons for spotting this sneaky method – my original build was using a much more complex method to get the text sitting on top of the bars!).
Finally hide the axis and the Measure Rank and Island Group fields. Remove all gridlines/zero lines/axis & row and column dividers. Stop the tooltips from showing. Name the sheet Bars.
Building the Bar Header
On a new sheet, add IslandGroup to Columns and manually re-order. Then double click in Columns and manually type MIN(0.1). Set the mark type to Bar and set the view to fit Entire View. Add Island Group to Colour. Reduce the Size of the bar. Edit the axis and fix to end at 0.7. Add Island Group to the Label shelf, and align bottom left. Adjust the size of the font to be larger and then add multiple carriage returns above the label text to shift the label to sit under the bar.
Remove all headers/axis and row/column dividers and gridlines. Stop the tooltip from showing.
Adjust the title of the sheet to reference the pMeasureSelected parameter.
Name the sheet Bar Header.
Building the map
We will need another parameter to store the selected Province value.
pSelectedProvince
string parameter defaulted to nothing
On. a new sheet, double click on the Geometry field. This will automatically display a map of the Philippines. Remove all the unnecessary detail via Map > Background Layers and unchecking all the options.
Add Province to the Detail shelf and Region and Island Group to the Tooltip. Adjust the Tooltip.
Show the pSelectedProvince parameter and manually enter the province Leyte.
Create a new field
Is Selected Province
[pSelectedProvince] = [Province]
and then add to the Colour shelf. Adjust the colours to suit (set the NULL field to the same as False).
We need to capture the ‘geometry’ of the selected Province
Drag this field onto the canvas and drop it on the Add a marks layer section that displays. This will create a second marks card. Change the mark type to Circle and adjust the colour as required. Add pSelectedProvince to the Detail shelf.
Select the circle mark, and add an annotation against the mark (right click > Annotate > Mark). Reference the parameter pSelectedProvince in the dialog window.
Providing the pSelectedProvince is on the Detail shelf and is referenced in the Annotation, then changing the value of the pSelectedProvince parameter to Samar or any other province, should retain the annotation. Once again, thanks to Sam for figuring this out as I could just not see it, even when I looked at the solution.
Remove row & column dividers. Stop the map options from displaying (Map > Map Options and uncheck all selections). Update the title of the sheet, and then name the sheet Map.
Adding the interactivity
Add the sheets to a dashboard using horizontal and vertical layout containers to arrange the objects.
Update the title of the Measure Selection sheet and the Bar Header sheet to match the text being displayed.
Create a dashboard parameter action to define the measure selection on click
Set Measure
On selection of the Measure Selection sheet, set the pMeasureSelected parameter, passing through the value from the Measure Selector field.
Create another action for the Province
Select Province
On selection of the Bars sheet, set the pSelectedProvince parameter, passing through the value from the Province field. When the selection is cleared, reset to nothing.
To stop the Bar Heading sheet from being clicked on, just float a blank object over the top.
To prevent the other bars and the measure selections from all fading when clicked on, create a new field
HL
‘HL’
and add to the Detail shelf of the Bars sheet and the Measure Selection sheet.
Then back on the dashboard add a dashboard highlight action
Unhighlight
On selection of the Bars sheet and the Measure Selection sheet, target the Bars and the Measure Selection sheets using the HL selected field only.
Now when a bar is clicked, it will look ‘selected’ (has a black bar around it), but the other bars won’t become faded/greyed out. Similarly when a measure is selected, the other circles won’t fade.
Phew! That should be it. There’s a fair amount going on here and lots of tricky ‘gotchas’. My published viz is here .
As we continue global recognition month for #WOW2023, Flavio Matos introduced this challenge which displays a unit chart of wines by type.
An added twist was to provide the ability for the user to switch between English (UK) and Portuguese (Brazil) languages, and the excel data set provided a sheet with the data per language.
After completing my solution, I checked out Flavio, and found he built the chart for each language (and so data source) in 2 separate sheets, added to 2 different dashboards and used navigation button with a flag image to switch between 2 dashboards. I chose to go a different route, one that didn’t mean duplicating the viz. This means that if I had to alter the viz in future for some reason, I’m only doing it once.
Modelling the data
To build in a single sheet, I needed to have the data for two languages combined into one table. Connect to the excel workbook and add the Wine sheet to the canvas, then add a relation to the Vinho sheet and set the relationship to match on the Wine field from each sheet.
Building the Viz
We need a parameter to manage the language selection
pCountry
string parameter defaulted to ‘UK’
This parameter is then used to determine which of the fields we need to use on the viz, and these are determined through calculated fields.
Country to Display
IIF([pCountry]=’UK’, [Category],[Family])
Add this to Rows then add Abreviation to the Detail shelf. Sort the Country To Display field by the count of the Abbreviation field descending.
Change the Mark type to Square then add Abreviation to the Label shelf. Adjust the sheet to Entire View and then align the label to be middle centre.
Create a parameter to store the list of English options that can be selected
pOptions-UK
string parameter containing the list of options (taken from the requirements page), with All listed first. Default to All.
Then create a version to store the Portuguese options
pOptions-Portuguese
string parameter containing the list of options (taken from the requirements page), with Todos listed first. Default to Todos.
To flag the wines that are related to the options selected, we need
Wine has Tag
IF [pCountry] = ‘UK’ THEN IF [pOptions-UK] = ‘All’ THEN TRUE ELSEIF CONTAINS([Tags],[pOptions-UK]) THEN TRUE ELSE FALSE END ELSE IF [pOptions-Portuguese]=’Todos’ THEN TRUE ELSEIF CONTAINS([Tags (Vinho)],[pOptions-Portuguese]) THEN TRUE ELSE FALSE END END
This returns true or false based on what country has been selected, and in turn what country specific option has been selected. If the wine is tagged with that option, or all wines have been selected then true is returned, otherwise false.
And then using this field we can determine how to colour the squares.
Colour
IF [Wine has Tag] THEN IF [pCountry]=’UK’ THEN [Type] ELSE IF [Type] = ‘Red’ THEN ‘Tinto’ ELSEIF [Type] = ‘Sparkling’ THEN ‘Espumante’ ELSEIF [Type] = ‘White’ THEN ‘Branco’ ELSE [Type] END END ELSE IIF([pCountry]=’UK’, ‘No Highlight’,’Nao Harmoniza’) END
This is slightly more detailed as the Type field in each of the excel sheets are both stored in English, but the legend in the viz shows the types in Portuguese when that language is selected. Ideally the Type should have just come straight from the data source.
Add the 3 parameters to the view, and then add Colour to the Colour shelf. Set the pCountry parameter to UK, and choose Appetizers from the pOptions-UK parameter. Adjust the colours to suit. Manually sort the colour legend options, so the colours are listed Red > Rose > Sparkling > White > No Highlight.
Then clear the pCountry parameter (or set it to any value other than UK) and set the pOptions-Portuguese parameter to Aperitivos. Once again adjust colours as required and re-order.
The tooltips need to be language specific, and only display for the wines that match the options chosen. For these we need the following calculated fields
Tooltip – Wine
IF [Wine has Tag] THEN [Wine (Wine)] ELSE ” END
Tooltip – Food Pairing
IF [Wine has Tag] THEN IIF([pCountry]=’UK’,[Food pairing],[Food pairing (Vinho)]) ELSE ” END
Tooltip – Food Pairing Label
IF [Wine has Tag] THEN IIF([pCountry]=’UK’,’Food pairing:’, ‘Harmoniza com:’) ELSE ” END
Tooltip – Notes
IF [Wine has Tag] THEN IIF([pCountry]=’UK’,[Notes],[Notes (Vinho)]) ELSE ” END
Add all four of these fields to the Tooltip shelf and adjust accordingly
Finally tidy up the formatting by removing row dividers and hiding the Country to Display column heading (hide field labels for rows).
Building the Country Selector
For this I needed to add the UK and Brazilian flags a custom shapes to my Tableau repository. I just sourced some images via my favourite search engine and added them to my repository as per the instructions here.
On a new sheet I then added Abreviation to Columns and also to Filter and just filtered to the first 2 options Ab and Ag. I changed the mark type to shape. I then created the field
Country Selector
IIF([Abreviation]=’Ab’,’UK’,’Brazil’)
and added this to the Shape shelf, and selected the two flags.
Remove the row dividers and hide the header (uncheck show header on the Abreviation pill).
Building the dashboard
Add the sheets to your dashboard and display both the options parameter and the colour legend. I used layout containers for most of the arrangement, but floated the colour selector viz. I then added the title in English and Portuguese in two separate text boxes. I also hid the title of the colour legend, and add 2 different text boxes with the colour legend title in English and Portuguese. My layout looked like
To only show the sections based on the language selected, we need the following fields
Country is UK
[pCountry]=’UK’
Country is Brazil
[pCountry]<>’UK’
On the dashboard, select the text box which contains the English title, then on the Layout tab, select the Control visibility using value checkbox, and choose the Country is UK field.
Repeat this for all the other objects – the other title, the food selector parameters and the colour legend titles, choosing the Country is UK or the Country is Brazil option as appropriate.
To switch the language, we need to add a dashboard parameter action
Select Country
On selection of the Country Selector viz, set the pCountry parameter by passing in the value from the Country Selectior field.
The final step is to stop the country flags from being ”greyed out’ ‘highlighted’ when one is selected (ie the other flag ‘greying out’).
Create a fields
True
TRUE
False
FALSE
and add these 2 fields to the Detail shelf of the Country Selector worksheet.
The back on the dashboard, add a dashboard filter action
Country Selector – Unhighlight
on selection of the Country Selector sheet on the dashboard, target the Country selector sheet directly, passing the fields True = False. Show all values if the selection is cleared.
And with that, the viz should be complete. My published version is here.
For this week’s #WOW2023 challenge , guest poster, Venkatesh Iyer asked us to create an UpSet Plot, with the added requirement of using just 1 calculated field.
To start with, I had to read up on what an UpSet plot was and looked through the blog post by Chris Love that was referenced in the challenge introduction. While this post gave me more clarity, it introduced more calculations than I was hoping for, so I started looking a bit wider for a bit of help. This YouTube video set me on my way.
Setting up the data
The requirements stated to limit to the Category of Furniture only, so after connecting to the Superstore data source, I added a Data Source Filter (right click data source > Edit Data Source filters) to restrict the information throughout the workbook just to the Furniture Category.
Doing this means I don’t have to keep adding the Category to the Filter shelf, and any FIXED LoDs I create will only be based on the subset of data that has been ‘pre-filtered’.
There are multiple charts in this challenge, and I used 5 sheets in total. Let’s start with the easy ones.
Building the Customer List
On a new sheet add Customer Name to Rows and Sales to Text. Format Sales to $ with 0dp, and widen each row. Remove row/column dividers and remove the ‘Customer Name’ column heading (right click and hide field labels for rows). Uncheck the Show Tooltips option from the Tooltip shelf. Name the sheet Customer List.
Building the Sub-Category Bar Chart
Add Sub-Category to Rows. Add Customer ID to Columns, then use the context menu to change the field to use the Count (Distinct) measure.
Note– I would typically create a specific calculated field containing the function COUNTD([Customer ID]), but as we only want a single calculated field in the solution, then this is the method to adopt.
Sort the resulting bar chart descending, and add Sub-Category to the Colour shelf and adjust to suit.
Widen each row and then click on the Label shelf and check the Show mark labels tick box. Align the labels middle left and format the font. Hide the axis and the Sub-Category column heading (uncheck show header). Remove all gridlines, row/column dividers/ zero lines etc. Uncheck the Show Tooltips option from the Tooltip shelf. Name the sheet Sub Cat Bar.
Identifying the groupings
Ok, now we’re at the point we need to identify the different ‘cohorts’ of customers based on what Sub-Categories they have purchased. Let’s build out a tabular ‘check’ sheet so we can see what we’re up to…
On a new sheet add Customer Name and Sub-Category to Rows. This simple table shows us that Aaron Bergman has at some point only ever bought Bookcases & Chairs, while Aaron Hawkins has purchased Chairs & Furnishings. These 2 customers are in different cohorts as they haven’t bought exactly the same combination of Sub-Categories. There are 15 different combinations in total.
Based on what I observed in the video, I can create a FIXED LoD calculated field to identify if a customer has bought Bookcases.
Pop this into the view, and we can see that there is a 1 reported against all the rows associated to each customer who bought Bookcases. So Aaron Bergman has a 1 against both rows, and Aaron Hawkins has 0 against both rows.
Creating similar calculated fields, specific for each of the 4 different Sub-Category values, and putting them into the table, we can see we have various combinations of 1s and 0s for each customer. Adam Shillingburg has bought all 4 types, so has 1’s across the board, while Adrian Sharni has only bought Furnishings, so has 3 0’s and a single 1.
Based on our understanding of what these fields are doing, we can combine what each one is doing into a single calculated string field.
Note the order is based on the order of Sub-Category bar chart. Add this into the view on Rows (rejig the order of the measure values to match).
So with this one calculated field Combo, we now have a dimension we can use to count the customers against. The calculated fields I used to demonstrate the concept are now superfluous and can be deleted if you wish, if you remove the check sheet too. I chose to keep mine in for reference.
Building the Combo bar chart
ON a new sheet, add Combo to Columns and then add Customer ID to Rows, but as before, set it to use the COUNTD aggregation.
Sort the bar to be ascending. Check the Show mark labels option on the Label shelf and adjust the alignment to be bottom middle, and the font to be bold. Change the colour of the bar to suit. Hide the axis and the Combo heading (uncheck show header). Remove all gridlines, row/column dividers/ zero lines etc. Uncheck the Show Tooltips option from the Tooltip shelf. Name the sheet Combo Bar.
Building the dot plot
Add Sub-Category to Rows and Combo to Columns. Manually sort the Sub-Category rows so they are listed with Furnishings at the top then Chairs > Tables and Bookcases at the bottom. Sort the Combo field by COUNTD of Customer ID ascending
Double click into Columns and manually type MIN(0) to generate an axis. Change the mark type to circle. Add Sub-Category to Colour. Widen each row.
Add Sub-Category to Label. Then double click into the pill on the label shelf and manually change to add the LEFT function around the pill, so the pill becomes LEFT([Sub-Category],1) to get the initial. Again, this is typically something I would explicitly store in its own calculated field. Manually re-sort the rows again, as this seems to break the sorting.
Align the label middle centre and bold the font. Then add another instance of MIN(0) on Columns to create a 2nd marks card. Change this mark type to Line. Remove the field from the Text shelf, and move the Sub-Category pill from Colour to Path.
Make the chart dual axis and synchronise axis. Right click on the MIN(0) axis title at the top of the chart and move marks to back.
Then hide the axis and the Combo column and Sub-category row (uncheck show header). Remove all gridlines, row/column dividers/ zero lines etc. Adjust the size of the marks as required. Uncheck the Show Tooltips option from the Tooltip shelf. Name the sheet Dot Plot. .
Building the Legend
The quickest way to do this is to duplicate the dot plot sheet. Then remove Combo from the Columns. mark type of the line from line to circle and decrease the size to the smallest possible. Move Sub-Category from Detail to Label and align middle right.
Edit the label and add some spaces in front of the text to push the labels further to the right. Format the font to suit.
Adjust the MIN(0) fields to both be MIN(0.2) instead (just double click into the fields to edit). Then edit the axis to be fixed from 0 to 1. This forces the display to the left.
Hide the axis, and name the sheet Legend.
Adding the interactivity
Arrange the items on a dashboard, using layout containers and padding to organise the 4 main charts and ensure the dot plot aligns both vertically and horizontally with the other two bar charts.. The legend chart is a floating object.
Add a filter dashboard action to filter the customer list
Filter Customers
On select of the Combo BarorSub Cat Bar sheets, target the Customer List sheet, showing all values when the selections are cleared.
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.
For this week’s #WOW2023 challenge, Kyle wanted us to build a viz that used selections on the viz rather than a set of filter controls to show how the sales for those selections were distributed.
This concept is referred to as proportional brushing and makes use of set actions to achieve the results. The complexity added here was the multiple selections being made.
6 sheets make up this dashboard – 1 for each bar chart, 1 for the KPI and 1 for the breadcrumb trail.
Building the basic bar charts
Create 4 sheets, one for each of the Region, Segment, Ship Mode and Sub-Category dimensions. The simplest way is to build one sheet, get all the formatting applied etc, then duplicate and replace the dimension on the duplicated sheet with the new one.
When building the first sheet, place the dimension (eg Region) on Rows and Sales on Columns, sorted descending. Adjust the Sales to be formatted to $ with 0dp. Hide the Sales axis, and format to remove all gridlines/axis lines/ zero lines and row/column dividers. Show mark labels and align centrally. Adjust the font label to 8pt. Widen each column if need be. Hide the dimension label from displaying (hide field label for columns). Adjust the tooltip to suit. Name the sheet based on the dimension.
Then duplicate this sheet, and drag the next dimension, eg Segment, and drop it directly on Region. If done properly, everything should seamlessly update. Re-name this sheet accordingly, then repeat the process until you have a sheet for each of the four dimensions.
Applying the proportional brushing
Create a set for each of the relevant dimensions.
Region Set – right click on the Region field in the data pane and select Create > Set. Select all the options to be in the set.
Repeat and do the same for each dimension, so you end up with Segment Set, Ship Mode Set and Sub-Category Set.
We need to determine the combination of all the values selected in each set. So we need
Is Selected Options
[Segment Set] AND [Ship Mode Set] AND [Region Set] AND [Sub-Category Set]
This returns true for all the records in the data which match the combined selections of the individual sets.
On the Region sheet, add Is Selected Options to the Colour shelf. The right click on each set in the data and and select Show Set, so the set of selections are listed on the canvas.
Change the options so only the Segment Consumer and rthe Ship Mode Standard Class are selected, along with all Region and Sub-Category values. Adjust the colours associated to the True and False values that are now presented
If need be, adjust the tooltip so the Is Selected Options is not displaying, then add the Is Selected Options field to the Colour shelf of the Segment, Ship Mode and Sub-Category sheets. Play with the set selections to see how the bars change. Once you’re familiar with the behaviour, reset all the sets so they all contain all the values.
Building the KPI sheet
On a new sheet add Sales to Text. Change the mark type to shape and select a transparent shape (see this blog to get this set up). Adjust the Label to include the text ‘Sales’ and format accordingly. Align middle centre. Add Is Selected Options to the Filter shelf and set to True.
Again, if you adjust the set selections, the value will adjust accordingly.
Building the Dashboard interactivity
Add the sheets onto a dashboard. I used both vertical and horizontal layout containers to get the objects positioned where I wanted. I also used blank objects set to height/width of 1px and with a black background colour to create the horizontal and vertical divider lines. You can see from the item hierarchy in the image below, how I laid out my dashboard (I like to rename my containers to help understanding)
Now add a dashboard change set values action for each of the 4 bar chart sheets.
Select Region
On select of the Region sheet only, target the Region Set. On running the action (ie clicking the bar), assign values to set, and when clearing the selection (clicking the bar again), add all values to the set.
Note – While not specified in the requirements, I noticed that the breadcrumbing functionality in Kyle’s solution didn’t behave if multiple selections of the same dimension were made – eg 2 regions were selected. I decided to add the requirement of only allowing a single dimension to be clicked (ie the single-select only box is checked).
Create a Select Segment, Select Ship Mode and Select Sub-Category set action using the same principals described above.
Creating the breadcrumb
I’ve added this last, so you understand how we can ensure each set only has either all the values in it, or just 1 value.
To create the breadcrumb, we’re going to build up some strings based on what the state of each set looks like. This involved several calculated fields…. I’m not sure if I’ve over complicated this though..
Anyway firstly, we want to capture the values that have been added to each set, so we need
Regions in Set
IF [Region Set] THEN [Region] END
Segments in Set
IF [Segment Set] THEN [Segment] END
Ship Modes in Set
IF [Ship Mode Set] THEN [Ship Mode] END
SubCats in Set
IF [Sub-Category Set] THEN [Sub-Category] END
The image below shows how each of these fields are behaving based on the set selections – if the value is not selected in the set, the Regions in Set field is Null.
Next we have fields to count how many different values exist in each of these fields.
Count Selected Regions
{FIXED: COUNTD([Regions in Set])}
Count Selected Segments
{FIXED: COUNTD([Segments in Set])}
Count Selected Ship Modes
{FIXED: COUNTD([Ship Modes in Set])}
Count Selected SubCats
{FIXED: COUNTD([SubCats in Set])}
Again you can see from the sheet below, this is counting the number of selections, which is ‘fixed’ (ie the same) for every row.
Now, while this is showing 2, as we’ve manually clicked on the set options, in practice when driven from the dashboard, we’re either going to have all values in the set, or just 1. So based on this assumption, we now just want to get the name of the single selection
Selected Region
IF SUM([Count Selected Regions]) = 1 THEN MAX([Regions in Set]) ELSE ” END
If there’s only 1 item in the set, then get it’s value, otherwise return ‘blank’.
Just testing this behaviour, we can see below that with all the Regions selected, the Selected Region field is empty, but with 1 value selected, we show that value.
Create equivalent fields for each dimension
Selected Segment
IF SUM([Count Selected Segments]) = 1 THEN MAX([Segments in Set]) ELSE ” END
Selected Ship Mode
IF SUM([Count Selected Ship Modes]) = 1 THEN MAX([Ship Modes in Set]) ELSE ” END
SelectedSubCat
IF SUM([Count Selected SubCats]) = 1 THEN MAX([SubCats in Set]) ELSE ” END
The order of the dimensions displayed in the breadcrumb is fixed, regardless of the order in which you click the options. That is, if you click a Segment then a Region, the breadcrumb will display the <segment> followed by the <region>. But if you click the Region first and then the Segment, the breadcrumb will still display the<segment> followed by the <region>. Based on this, we can create string values for each dimension that differ depending on whether we know there is a selection made against a subsequent dimension (ie should we include the ‘>’ character or not).
Let’s go through in order. Firstly, no selections made
All Segmentations BC
IF [Selected Segment]=” AND [Selected Ship Mode]=” AND [Selected Region]=” AND [Selected SubCat]=” THEN ‘All Segmentations’ END
If all the ‘selected’ values are empty, then all the sets contain all the values, so display ‘All Segmentations’.
If there are selections made, then the dimensions are ordered as Segment > Ship Mode > Region > Sub-Category
Segment BC
IF [Selected Segment]<>” AND ([Selected Ship Mode]<>” OR [Selected Region]<>” OR [Selected SubCat]<>”) THEN [Selected Segment] + ‘ > ‘ ELSE [Selected Segment] END
If there is only 1 Segment selected and at least 1 of the other dimensions has been selected too, then add the ‘>’ character after the Segment name, otherwise just show the Segment.
Ship Mode BC
IF [Selected Ship Mode]<>” AND ([Selected Region]<>” OR [Selected SubCat]<>”) THEN [Selected Ship Mode] + ‘ > ‘ ELSE [Selected Ship Mode] END
Similar to above, but this time, we only need to compare with the dimensions that are below Ship Mode in the display hierarchy.
Region BC
IF [Selected Region]<>” AND [Selected SubCat]<>” THEN [Selected Region] + ‘ > ‘ ELSE [Selected Region] END
There is only one dimension below Region. As Sub-Category is at the bottom of the ordering, we don’t need anything special – the value of the Selected SubCat field will do.
On a new sheet, add All Segmentations BC, Segment BC, Ship Mode BC, Region BC and Selected SubCat to the Text shelf. Change the mark type to shape and change to use a transparent shape.
Adjust the label, so all the fields are ordered correctly and positioned exactly next to each otherwith no spacing/carriage returns between. Align the label middle left.
Show the set controls, and then test the functionality by altering the selections, ensuring either only 1 value or all values are selected
Once you’ve finished testing, ensure all values are selected in all sets.
The add this sheet to the dashboard – I had the title and the breadcrumb in a vertical container, which was the left hand side of a horizontal container
And hopefully that should be it. My published viz is here.
The challenge this week was to provide a balance between the user requesting ‘all the numbers’ and the visualisation designer trying to encourage ‘best practice’. It was inspired by Sam Parsons‘ viz here, which he discusses in this video.
We’re going to use 2 sheets to build this, one to display the text and one to display the bars. But first we need to define some parameters for the user to drive the display
pDisplayType
string parameter containing a list of two options, and defaulted to ‘Bars’
pShowExpanded
boolean parameter, defaulted to False
The measures we’ll be displaying are the Sales in 1000s and the Profit Ratio percentage, so we’ll need
Sales (k)
[Sales]/1000
format to 1 dp
and
Profit Ratio %
(SUM([Profit])/SUM([Sales]))*100
custom format this to ▲ #,##0.0;▼ #,##0.0 (you can use this site to copy & paste the symbols required).
Let’s start to build out the tabular view.
Add Category and Sub-Category to Rows. Add Region and Order Date (as a blue, discrete pill, at the YEAR level) to Columns. Add Measure Names to Filter and filter to Sales (k) and Profit Ratio %. Add Measure Values to Text. Add Measure Names to Columns in front of the Order Date field. Swap the order of the pills in the Measure Values box.
Add subtotals (Analysis > Add all Subtotals) and set Row Totals to Left and Column Totals to Top. Add Column Grand Totals (Analysis > Show Column Grand Totals).
Set the totals, so they are displaying the average values (Analysis > Totals > Total All Using > Average)
Right click on the label ‘Grand Total’ in the table, and format. In the left hand pane, change the label from ‘Grand Total’ to ‘All Categories’
Right click on the ‘Total’ label in the columns and format and change the Totals label to ‘Avg per Yr’
Now we have the content of the table, we need to add the functionality to expand/collapse (hide or show) the Year columns based on the pShowExpanded parameter. For this we need
Show / Hide Columns
IF [pShowExpanded] THEN ‘Show’ ELSE ‘Hide’ END
Add pShowExpanded to the view, and add Show/Hide Columns to Rows.
You should see that the value for Show/Hide Columns is only visible against the Year columns and not against the sub totals. With pShowExpanded set to False the value displayed is ‘Hide’
Now right click on the text ‘Hide’ and select the Hide option from the context menu
The Year columns will disappear. Change pShowExpanded to True, and voila! the columns are displayed. Uncheck Show Header against the Show/Hide Columns so the text ‘Show’ is not visible when expanded.
The ‘hide’ option is a really sneaky trick, but so very effective at not displaying specific rows/columns without filtering them out. It isn’t obvious it’s been applied though (there’s no clues in the canvas), so as a top tip, I recommend always adding a note to the worksheet caption to remind your future self (or a colleague who needs to maintain the workbook), that this has been applied.
Formatting the table
Now the functionality has been nailed, we need to apply the formatting & styling to the table.
I set the fonts to Tableau Medium, colour #7d4e47 throughout. The ‘All Categories’ Grand Total label was 11pt, the Category labels 9pt and the Sub-Category labels 8pt. The Region labels were 11pt, the Measure Names labels were 9pt and the ‘Avg per Yr’ and YEAR(Order Date) labels all 8pt. The Text of the numbers in the main body of the table were all set to 8pt too.
The background of the workbook was set to cream: #fefaf1
and then the total and grand total rows were coloured #faf4e7 and #f5eddc accordingly
To colour the font, add Measure Values to Colour, and right click and select Use SeparateLegends
This will then give you two colour legends.
Adjust the Sales (k) colour legend – select any diverging colour palette, set Stepped Colour to 2 steps, and check Include Totals, and then click on the colours at each end and set both to the same brown (#7d4e47).
Repeat similar steps for the Profit Ratio % colour legend, but this time also set the range to be centred at 0, and apply different colours to the ends (#ff6f6a and #7d4e47).
Remove the Category & Sub-Category and Region/Order Date labels from displaying (hide field labels for columns/rows), and adjust the width and height of the cells to give a bit more spacing. Hide the tooltips from showing.
Create the bar table
Duplicate the sheet. Add Measure Values to Columns, and the click on the Label shelf and uncheck Show Mark Labels. Click on the Colour shelf and change the opacity to 35%. Reduce the Size of the bar to suit, then hide the Measure Values axis (uncheck Show Header).
Click on the Tooltip shelf and enable tooltips, then adjust accordingly.
Re-apply any of the formatting to the text that may have been lost – for some reason I lost all the Tableau Medium style fonts…???
Swapping the sheets on the dashboard
Create a dashboard, add a vertical layout container and add both sheets.
We will make use of dynamic zone visibility to only display the sheet we want based on the value of pDisplayType. To do this, we need some more calculated fields
Show Bar
[pDisplayType] = ‘Bars’
Show Numbers
[pDisplayType] = ‘Numbers’
Go back to the dashboard. Add the pDisplayType parameter. Click on the Bar sheet so it is selected (grey box) and on the Layout tab, check the Control visibility using.. checkbox and choose the Show Bar field.
Repeat the same steps for the Text table, but select the Show Numbers field instead. Depending what value is displayed in the pDisplayType parameter, one of the sheets should disappear, and changing the pDisplayType value should swap between them.
For the challenge this week, Sean wanted us to identify the sales of ‘new’ products compared to previous years. This challenge was born out of a requirement he had at work, where he discovered the definition of a ‘new product’ was different to what he’d assumed.
In this instance, the ‘client’ only cared about comparing sales in the same month across the years. Any sales related to the products outside of the specific month were irrelevant and were to be ignored. A product was then counted as ‘new’ in the first year (of the specified month) that there was a sale.
In this case we are always looking at the month of September (the previous month to ‘today), so if Product X was sold in Sept 2021 and Sept 2023 only, then Product X would count as new in Sept 2021, and only the sales for Product X in Sept 2021 would be included in the subsequent values displayed.
Now I thought I’d nailed my understanding of the requirement, and went ahead building out a tabular view of the data using table calculations, but my final output did not match Sean’s result – in fact it was quite way off. I stepped through my logic, even sharing some samples with Sean to sense check, and we seemed to concur on the products identified as new, so I was incredibly puzzled as to what was causing the value discrepancy.
I messaged my friend, Rosario Gauna, to share my numbers and see what she came up with…. she matched Sean! Doh! I then had to spend time dissecting my solution and Rosario’s to see which products I was/wasn’t counting. Eventually I found the problem….
In using table calculations, I had included all the fields the viz required which included the Segment (Home Office, Corporate, Consumer). This meant that in my logic to identify a ‘new product’, I was counting the product as new in the first year that the product was sold in each Segment – ie if Product X sold in Sept 2022 against Segment Home Office and then sold in Sept 2023 against Segment Corporate, I counted both sales as being ‘new’ as the segment differed. However I only wanted the Sept 2022 sale to count as ‘new’.
With this understood, I tried to revise my initial table calc build, but to no avail. So I opened up a new workbook and started again, this time taking a different track. This is what I’ll blog, but I felt it important to explain how even a requirement I thought I’d understood, could still get mis-interpreted.
Building out the calculations
The first requirement is that we’re only looking at comparing the data for the previous month to ‘today’. If this was being developed for a live business application we’d reference the TODAY() function, but since I want this viz to continue to display on Tableau Public after we hit 2024, then I’m going to use a parameter to hardcode ‘today’.
pToday
date parameter defaulted to 4th October 2023
From this, I can then determine the month we need to compare
working from right to left (inside to out), this takes ‘today’ and finds the 1st of the month (ie 1st October), then goes back 1 month (ie 1st September), then gets the month number ie 9.
As this returns a number, it automatically is listed in the ‘measures’ section of the data pane (under the line), but I dragged it into the top half as I want the number to be discrete.
I then created
Filter Month
DATEPART(‘month’, [Order Date]) = [Month to Compare]
which returns true for all the orders where the month of the Order Date is in September.
Let’s build up a table so we can start to see what we’re working with
Add Product Name and Filter Month to Rows, Order Date (at the Year level) to Columns and Sales to Text.
In the example above, 3-ring staple pack has sales in September 2022 and in other months in 2020 and 2021, but we don’t care about those months, so this product is counted as new in 2022, with a value of $11.
3M Hangers With… have no sales in September in any year, so we won’t be counting that product at all.
6″ Cubicle Wall Clock has sales in both September 2022 and 2023, so this product will be counted as new in 2022 with a value of $83.
So we can filter the date just to Filter Month = True (move Filter Month from Rows to the Filter shelf).
Now we want to identify the earliest year for each Product Name.
Add this onto Rows, and you can see below we’re picking up the right year.
Now we can identify the value of the sales we want to count.
New Product Sales
IF [Year First Purchased in Month] = YEAR([Order Date]) THEN [Sales] END
Only get Sales for the year that matches the minimum year. Format this field to $ with 0 dp.
Adding this in to the table, we can see we only have a value against the first year.
So we’ve identified the sales values of the products we care about. We can now aggregate this at a higher level, and incorporate the other dimensions.
Remove Sales from Text, add Category to Rows and Segment to Columns. Remove Product Name and Year First Purchased in Month.
The viz displays the % of total new product sales by Category per Segment. Right click on the New Product Sales pill and add Quick Table Calculation, selecting Percent of Total. Right click on the pill again and select Compute Using -> Category. Right click on the pill and Format and format to % with 0dp. Add another instance of New Product Sales back into the table.
Now we want to be able to compare the sales for the current year/category/segment against that of the previous year.
Previous Year Sales
LOOKUP(SUM([New Product Sales]),-1)
This looks up the New Product Sales value of the previous (-1) field. Add this field into the table, and right click and Edit Table Calculation. Adjust so the calculation is computing by Year of Order Date only.
You should be able to see that the values from the previous year for each Segment & Category are now displayed against each cell. As there is no data for 2019, there are no values listed for this field against the 2020 fields.
Now we have the values we want to compare in the same ‘row’ of data, we can compute
% Diff From Previous Year
(SUM([New Product Sales]) – [Previous Year Sales]) / [Previous Year Sales]
custom format this to ▲0%;▼0% (use this site to get the images to copy & paste), and add this into the table ensuring the table calculation is set to compute using Year of Order Date only, as you did above.
And now we have all the components needed to build the viz.
Building the bar chart
Duplicate the table sheet, then apply the following steps
Move % of Total New Product Sales to Rows
Move Category to Colour and adjust accordingly
Move New Product Sales and % Diff from Previous Year to Label
Remove Previous Year Sales
Add another instance of % of Total New Product Salesto Label ( I tend to do this by pressing ctrl, and then clicking on the pill in the Rows and dragging onto Text – this creates a duplicate of the pill and preserves the table calculation settings. Otherwise add an instance of New Product Sales to Label, then add the Percent of Total Quick Table Calculation and ensure the pill is set to compute by Category.)
Move Segment to be in front of the Year(Order Date) field on Columns – if this changes the viz display, ensure the mark type is set to bar.
Tidy up the display by
removing all gridlines/zero lines etc
remove row dividers
Adjust title of the y-axis
Hide the Segment/Order Date column heading
Hide the tooltips
The text and formatting of the labels also needs to be adjusted. We don’t want the ‘vs PY’ text displaying for the 2020 years when there is no difference from previous year.
Label PY Text
IF NOT ISNULL([Previous Year Sales]) THEN ‘vs PY’ END
Add this to the Label shelf and ensure the table calculation is set to compute using YEAR of Order Date only. Then adjust the layout of the label text and the format the size of the text.
Donna + DataViz 