Tag: Parameters

Binary Parameters

Donna ColesLeave a comment

This week’s challenge was a guest post by Felicia Styer, who wanted us to make multiple selections using just a single parameter, rather than any groupings or sets.

We’ll start by focusing on the initial requirement, which was to bucket into Sub-Categories selected and those not.

Setting up the parameter

The main functionality is controlled via a single string parameter which will just contain a string of 0s and 1s. 0 indicates the Sub-Category is not selected, 1 indicates it is. The position of the 1 or 0 in the string represents the associated Sub-Category. There are 17 Sub-Categories in the data set, so we need to create a parameter of 17 characters. Arbitrarily set some entries to 1 and the rest to 0.

Binary Parameter

string parameter containing the string 01000101000010000

Building the Sales by Subcategory viz

Add Sub-Category to Rows and Sales to Columns. Sort by Sales descending.

We want to assign a number for each row. We can use Index() or Rank the rows based on Sales. I chose to to the latter

Sub Cat Rank

RANK_UNIQUE(SUM(Sales), ‘desc’)

Format this to a number with 0dp but prefixed with #

Add this to Rows, change to discrete (blue pill) and then move to be listed before Sub-Category.

We want to colour the bars based on the ‘bucket’ they’re in according to the Binary Parameter.

Bucket

MID([Binary Parameter],[Sub Cat Rank],1)

This returns the character that is in the nth position in the string – ie Tables is ranked fourth, so this calculation will return the 4th character in the Binary Parameter string.

Add this to Colour and adjust accordingly. Show the Binary Parameter on the sheet, and then adjust the values between 1 and 0 to see the bars change colour.

When a bar is clicked, we want to update the parameter. We will use a dashboard parameter action to drive this functionality, but we need to pass a value into the parameter. This value needs to be a 17 character string of 1s or 0s, where only the character at the nth position based on the rank needs to differ.

For example, the string 01000101000010000 indicates Phones is selected – it’s ranked 2nd in the list and the 2nd character of the string is a 1. When Phones is clicked, we want it to become unselected. So the character in the 2nd position needs to change to a 0, while all the other characters remain the same.

Value for Param

IF [Bucket] = “0” THEN
LEFT([Binary Parameter],[Sub Cat Rank]-1) + “1” + MID([Binary Parameter],[Sub Cat Rank]+1)
ELSE
LEFT([Binary Parameter],[Sub Cat Rank]-1) + “0” + MID([Binary Parameter],[Sub Cat Rank]+1)
END

If the row is currently in Bucket 0, then get the portion of the Binary Parameter string before the nth term, concatenate it to 1 and then concatenate that with the portion of the Binary Parameter string after the nth term, otherwise, the row is associated to Bucket 1, so concatenate the preceding and following string with a 0 instead.

Add this to the Detail shelf.

Finalise the display by adding Sales to the Label, removing row & column dividers, updating the Tooltip, hiding the column headers and adjusting the font size.

Adding the selection interactivity

Add the sheet to a dashboard. Add a dashboard parameter action

Set Bucket

On selection of the sheet, set the Binary Parameter parameter passing the value of the Value for Param field. Leave the parameter with the current value when selection is cleared.

Click a bar to test the functionality. The bars should be changing colour. However, on click, Tableau automatically highlights the selected bar and the others ‘fade’. We’re already using colour to identify what’s been selected, so don’t want this to happen. To resolve this we will apply the True/False method to deselect the marks which is documented here.

You will need to create True and False calculated fields, and add them to the Detail shelf of the viz sheet. Then add a dashboard filter action as below.

Now when you click, the bars immediately change to the right colour with a single click of the mouse.

Building the Sales by Bucket bar chart

On a new sheet, add Sales to Columns, Bucket to Rows and Sub-Category to Detail. Adjust the table calculation setting of the Bucket pill so it is computing explicitly by Sub-Category.

Add Bucket to Colour and again adjust the table calculation as above. When you hover over the bar, you will see it is actually a stacked bar of each Sub-Category. We want these ordered so those with the smallest sales are on the left. Apply a Sort to the Sub-Category field on the Detail shelf to sort by Sales Descending

Widen each row. Add Sales to Label and set the Label to only show when selected (so they only appear when the segment of the bar is clicked on)

Add a Reference line to the Sales axis that shows the Sum of Sales per cell, and displays the Value. Don’t show any line or tooltip, and ensure the reference line isn’t recalculated when the bar chart is clicked.

Format the reference line label so it is positioned right middle, and adjust the font size.

Once again remove any row/column dividers and row headings and adjust the font sizes.

Arrange this chart onto the dashboard with the other chart using layout containers as required.

My version of this challenge is published here.

Bonus Challenge

For the bonus challenge to use more than 2 buckets, there was no example actually published. So I interpreted it as each click added it to the next bucket until you reached the maximum number of buckets allowed, at which point the Sub-Category would become deselected.

For this I created a parameter

# of Buckets

integer parameter from 1 to 5

The expectation in this instance was that rather than a string of 1s and 0s in the parameter, the parameter could contain any number from 0 up to # of Buckets – 1.

So the Value for Parameter field just had to change to become

IF [Bucket] = “0” THEN
//we’ve clicked once so move it to 1st bucket
LEFT([Binary Parameter],[Sub Cat Rank]-1) + “1” + MID([Binary Parameter],[Sub Cat Rank]+1)
ELSEIF [Bucket] = STR([# of Buckets]) THEN
//we’re already at the end, so reset to the starting position of 0
LEFT([Binary Parameter],[Sub Cat Rank]-1) + “0” + MID([Binary Parameter],[Sub Cat Rank]+1)
ELSE
// need to move to the next bucket
LEFT([Binary Parameter],[Sub Cat Rank]-1) + STR(INT([Bucket])+1) + MID([Binary Parameter],[Sub Cat Rank]+1)
END

The Colours associated to the Bucket field also then need to be updated to handle however many buckets you have, which you can set initially by manually updating the Binary Parameter parameter.

Note – as I have both versions in my workbook, I have fields suffixed with ‘bonus’ to represent the calculated fields/parameters needed.

My published version of this viz is here.

Happy vizzin’!

Donna

Filter for One Value OR All Others

Donna ColesLeave a comment

Erica set the latest challenge, testing us on our ability to master tricky filter scenarios – in this case either show the info for one specific value of a field, or only show the other values, but allow them to be filtered themselves too. The challenge had two parts – the main challenge and a bonus option. I managed to complete both, so will blog both too.

Main challenge – Building the basic viz

On a new sheet add Region and Category to Rows and Sales to Columns. Add Region to Colour and adjust accordingly.

Sort Region by Sales descending

and then click the descended sort button on the toolbar to sort the Category field by Sales too.

Format Sales to be $ with 0 dp. Remove column dividers, and widen each row slightly.

Main challenge – Apply the filtering

Create a parameter

pRegionType

string parameter with 2 options : Not West and West, defaulted to Not West

Create a calculated field to determine whether to display the West Region only, or the other Regions

Filter Region West or Not v1

([pRegionType] = ‘West’ AND [Region] = ‘West’)
OR
([pRegionType] = ‘Not West’ AND [Region] <> ‘West’)

Add this to the Filter shelf and set to True. This is essentially the ‘first level’ filter. Show the parameter and switch between the two values to see the behaviour

Now we need a ‘second’ filter, to allow the relevant Regions to be selected. For this, add Region to the Filter shelf, but select the Use all option

and then show the Region filter list on the canvas, and adjust the settings so only relevant values are displayed

This means when the pRegionType parameter is West, only West will be displayed in the Region filter, but when Not West is selected, all regions except West will display, and the filter can be interacted with in the normal manner.

Main challenge – Building the dashboard

Arrange the viz and the parameters on the dashboard as required, using layout containers, padding and background colours to help organise the content and display required.

We only want the Region selection filter to display when the pRegionType parameter is set to Not West. We can use dynamic zone visibility for this. Create a calculated field

DMZ – Display Filter Control

[pRegionType] = ‘Not West’

and then on the dashboard, select the Region filter and check the Control visibility using value option and select the DMZ – Display Filter Control field.

Bonus Challenge – Building the Viz

Recreate the viz as described above (or duplicate the sheet of the original viz, and remove all the pills from the Filter shelf.

Bonus challenge – Apply the filtering

Create a parameter

pSelectedRegion

string parameter, defaulted to <empty string>

This parameter is going to contain a string that can contain one or more Regions in a delimited format eg | East | or |East||South| etc. The contents of this string will determine how we filter the chart to mimic the required behaviour.

Firstly, we want the ‘1st level’ filter to determine whether we’re displaying just the West Region or all the other Regions.

Filter Region West or Not v2

(CONTAINS([pSelectedRegion],’West’) AND [Region] = ‘West’)
OR
(NOT CONTAINS([pSelectedRegion], ‘West’) AND [Region] <> ‘West’)

Add this to the Filter shelf and set to True. Show the pSelectedRegion parameter. With the parameter empty, the West Region should not display.

Type the word West into the parameter. Now the just the West Region should display.

And if you enter additional text alongside the word ‘West’, still the ‘West’ Region should display

But if you remove the ‘West’ text, all the Regions should display whatever the text is contained.

This behaviour is essentially simulating that of the ‘West’ | ‘Not West’ parameter selection in the previous version.

Now we want to control the 2nd level of filtering where the same parameter is used to drive which of the ‘other’ Regions display.

Filter Other Regions v2

CONTAINS([pSelectedRegion], ‘West’) OR
NOT CONTAINS([pSelectedRegion],[Region])

Set the pSelectedRegion parameter to empty so all Regions are displayed. Add Filter Other Regions v2 to the Filter shelf and set to True.

Enter the text East into the parameter. The East option should disappear.

Add the text ‘South’. That too should disappear

Add the text ‘West’ and only the West Region will show

Play around entering multiple combinations of Regions. Ultimately if the text ‘West’ is present anywhere in the parameter string, only the West Region will display. If West is not present, then any other Region in the string will not be presented in the display. All sounds a bit backwards, but it works 🙂

So now we need to actually control how the pSelectedRegion parameter will get populated. And this will be via a parameter action fired from the selection made from a ‘custom’ legend sheet.

Bonus challenge – Building the filter control

On a new sheet, add Region to Rows and manually type in MIN(0.0) into Columns. Change the mark type to shape. Add Region to Label and show the labels (widen each row slightly). Edit the MIN(0.0) axis to be fixed from -0.1 to 0.5 which will shift the display to the left.

Sort the Region field by Sales descending.

Hide the axis, stop the Tooltip from displaying, hide the Region header, remove all gridlines/ axis rulers/ zero lines, row/column dividers. Set the background colour to light grey.

The Colour and the Shape (filled or unfilled) is determined based on the entries we have captured in the pSelectedRegion parameter, but the logic for each attribute is different.

Colour v2

If [pSelectedRegion] = ‘|West|’ THEN ‘West’ ELSE [Region] END

Show that parameter and make it empty. Add Colour v2 to the Colour shelf. Adjust colour to suit if not already set.

Then enter the text |West| – all the symbols should now all be Navy (or whatever colour you have chosen for West).

For the shape, create

Shape v2

IF CONTAINS([pSelectedRegion] , ‘West’) AND [Region] = ‘West’ THEN ‘Fill’
ELSEIF CONTAINS([pSelectedRegion], ‘West’) AND [Region] <> ‘West’ THEN ‘Empty’
ELSEIF ([Region] <> ‘West’) AND [pSelectedRegion]=” THEN ‘Fill’
ELSEIF ([Region] <> ‘West’) AND NOT CONTAINS([pSelectedRegion],[Region]) THEN ‘Fill’
ELSE ‘Empty’
END

and add to the Shape shelf. Note – this logic took a lot of trial and error to get the desired result.

Whenever the text West exists in the parameter, then the West Region should be a filled circle and all the other regions should be empty (the first 2 lines of the logic statement). If the parameter is empty, we want all the regions (except West) to be filled (so West will be empty). And if the parameter contains a Region(s) that isn’t West, we want that Region to be empty as well – only non-West Regions that aren’t in the parameter should be filled.

To control the text being passed into the pSelectedRegion parameter, we need a field

Region for Param

IF CONTAINS([pSelectedRegion],’West’) THEN ” //West has been selected again so reset parameter to empty
ELSEIF CONTAINS([pSelectedRegion], [Region]) THEN REPLACE([pSelectedRegion], ‘|’ + [Region] + ‘|’ ,”) //selected region is already in the parameter, so remove it ”
ELSE [pSelectedRegion]+ ‘|’ + [Region] + ‘|’ //append current region selected to the existing parameter string
END

Add this to the Detail shelf.

Finally, we will want to ensure the marks aren’t highlighted on selection, so create fields

True

TRUE

False

FALSE

and add these to the Detail shelf too.

Bonus challenge – adding the interactivity

Build the dashboard again using layout containers and background colours and padding

Create a dashboard parameter action

Set Region

On selection of the Filter Control viz, set the pSelectedRegion parameter passing in the value from the Region for Param field. Set the field to <empty string> when deselected

Create a dashboard filter action

Deselect Marks

On select of the Filter Control viz on the dashboard, target the Filter Control sheet itself, passing in the specific fields of True = False.

And this should complete the required elements. My published viz is here.

Happy vizzin’!

Donna

People Resource Planning

Donna ColesLeave a comment

This week’s #WOW2024 challenge was a guest post from Dan Wade using an alternative custom data set focused on staff resource planning.

Creating the parameters

We need 3 parameters for this challenge

pMeasure

string parameter containing a list of 3 options: Budget, Demand, Supply and defaulted to Supply.

pAttrition_Actual

string parameter containing a list of 2 options: Actuals and Attrition and defaulted to Attrition

pRate

a float parameter containing a list of values from 0.05 to 0.12, defaulted to 0.06 and formatted as % with 0 dp.

Building out the calculations

On a new sheet add Forecast Date as a discrete (blue) pill at the month-year level to Rows and add Budget FTE, Demand FTE and Supply FTE and Actuals FTE to Columns vis Measure Names/Measure Values, so you have a tabular display. Show the 3 parameters.

The first 3 measures will be plotted as 3 of the lines on the chart. The 4th line – the Actuals/Attrition line is a calculation based on the parameter selections.

If the pAttrition_Actual parameter displays ‘Actuals’ then we need to show the Actuals FTE as a constant value across every row. From the above, we can see it’s only set against Jan 2024. We will make use of a FIXED LOD calculation to ‘spread’ this value across every row.

However if the pAttrition_Actual displays ‘Attrition’ then we want to calculate a value which is initially a proportion of the Actual FTE, but then is a proportion of the value calculated against the previous month. The requirements also state the rate in the pRate parameter is an annual attrition rate, so we need to apply 1/12 of this value against each month (assuming a linear decline). The calculation we end up with is

Actuals/Attrition FTE

IF [pAttrition_Actual] = ‘Actuals’ THEN SUM({MAX([Actuals FTE])})
ELSE
IF FIRST()=0 THEN SUM([Actuals FTE])
ELSE PREVIOUS_VALUE(0) * (1-([pRate]/12))
END
END

{MAX([Actuals FTE])} is the Fixed LOD which spreads the Actuals FTE value across every row. FIRST() is a table calculation which identifies the first row in the table. PREVIOUS_VALUE(0) looks at the previous value compared to the current row we’re on, and then is reducing it by 1/12 of the pRate parameter. Format this to a number with 2 decimal places.

Add this field to the table and explicitly set the table calculation to compute by Forecast Date.

Adjust the values of the pAttrition_Actual and pRate parameters to see the behaviour of the calculation.

Next we need to calculate the actual difference between this value and the value of the measure selected in the pMeasure parameter. To start we need

Selected Measure

CASE [pMeasure]
WHEN ‘Supply’ THEN SUM([Supply FTE])
WHEN ‘Demand’ THEN SUM([Demand FTE])
WHEN ‘Budget’ THEN SUM([Budget FTE])
END

and then we can create

FTE Difference

[Selected Measure] – [Actuals / Attrition]

Add this to the table. Verify the setting of the table calculation. Adjust the pMeasure value to see the changes.

In order to colour the bars on the viz, we need to know the % difference

% FTE Difference

[FTE Difference]/[Selected Measure]

format this to % with 2 dp and then create

% Diff > 20%

IF [% FTE Difference] > 0.2 THEN ‘Outside 20% range’ ELSE ‘Within range’ END

Add this to Rows and verify the output.

Building the Viz

On a new sheet add Forecast Date as a continuous (green) pill at the month/year level to Columns. Add Budget FTE, Demand FTE and Supply FTE to the same axis, using Measure Values/Measure Names. Adjust colours accordingly. Edit the y-axis and uncheck Include zero and change the axis title.

Add Actuals/Attrition FTE to the Measure Values section and ensure the table calculation is set to compute by Forecast Date. Adjust colour.

Add Measure Names to the Label shelf. Adjust to align right and central and set the font to match mark colour. Edit the date axis, adjust the axis title, then set the axis to have a fixed end of 31 Jul 2027. This gives some space for the labels to display.

Adjust the Tooltip to suit.

Then add Actuals/Attrition FTE to Rows, making sure the table calculation is set as it should. Change the mark type to Gantt Bar. Remove Measure Names from the Label and Colour shelf of this marks card.

Add FTE Difference to the Size shelf, adjusting the table calc setting. Then add % Diff > 20% to the colour shelf. Set the colours accordingly, and reduce the opacity to 25%.

Add Budget FTE, Demand FTE and Supply FTE to the Tooltip shelf then adjust the tooltip as required, making reference to the parameters to make the tooltip ‘dynamic’

Make the chart dual axis and synchronise the axis.

Show the parameters and adjust to see how the chart behaves with the different settings. Hide the right hand axis, remove row/column dividers, but make the axis lines slightly more prominent than the gridlines. Update the title and again reference the parameters so the text is dynamic.

Then add the chart to a dashboard and edit the parameter/legend titles as required.

My published viz is here.

Happy vizzin’!

Donna

Can you visualise headcount distribution in multiple ways on a single sheet?

Donna Coles5 Comments

This week’s challenge focuses on allowing the user flexibility in both how they want to ‘slice’ the measure being reported (actual headcount), and how they want the results displayed to meet the needs of the question they are trying to answer. Whilst the above could be built using multiple sheets and ‘sheet swapping’ using dynamic zone visibility, the aim is to try to build within a single sheet.

Let’s get cracking.

Enhancing the data

Not all the fields we need to slice the data by are included initially in the data provided, so we need to define some additional calculated fields, as stated in the requirements.

Contract Type

IF [FTE] = 1 THEN ‘Full Time’ ELSE ‘Part Time’ END

To determine the Age Bracket an employee belongs to, we first need to know their age. Usually this would be based on today’s date (using the TODAY() function), but as this data is static, we’re going to pretend today is 01 Dec 2022, by capturing this in a parameter.

pToday

date parameter defaulted to 01 Dec 2022

We can then work out how old the employee is (in complete years) on 01 Dec 2022

Employee Age

IF DATEPART(‘dayofyear’, [Birth Date])<=DATEPART(‘dayofyear’,[pToday]) THEN
DATEDIFF(‘year’, [Birth Date], [pToday])
ELSE
DATEDIFF(‘year’, [Birth Date], [pToday])-1
END

The ‘dayofyear’ parameter of the DATEPART function returns as it suggests the day of the year, so is a number from 1 to 365 (or 366 in a leap year). So this calculation is basically saying, if the birthday falls before ‘Today’, then they’ve had their birthday in that year, so a simple difference between the years suffices, otherwise the birthday hasn’t happened yet, so take one off the difference in years.

We can then create

Age Bracket

IF [Employee Age] < 20 THEN ‘Under 20’
ELSEIF [Employee Age] < 30 THEN ’20-29′
ELSEIF [Employee Age] < 40 THEN ’30-39′
ELSEIF [Employee Age] < 50 THEN ’40-49′
ELSEIF [Employee Age] < 60 THEN ’50-59′
ELSE ‘Over 60’
END

Setting up the parameters

All the user interactivity is driven through selection of parameters.

pDisplayType

string parameter defaulted to Butterfly Chart with a list of values Stacked, Side by Side, Butterfly which each have a slightly different Display As value (up to you whether you want to do this).

pDisplayRowsBy

string parameter defaulted to Age Bracket with required options listed.

pSplitBarsBar

string parameter defaulted to Contract Type with required options listed

Building the Viz

The pDisplayRowsBy parameter defines the field that we want to display on the Rows shelf. The parameter is just a string value though which we need to map to a proper field from the data set

Dimension – Rows

CASE [pDisplayRowsBy]
WHEN ‘Age Bracket’ THEN [Age Bracket]
WHEN ‘Contract Type’ THEN [Contract Type]
WHEN ‘Department’ THEN [Department]
WHEN ‘Gender’ THEN [Gender]
WHEN ‘Nationality’ THEN [Nationality]
END

The pSplitBarsBy parameter defines the field that will be used to colour the bars. Again we need to map this to fields in the data set

Dimension – Colour

CASE [pSplitBarsBy]
WHEN ‘Gender’ THEN [Gender]
WHEN ‘Contract Type’ THEN [Contract Type]
END

The existing Employees(Count) field that is automatically included is just a counter for the number of rows in the data set, so is essentially our headcount number. However, we need to think about how the display needs to look for the different types of chart, specially the butterfly chart.

When building a butterfly chart, the bars to the right of the middle axis are plotted as positive numbers, while the bars to the left are plotted as negative numbers (so the middle is actually 0). So in the event the display type of Butterfly Chart is selected, we need to make some adjustments, so that some of the values are plotted on the negative axis. This means our calculation looks like

Headcount

IF [pDisplayType] = ‘Butterfly’ THEN
IF MIN([Dimension – Colour]) IN (‘Part Time’, ‘Female’) THEN COUNT([Employees]) * -1
ELSE COUNT([Employees])
END
ELSE
COUNT([Employees])
END

If it’s a Butterfly chart, then if the values associated to the field we’re colouring the bars by are ‘Part Time’ (pSplitBarsBy = Contract Type) or ‘Female’ (pSplitBarsBy = Gender), then negate the count of employees, otherwise just return the count of employees.

Let’s start putting some of this into a viz to see what we’ve got…

On a sheet, add Dimension-Rows to Rows and Headcount to Columns. Add Dimension-Colour to Colour. Show the 3 core parameters and adjust the colours to suit.

Change the pSplitBarsBy to Gender, and adjust colours again. Change to ‘N/A’ and adjust again, so all possible options for the Dimension-Colour field have been set. Change pSplitBarsBy back to Contract Type.

Now change pDisplayType to Stacked Bar.

We want Full Time segment of the bar to appear first against the zero line of the axis, so manually re-order the options in the colour legend, so Part Time is listed first.

Change pSplitBarsBy to Gender and verify that the Male segment of the bars are displaying first against the zero line of the axis. Change pSplitBarsBy back to Contract Type.

For the side by side bar chart, we need another dimension on Rows that is essentially the Dimension -Colour field. However, we only want the value when the pDisplayType is Side by Side. So we need

Dimension – Side by Side

IF [pDisplayType]=’Side By Side’ THEN [Dimension – Colour] ELSE ” END

Add this to Rows after the Dimension-Rows field.

This adds an additional column to the display. While we’re in side by side or butterfly ‘mode’ , nothing is displayed. Change pDisplayType to Side by Side and we get the separation required.

Since the Colour legend is defining what the bar relates to, we can hide this field (right click on the Dimension – Side by Side pill and uncheck show header.

We want to also show the % of Total headcount split for each row. Create a new field

Total Employees Per Row

{FIXED [Dimension – Rows] : COUNT([Employees])}

then create

% Total Headcount

[Headcount]/SUM([Total Employees Per Row])

and format to a custom number format of 0%;0%. This is a % with 0 decimal places, but the custom format is used as we want negative numbers (caused by the butterfly chart type) to be displayed as +ve percentages.

Add % Total Headcount to Columns and verify the display for the different display types. Hint – if the calc is working as expected, the Stack Bar option should show 100% 🙂

Labelling the Viz

We can’t simply add labels using the usual method (show mark labels) and adjusting the positioning, as it won’t always show what we want, where we want for all the different display types. So we need to employ different techniques based on the chart being displayed.

Let’s start with the stacked bar chart. For the Headcount bars, we want to display the total number of employees at the end of the bar, and for the % Total Headcount, we want the % displayed in the middle of the bar.

Create a new field

Ref Line – Stacked

IF [pDisplayType] = ‘Stacked’ THEN [Headcount] END

Add this to Detail shelf of the Headcount marks card, then add a reference line to the Headcount axis (right click axis > Add Reference Line) that for each pane references the Sum of the Ref Line – Stacked field, and just displays the Value on the Label. No line or tooltip is displayed.

Format the reference line (right click the ‘invisible’ line) to adjust the colour and alignment of the font.

Add % Total Headcount to the Label shelf of the % Total Headcount marks card. You may need to adjust the width of the bars so you can see the values. Adjust the font to be white.

Change the pDisplayType parameter to Side by Side. No labels should be displayed. This is because the label we used for the headcount bar was based on the display type, and the label we used for the % total has been coloured white and is therefore invisible on a white background (set it to black and you’ll see it displayed). So again we need more fields

Ref Line – Side By Side

IF [pDisplayType] = ‘Side By Side’ THEN [Headcount] END

Add this to the Detail shelf of the Headcount marks card, and again add a reference line to the Headcount axis, this time setting the scope at the cell level.

Once again format the reference line to set the font and alignment.

Create

Label – % of Total Side by Side

IF [pDisplayType] = ‘Side By Side’ THEN [% Total Headcount] END

Format to a % with 0 dp and add this to the Label shelf of the % Total Headcount marks card, and then adjust the Label so this field is appropriately coloured and listed before the other label (which is coloured white).

Change the pDisplayType parameter to Butterfly chart. The % labels should already display. We just need to sort the labelling for the actual headcount. For this we need

Ref Line – Butterfly Pos

IF [pDisplayType] = ‘Butterfly’ THEN
IF [Headcount] >= 0 THEN [Headcount] END
END

and

Ref Line – Butterfly Neg

IF [pDisplayType] = ‘Butterfly’ THEN
IF [Headcount] < 0 THEN [Headcount] END
END

Add both these fields to the Detail shelf of the Headcount marks card. Then add reference lines for each of the two fields

Then format both the reference lines so the font and alignment is set as required (in the case of the Ref Line – Butterfly Neg reference line, it needs to be left aligned)

Add Headcount and % Total Headcount to the Tooltip shelf of the All marks card, and then adjust the tooltip. Finally tidy up by

  • Adjusting format of the Dimension – Rows values and aligning middle left.
  • Hide field labels for rows
  • Remove column dividers
  • Adjust row dividers to be more subtle
  • Remove gridlines, zero lines, axis ticks & rulers
  • Remove the Headcount & % Total Headcount axis.

Add the viz to a dashboard. I used a horizontal container placed above the viz to add text objects to represent the headings. The first text box just the referenced the pDisplayRowsBy parameter value, so it was dynamic.

My published viz is here.

Happy vizzin’!

Donna

Can you rebuild this Tube Map?

Donna ColesLeave a comment

Lorna called on the help of a data schooler, Olivier Newman to set this week’s challenge, which will be part one of a 2-part challenge.

For those of you who are regular readers of my blog, you’ll know that working with maps and spatial data isn’t something I do often, so challenges like this always start with me feeling a little bit daunted by what’s required.

Side Note – I originally built this challenge using Tableau Desktop v2024.1, but encountered some issues with getting the data on the map updated as I made changes to the selections – the selection changes were visible on other tabular sheets, just not on the map, unless I forcibly refreshed the data source. Recreating in Tableau Desktop v2023.3 was fine. And the version published from v2024.1 to Tableau Public also worked fine on Tableau Public. I have raised this to Tableau via Slack channels I have access to, so if you experience similar issues, that may be why…

Understanding the data and the requirement

I initially spent some time trying to understand how the data matched up to the information I could see on the viz, specifically what was being listed in the Arrival Station selection box.

I found, every Station was associated with a Line, but the Station could be associated to more than one Line. Every Line was associated to a Branch, but again, the Line could be associated with more that one Branch. Picking some specific Stations as an example…

  • Amersham Station is associated to 1 Line (Metropolitan) which is associated to 1 Branch (Metropolitan Line Branch 0) – so Amersham is associated to 1 Branch
  • Bank Station is asscociated to 3 Lines (Central, Northern, Waterloo) which in turn are only associated to 1 Branch each – so Bank is associated to 3 Branches
  • Acton Town Station is associated to 2 Lines (District and Piccadilly); District is associated to 1 Branch which Piccadilly is associated to 2 Branches – so therefore Acton Town is associated to 3 Branches.

The list of possible Arrival Stations is based on the set of Stations associated to any of the Branches the Starting Station is associated to.

So for Amersham, we’re looking for all those Stations on the metropolitan branch 0 Branch

For Bank we’re looking at Stations on the central 0, northern 1 and waterloo 0 Branches

and for Acton Town, we’re looking at stations on the district 0, piccadilly 0 and piccadilly 1 Branches.

So first we need to find a way to

  1. Identify the Starting Station
  2. Identify the Branches the Starting Station is associated with
  3. Identify the Stations associated to these Branches.

Identifying the Arrival Stations

To start with, we need to capture the starting station, which we can do with a parameter

pStart

String parameter which is a List object that populates from the Station field when the work book is opened, and is defaulted to Bank.

For the rest, we’ll build up what we need step by step, so on a new sheet add Branch and Station to Rows and display the pStart parameter.

I’m first going to identify the possible Branches associated to the pStart station, and ‘spread’ this across all the stations in that Branch

Possible Branches

{FIXED [Branch] : MIN(IF [Station] = [pStart] THEN [Branch] END)}

If the Station in the row matches that in pStart, then get the Branch for that row, then ‘spread’ that across all the rows with the same Branch (via the {FIXED [Branch]: …. } statement.

Add this onto Rows and you’ll see the name of the Branch is listed against all the stations associated to the branch that the pStart station is related to

Now we can define a field to capture the stations that have a Possible Branch

Possible Destination Stations

IF NOT ISNULL([Possible Branches]) THEN [Station] END

Add this to Rows too, and stations should only be listed against those rows with a Possible Branch

We can use this field to then create a Set. Right click on Possible Destination Stations > Create > Set

Destination Stations Set

Select Epping from the list displayed

Add the field to the Colour shelf (the Epping row should be coloured IN the set). Then click on the pill on the Colour shelf and select Show Set

The list of possible options in the Destination Stations Set should be displayed. Change the control type to be single value dropdown

Now test the behaviour of the set by changing the value of the pStart parameter eg select Amersham. Epping remains selected but is now contained in ( ) as it’s not a valid value. The other options to select though should all now have changed.

This is the ‘relative values’ only type behaviour required.

Determining the number of stops

While we’re working with a ‘check sheet’, let’s finalise the other calculations we’re going to need to build the final viz; firstly the number of stops between the two selected stations. We’re going to use the Path Order field to help with this.

Firstly, if it’s appearing as a string in the data set, convert it to a numeric whole number field, then add it to Rows between Branch and Station It should be a discrete dimension (blue disaggregated field). A unique number should be listed against each record; this record is effectively an index defining the order of the Stations on the Branch.

Let’s reset the station parameters to start at Bank and end at Epping These stations are on the Central 0 Branch, and Bank is at Path Order 47 and Epping at 61

The number of stations is the absolute difference between these two numbers. To determine this, we need to capture the Path Order for the starting station against every row.

Now, it’s possible that the stations are on multiple branches, so we need to make sure we have a handle on the Branch we care about

Selected Branch

{FIXED: MIN(IF [Destination Stations Set] THEN [Branch] END)}

Get the Branch associated to the selected destination station, and then ‘spread this’ across all rows.

Add this to Rows.

Now we can get the number associated to the pStart station on the Selected Branch, and spread this across every row

Starting Station Path No

INT({FIXED: MIN(IF [pStart] = [Station] AND [Branch] = [Selected Branch] THEN [Path Order] END)})

as well as

Destination Station Path No

INT({FIXED: MIN(IF [Destination Stations Set] AND [Branch]=[Selected Branch] THEN [Path Order] END)})

Add both of these as discrete dimensions to Rows

Then we can create

No. of Stops

ABS([Starting Station Path No] – [Destination Station Path No])

which is just the absolute difference between the two

Identifying the stations between start & end

The final piece of the puzzle, that we’re going to need is just to isolate all the Stations on the Branch that lie between the pStart station and the station in the Destinations Station Set. As this is going to be used to highlight the section of line on the map, I called this

Highlight Line

[Path Order] >= MIN([Starting Station Path No],[Destination Station Path No]) AND [Path Order] <= MAX([Starting Station Path No], [Destination Station Path No])

Here I utilised the rarely used (at least in my case) feature of the MIN and MAX functions, that allows you to supply multiple values and return a single value – the MIN or the MAX of the options provided. So in this case, I want to flag all the rows as being true if the Path Order sits between the Starting Station Path No and the Destination Station Path No. Add this onto Colour instead of the In/Out set and we can see all the rows between the two endpoints are highlighted.

Test by trying different start and ends, so you’re happy how the behaviour is working.

Building the tube map

This did take a bit of time to get right, and I did end up referring to Tableau’s own KB article on creating paths between origin and destination to get some pointers (although I didn’t follow it to the letter…)

Create a new sheet, then create a spatial field

Station Location

MAKEPOINT([Right Latitude], [Right Longitude])

and double click to automatically add the field to the new sheet. Longitude and Latitude fields are automatically generated and a basic layout is immediately visible

Add Branch to Detail then change the mark type to Line.

Add Path Order to Path. The lines should all now join up as expected

Delete all the text from the Tooltip, but ensure Show Tooltip is still enabled.

Set the background of the map to dark (Map menu > Background Maps > Dark). Adjust the Colour of the line to whatever suits (I used #01e6ff)

Add a 2nd map layer – drag Station Location onto the canvas and drop when the Add a marks layer option appears

Change the Mark type of this 2nd marks card to circle, then add Station and Line to the Detail shelf. Change the colour to same as the line and adjust the Size if required. Update the Tooltip as required.

To highlight the stations between those selected, create a new spatial field, just for those stations

Selected Stations

IF [Highlight Line] THEN [Station Location] END

Drag this on to the canvas to make a 3rd marks layer.

Add Branch to Detail, change the Mark type to line and add Path Order to Path. Change the Colour to something contrasting (I chose #ff00ff). Adjust the Size so the line is a bit thicker than the other lines.

To label the start & end station, create

Label – Stations

IF [Station] = [pStart] OR [Destination Stations Set] THEN [Station] END

Add to the Label shelf, and change to be an attribute (rather than dimension) so it doesn’t break up the line. Adjust the font accordingly. I set it to Tableau Medium 8pt bold in white, aligned top centre. All the labels to overlap other marks.

Show the pStart parameter and the Destination Stations Set list (just right click on the field in the data pane on the left and select Show Set – this is now an option as there are fields already on the viz that reference that set). Test the display by changing the options.

Add No of Stops to the Detail shelf, then update the title to reference the field. Set the font to white and align right.

Format the background of the whole worksheet to black, remove row/column dividers. Hide the null indicator field, and remove all map options (Map menu > map options, uncheck all the fields).

The viz should now be ready.

Add it onto a dashboard, which is also formatted to have a black background. Display the pStart parameter and the Destination Stations Set as floating objects. Update the title of each and format the latter so it has a black shading to the body of the control. Remove the ‘all’ option from the arrival station control (customise > uncheck show ‘all’ value).

My published version is here. Hopefully I’ve built it in a way that supports the impending Part 2…

Happy vizzin’!

Donna

Can you dynamically zoom into a clustered area?

Donna ColesLeave a comment

For this week’s #WOW2024 challenge, Lorna revisited an old #WOW2021 challenge based on Hans Rosling’s chart used in his famous TED talk.

Modelling the data

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

  1. Life Expectancy csv
  2. Population csv
  3. Income csv
  4. 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.

Happy vizzin’!

Donna

Can you build a dashboard for pre-aggregated metrics?

Donna Coles1 Comment

For the challenge this week, Kyle asked us to recreate the visualisation above using an adapted version of Superstore which had a customer count metric for 3 dimensions (Category, Segment and Region) along with ‘no’ dimension (null) pre-aggregated at a Yearly or Monthly Level.

By this I mean that, at a Yearly level, when the date was 1st Jan 2019 say, a row of data existed for the (distinct) customer count of all the combinations of the 3 dimensions and null. In total 80 rows for the one date.

As the data was pre-aggregated, it made no sense to say the customer count for Technology is the sum of all the rows where Category = Technology and this would mean data was being double counted.

Pivoting the data also wouldn’t yield the desired result. So the aim of this challenge was to be able to identify the relevant rows of data that needed to be displayed based on the options selected by the user.

Building the calculations

Parameters will be driving the user selections, so these need to be set up

pDateGrain

string parameter with a list of 2 options: Monthly and Yearly. Defaulted to Monthly.

pColour

string parameter with a list of 4 options : Category, Region, Segment, None. Defaulted to Segment

Similarly, create pXAxis and pYAxis parameters similar to above, but default both to None.

On a new sheet build a tabular view with

  • Table Names, Category, Segment and Region on Rows
  • Order Date set to discrete (blue pill) exact date on Columns
  • Customer Count on Text
  • Show all 4 parameters created

The rows of data need to be filtered by Table Name (as defined by the pDateGrain parameter) and a combination of Category, Segment and Region based on the options selected in the other 3 parameters.

To filter by the Table Name we need

Filter – Date Grain

[pDateGrain] = [Table Names]

Add this to the Filter shelf and set to True.

Change the pDateGrain parameter to Yearly as there is less data to see/check.

Based on the options selected in any of the other 3 parameters, we need to find matching rows.

For example, if pColour is Segment and the other parameters are None, we are looking for the rows where the Segment column is not null, but the Region and Category columns are (we would be after the same rows if pXAxis was set to Segment, and the other parameters were None or, if pYAxis is Segment and the other parameters were None).

In this case, we’re looking for 3 rows of data – those highlighted below

If instead any two of the parameters were set to Segment and Category and the other None, then we’d be looking for rows where Segment is not null, Category is not null and Region is null. This would be 9 rows in total (a snippet of which is shown below).

We also need to deal with scenarios where all three parameters were set to something different, or all set to None as well as handle if multiple parameters are set to the same thing.

Now to do this, I ended up building a single field to use as filter that contains all the scenarios. As I was building it up, I figured there should be a slicker way, and there is (check out Kyle’s solution), but if your brain is wired the same way as mine, then you’ll end up with this

Filter – Rows to Include

IF [pColour] = ‘None’ AND [pXAxis] = ‘None’ AND [pYAxis] = ‘None’ THEN
//no options selected
IF ISNULL([Region]) AND ISNULL([Category]) AND ISNULL([Segment]) THEN TRUE END
ELSEIF (([pColour] <> ‘None’ AND [pXAxis] = ‘None’ AND [pYAxis] = ‘None’) OR
([pColour] = ‘None’ AND [pXAxis] <> ‘None’ AND [pYAxis] = ‘None’) OR
([pColour] = ‘None’ AND [pXAxis] = ‘None’ AND [pYAxis] <> ‘None’)) THEN
// one of the 3 options selected, so work out which dimension
IF [pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’ THEN
IF ISNULL([Region]) AND ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END
ELSEIF [pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’ THEN
IF ISNULL([Region]) AND NOT ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END
ELSEIF [pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’ THEN
IF NOT ISNULL([Region]) AND ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END
END
ELSEIF (([pColour] <> ‘None’ AND [pXAxis] <> ‘None’ AND [pYAxis] = ‘None’) OR
([pColour] <> ‘None’ AND [pXAxis] = ‘None’ AND [pYAxis] <> ‘None’) OR
([pColour] = ‘None’ AND [pXAxis] <> ‘None’ AND [pYAxis] <> ‘None’)) THEN
// two options selected, so work out which dimensions we need
IF ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) AND ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) THEN
IF ISNULL([Region]) AND NOT ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END
ELSEIF ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) AND ([pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) THEN
IF NOT ISNULL([Region]) AND ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END
ELSEIF ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) AND ([pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) THEN
IF NOT ISNULL([Region]) AND NOT ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END
//or the two options selected are the same dimension
ELSEIF ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) AND ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) THEN
IF ISNULL([Region]) AND ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END
ELSEIF ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) AND ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) THEN
IF ISNULL([Region]) AND NOT ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END
ELSEIF ([pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) AND ([pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) THEN
IF NOT ISNULL([Region]) AND ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END
END
ELSE //all three selected, but they could be all the same dimension or 2 of the three the same
IF ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) AND ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) AND ([pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) THEN
//all three different
IF NOT ISNULL([Region]) AND NOT ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END
ELSEIF ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) AND ([pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) THEN
IF NOT ISNULL([Region]) AND ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END
ELSEIF ([pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’) AND ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) THEN
IF ISNULL([Region]) AND NOT ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END
ELSEIF ([pColour] = ‘Region’OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’) AND ([pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’) THEN
IF NOT ISNULL([Region]) AND NOT ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END
ELSEIF [pColour] = ‘Category’ OR [pXAxis] = ‘Category’ OR [pYAxis] = ‘Category’ THEN
IF ISNULL([Region]) AND ISNULL([Segment]) AND NOT ISNULL([Category]) THEN TRUE END
ELSEIF [pColour] = ‘Segment’ OR [pXAxis] = ‘Segment’ OR [pYAxis] = ‘Segment’ THEN
IF ISNULL([Region]) AND NOT ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END
ELSEIF [pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’ THEN
IF NOT ISNULL([Region]) AND ISNULL([Segment]) AND ISNULL([Category]) THEN TRUE END
END

END

Blimey! A bit monolithic I know, but it just grew organically as I tried out the different scenarios step by step. Unfortunately the above doesn’t copy over the formatting nicely, as there are nested (tabbed) IF statements which makes it (a bit) easier to read.

Suffice to say, I’m not going to walk through step by step, but it’s checking for all the different permutations are discussed above, and marking the relevant rows as True. This field can then be added to the Filter shelf and set to True.

Kyle’s solution, essentially replaces this one calculated field, with 3 calculated fields – 1 per parameter – which are all then added to the filter shelf. It’s much neater 🙂

So now we’ve identified the rows we want based on parameters, but there is also the ability to filter the rows further based on the values of the Category, Segment or Region.

Add each of the 3 fields to the Filter shelf and select the All option, then show the filters on the view. For each of the Category, Segment and Region filters, set the option to show Only Relevant Values. This will prevent the NULLs from showing as an option when the relevant dimension is listed as one of the parameter selections

As you can see from the above image though, Region is only showing Null, and this is because in the example above, Region isn’t selected as an option for the pColour, pXAxis or pYAxis parameters. When it comes to the dashboard, we don’t want the Region filter to be visible in this case. To help with this, we need 3 further calculated fields.

Show Filter – Region

[pColour] = ‘Region’ OR [pXAxis] = ‘Region’ OR [pYAxis] = ‘Region’

This returns True if one of the 3 parameters contains the value ‘Region’. Similarly, create Show Filter – Category and Show Filter – Segment fields.

The final calculated fields we need are to help build the ‘cross tab’ view.

X-Axis

CASE [pXAxis]
WHEN ‘Category’ THEN [Category]
WHEN ‘Region’ THEN [Region]
WHEN ‘Segment’ THEN [Segment]
ELSE ”
END

Y-Axis

CASE [pYAxis]
WHEN ‘Category’ THEN [Category]
WHEN ‘Region’ THEN [Region]
WHEN ‘Segment’ THEN [Segment]
ELSE ”
END

Colour

CASE [pColour]
WHEN ‘Category’ THEN [Category]
WHEN ‘Region’ THEN [Region]
WHEN ‘Segment’ THEN [Segment]
ELSE ”
END

Now we’ve got all the fields needed to build the viz.

Building the viz

The quickest way is to duplicate the sheet we’ve built, as all the filters need to apply, so

  • Duplicate the sheet
  • Remove all the fields from Rows
  • Change the Order Date field on Columns to be continuous (green pill)
  • Add X-Axis to Columns
  • Add Y-Axis to Rows
  • Move Customer Count to Rows
  • Add Colour to the Colour shelf.
    • Adjust the colours to suit.
    • Change the value of the option in the pColour parameter, and readjust the colours. Repeat so that colours are set for Category, Segment and Region.
  • Add Colour to the Label shelf

Remove all gridlines, axis and zero lines. Remove the Y-Axis and X-Axis row/column labels by right clicking the text and selecting Hide field labels for rows/columns. Edit the Order Date axis (right click axis -> Edit) and remove the axis title.

Add Order Date to Tooltip and format it to the ‘March 2001’ date format. Adjust the tooltip as below

Hiding the filters

Add the viz to a dashboard and arrange the parameters and filter controls in the relevant location. I used layout containers to help with the organisation.

Select the Category filter and on the Layout tab, select the Control visibility using value checkbox and select the Show Filter – Category field.

Repat the same steps for the Region and Segment filters, selecting the equivalent calculated fields.

And that should be it. My published viz is here.

Happy vizzin’!

Donna

Custom Map Analysis

Donna Coles1 Comment

For the final week of Community Month, my colleague, Nik Eveleigh, posed this challenge to apply some tricky filters to a map. Let’s dive straight in!

Identifying the States and Profitability

The core functionality of the map display is driven by a parameter allowing the user to select which set of states they want to analyse, so let’s set this up first.

Select a grouping

string parameter containing 6 entries in the list and defaulted to ‘Top 20 States by Sales’

To identify the top 20 States by Sales, we need to create a Set. Right click on State/Province > Create > Set and use the ‘Top’ option to create the set based on top 20 sum of Sales.

Top 20 States by Sales

To verify this is working as expected, add State/Province to Rows, Sales to Text and sort descending. The add Top 20 States by Sales to Rows and you should see the first 20 rows with In and the rest listed as Out.

To identify the states selected when the ‘Custom States List’ option is selected, we’ll also need another set to store the selections. Right click on State/Province > Create > Set, leave the list of states all unselected and rename the set

Selected States

To verify this is working, on a new sheet add State/Province to Rows and Selected States to Rows too. Then on the context menu of the Selected States pill, choose Show Set to get the list of States displayed. Select the first couple in the list and see the value in the table change from Out to In.

It is this set control filter list that will be used to provide the selection when the appropriate value in the Select a grouping parameter is chosen.

While the sets display In or Out when shown in the table, they are actually booleans with the equivalent of a True or False. We now need to build a boolean field which will encapsulate all the relevant states included based on the parameter option selected.

Filter States

CASE [Select a grouping]
WHEN ‘Top 20 States by Sales’ THEN [Top 20 States by Sales]
WHEN ‘Central Region’ THEN IF [Region] = ‘Central’ THEN TRUE ELSE FALSE END
WHEN ‘Southern Region’ THEN IF [Region] = ‘South’ THEN TRUE ELSE FALSE END
WHEN ‘Eastern Region’ THEN IF [Region] = ‘East’ THEN TRUE ELSE FALSE END
WHEN ‘Western Region’ THEN IF [Region] = ‘West’ THEN TRUE ELSE FALSE END
WHEN ‘Custom States List’ THEN [Selected States]
END

Let’s sense check the workings of this too. On a sheet add State/Province, Region and Filter States to Rows. Show the Select a grouping parameter. Also click on the Selected States field in the left hand data pane and Show Set, so the list of states shows. Ensure all are unchecked. Depending on the option selected in the Select a grouping parameter, then Filter States column should display true or false.

If you select a state from the list, this should only present as True when the Custom States List option is selected

Right, now we know how to identify the states we want, we can start to look at understanding their profitability. Firstly we need to get the profit ration

Profit Ratio

SUM([Profit])/SUM([Sales])

format this to % with 0 dp.

Profitability

IF ATTR([Filter States]) THEN

IF [Profit Ratio] < -0.25 THEN ‘Highly Unprofitable’

ELSEIF [Profit Ratio] >= -0.25 And [Profit Ratio] < 0 THEN ‘Unprofitable’

ELSEIF [Profit Ratio] >=0 AND [Profit Ratio] < 0.25 THEN ‘Profitable’

ELSE ‘Highly Profitable’

END

ELSE ‘Not Included’
END

If the states is one of the filtered ones then work out isn’t profitability ‘bracket’ otherwise report as ‘Not Included’.

Building the Map

On a new sheet double-click on State/Province to automatically generate the map. If the map doesn’t display with US and Canada Edit Locations (via the Map menu) and ensure your settings are as below

Display the Show a grouping parameter and have it set to Top 20 States by Sales. Add Profitability to Colour and adjust colours accordingly.

Click on the Selected States field in the left hand data pane and Show Set, so the list of states shows, then test changing the values in the Select a grouping parameter and see the display change.

Clean the map up by clicking Map -> Background Layers, and then unchecking all the options in the Background Map Layers section displayed on the left hand side.

To label the highlighted state we need

Label – PR

IF ATTR([Filter States]) THEN [Profit Ratio] END

format to % with 0 dp and then add to the Label shelf and set the font to bold.

Add Profit Ratio to the Tooltip shelf and adjust the tooltip.

To display the summary Profit Ratio values for all the filtered states vs those not selected, we need

PR by Filter State

{FIXED [Filter States]: [Profit Ratio]}

and then

PR Selected States

ZN({FIXED:AVG(IF [Filter States] THEN [PR by Filter State] END)})

Custom format this with 0%;-0%;–

This formatting with show values with 0dp for positive and negative values and — when no values exists.

Repeat the process to create

PR Non Selected States

ZN({FIXED:AVG(IF NOT([Filter States]) THEN [PR by Filter State] END)})

and apply the same formatting above.

Add both PR Selected States and PR Non Selected States to the Detail shelf and change the aggregation to Average.

Then click on a state on the bottom left of the map (I chose California) and select Annotate > Mark. Add the reference to the PR Selected States and supporting text into the annotation dialog. The when completed, manually move the annotation to the space to the left of the map. Format the annotation to add a border, round the edges and remove the line. You many need to re-edit the annotation to rec-centre the text.

Repeat a similar process, by annotating a state on the right hand side and referencing the PR Non Selected States field instead.

Finally remove all row & column dividers and hide the map options (Map -> Map Options -> uncheck all selections)

Hiding the Selected States control

In order to control visibility of the Selected States list, we need a boolean field

Custom States Selected

[Select a grouping] = ‘Custom States List’

Once all objects have been added to the dashboard and arranged where you want, click on the Selected States control, so it is selected via a grey border, then on the left hand Layout pane, select the Control visibility using value checkbox and choose the Custom States Selected field

The State list will now only display when the Select a grouping parameter contains the ‘Custom States List’ value.

And that should be it. My published viz is here.

Happy vizzin’!

Can you use dynamic axis titles?

Donna Coles1 Comment

Lorna used this week’s challenge to showcase a new feature in Tableau v2023.1 – dynamic axis titles. As you can expect, you’ll therefore need this version of Desktop (or later if you’re reading this in the future ;-)) to complete the challenge.

Modelling the data

Download the file Lorna provided and connect to the 2023 sheet. Lorna hinted that a pivot would help, so in the data source canvas, multi-select all the measures (ctrl-click each column – there are several) then right-click and Pivot.

Rename Pivot Field Names to Measure and rename Pivot Field Values to Value.

Building the Basic Scatter Plot

We need 2 parameters to control the selection of the measures we want to display in the scatter plot. Right click on Measure > Create > Parameter

pMeasure1

string parameter defaulted to Metres, and all the possible other values should be listed

Repeat the steps again to create pMeasure2 which is defaulted to Tackles

To determine the value to plot on the axes based on the selections from the parameters we need

X-Axis Value

IF [pMeasure1] = [Measure] THEN
IF [Value] <> 0 THEN [Value] END
END

Note – the additional nested IF was added as I discovered while playing with the Lorna’s solution that marks didn’t display when the value was zero.

Similarly we need

Y-Axis Value

IF [pMeasure2] = [Measure] THEN
IF [Value] <> 0 THEN [Value]END
END

Add X-Axis to Columns, Y-Axis to Rows, Team Name to Rows and Name to Detail. This will give a basic scatter plot.

Change the mark type to circle, adjust the colour, and reduce the opacity to around 70%. Add a grey border to the circles.

The size is based on the number of games the player has played, so we need

Count Games

COUNTD([Game ID])

Add this to the Size shelf.

Adjust the Tooltip so it references the parameters and the other relevant fields

To change the axis titles, edit the x-axis (right click axis > edit axis) and from the menu arrow next to the word ‘custom’, select the pMeasure1 option.

Repeat the same for the y-axis, but select pMeasure2 instead.

Making the small multiples

For this we need to define which row and column each Team Name should sit in. As we’ve only got 12 teams to work with, and that number is static, and we know we’re working with a 3×4 grid, I’m going to ‘hardcode’ this a bit rather than use more dynamic calculations.

To see what’s going on, on a new sheet add Team Name to Rows. Then create a new field

INDEX

INDEX()

and add this to Rows and convert to discrete. It should provider a counter from 1 to 12 for each Team Name.

Based off this INDEX value we’ll work out which row and colum each team will sit.

Column

(INDEX()-1) % 3

Row

IF [INDEX]<=3 THEN 1
ELSEIF [INDEX]<=6 THEN 2
ELSEIF [INDEX]<=9 THEN 3
ELSE 4
END

Add these to the table as blue discrete pills

Back onto the scatter plot sheet, add Row to Rows as a blue discrete pill, add Column to Columns as a discrete pill, and move Team Name to Detail. Modify the table calculation settings of both the Row and the Column pill so that the calculation is computed using Team Name and Name (in that order) and at the level of Team Name

Hide the Column and Row pills (uncheck Show Header)

Adding the Team Name title

Create a new field

Ref Line

WINDOW_MAX(SUM([Y-Axis Value])) * 1.1

and add this to the Rows. This will create a second marks card. Set the table calcuation of the Ref Line pill to compute using Name and Team Name (in that order).

On the Ref Line marks card, remove the pill from the Size shelf, change the mark type to gantt bar, and reduce the size to the smallest possible, set the opacity to 0%, and border to none.

Add Team Name to the Label shelf, then set to label min/max values, by the X-Axis Value field and Label Minimum value only.

Each team name should only be displayed once. Edit the text of the label and add a few spaces to shift the label across and align it better.

Edit the Tooltip of this marks card, and delete all the text. Now make the chart dual axis and synchronise the axis. Then remove Measure Names from the All marks card, and hide the right hand axis.

Finally remove the 0 lines from displaying, and hide the nulls indicator (right click). Add the chart to a dashboard, and position the parameters as floating objects within the text of the title. I just used spaces within the text to leave room for where I wanted to place the parameterss.

And that should be it. My published viz is here.

Happy vizzin’!

Donna

Calculating Year-on-Year Percentage Change

Donna Coles1 Comment

In this week’s #WOW2023 challenge, Erica asked us to show the data for the selected year for a set of EU countries, but within the tooltip, provide additional information as to how the data compared to the same month in the previous year.

A note about the data

For this we needed to use the EU Superstore data set, a copy of which was provided via a link in the challenge page. Since part of validating whether I’ve done the right thing is to have the same numbers, I often tend to use any link to the data provided, rather than use any local references I may have to data sets (ie I have so many instances of Superstore on my local machine due to the number of Tableau instances I have installed). I did find however, that using the data from the link Erica provided, I ended up with a data set spanning 2015-2018 rather than 2016-2019. However I quickly saw that the numbers for each year had just been shifted by a year, so 2018 in Erica’s solution was equivalent to the 2017 data I had.

The viz is also just focussed on a subset of 6 countries. I chose to add a data source filter on Country to restrict the data to just those countries required (right click data source in the data pane -> Add data source filter).

Building out the required data calculations

The data will be controlled by two parameters relating to the Year and the Country

pYear

integer parameter, defaulted to 2017, displayed using the 2017 format (ie no thousand separators). 3 options available in a list : 2016,2017,2018

pCountry

string parameter defaulted to Germany. This is a list parameter and rather than type the values, I chose the option Add values from -> Country

We need to use the pYear parameter to determine the data we want to display, rather than simply apply a quick filter on Order Date, as we need to reference data from across years. Simply filtering by Order Date = 2017 will remove all the data except that for 2017, and so we won’t be able to work out the difference from the previous year. Instead we create

Sales Selected Year

ZN(IF [pYear] = YEAR([Order Date]) THEN [Sales] END)

Wrapping within ZN means the field will return 0 if there is no data.

Format this to € with 0 dp.

We can then also work out

Sales Prior Year

ZN(IF [pYear]-1 = YEAR([Order Date]) THEN [Sales] END)

which then means we can work out

Diff From PY

(SUM([Sales Selected Year]) – SUM([Sales Prior Year])) / SUM([Sales Prior Year])

custom format this to +0.0%;-0.0%;0.0%

This will display a positive change in the format +12.1%, a negative change as -12.1% and no change as 0.0%

Let’s pop all this information out in a tabular view along with the Country and Order Date to sense check the numbers

This gives us the core data to build the basic viz.

Core viz

Add Order Date at the Month date part level (blue pill) to Columns and Sales Selected Year to Rows and Country to Colour. Make sure it’s a line chart (use Show Me) if need be. Adjust the colours accordingly.

Amend the Order Date axis, so the month names are in the abbreviated format (right click on the bottom axis -> format)

Identifying the selected country

We need to change the colours of the lines to only show a coloured line for the selected country. For this we need

Is Selected Country

[Country]=[pCountry]

Add this field to the Detail shelf . Then click on the small icon to the left of the Is Selected Country pill, and select the Colour option.

This will mean that both Country and Is Selected Country are on the Colour shelf, and the colour legend will have changed to a combo of both pills

Move the Is Selected Country pill so it is positioned above the Country pill in the marks card section, and this will swap the order to be True | Country instead. Modify all the colours in the legend that start with False to be ‘grey’. Change the pCountry parameter and check the right colour combinations are displayed.

Change the Sort on the Is Selected Country pill so it is sorted by Data source order descending. This will ensure the coloured line is in front of the grey lines.

Adding the circles on the marks

We need a new field that will just identify the Sales for the selected country and selected year.

Sales Selected Year & Country

IF [Is Selected Country] AND [Year Order Date]=[pYear] THEN [Sales] END

Add this to Rows, then make the chart dual axis and synchronise the axis. Change the mark type of the Sales Selected Year & Country marks card to a circle, and adjust the Size to suit.

Finalising the line chart

Add Diff From PY onto the Tooltip shelf of the All marks card.

Create a new field

Month Order Date

DATENAME(‘month’, [Order Date])

and also add this to the Tooltip shelf. Adjust the tooltip to match the required formatting.

Hide the right hand axis (uncheck Show header).

Edit the left hand axis and delete the title, fix the axis from 0 to 50,000 and verify the axis ticks are displaying every 10,000 units.

Hide the 60 nulls indicator (right click -> hide indicator).

Remove the row & column dividers. Hide the Order Date column heading (right click -> hide field labels for columns)

Create the Country name for the heading

On a new sheet

  • Add Country to Rows
  • Add Is Selected Country to Filters and set to True
  • Add Country to Colour and then also add Is Selected Country to colour in the way described above.
  • Add Country to Label
  • Adjust the formatting of the Text so it is much larger font.

Hide the Country column (uncheck show header), and remove all row/column dividers. Ensure the tooltip won’t display.

Putting it all together

I used a horizontal container placed above the core viz. In the horizontal container I added blank objects, a text object, and the Country label sheet. I adjusted the size of the objects to leave space to then float the parameters. The parameters were resized to around 25 pixels so they just displayed the arrow part of the parameter. All this was a little bit of trial and error, and I did find that after publishing to Tableau Public, I had to adjust this section again using web edit.

My published viz is here.

Happy vizzin’!

Donna