How are EPL Football Teams performing this season compared to previous seasons?

Donna ColesLeave a comment

I set this week’s challenge and I tried to deliver something to hopefully suit everyone wherever they are on their Tableau journey. The primary focus for this challenge is on table calculations, but there’s a few other features/functionality included too.

I love football – all the family are involved in it in some way, so thought I’d see what I could get from a different data set this week. The data set contains the results of matches played in the English Premier League for the last five seasons – from the 2020-21 season to the current season 2024-25. Matches up to the end of 2024 only are included.

As I wrote the challenge in such a way that should allow it to be ‘built upon’ from the beginner challenge, I’m going to author this blog in the same way. I’ll describe how to build the beginner challenge first, then will adapt /add on to that solution. Obviously, as in most cases, this is just how I built my solution – there may well be other ways to achieve the same result.

Beginner Challenge

Modelling the data

The data set provided displays 1 row per match with the teams being displayed in the Home and the Away columns respectively, and the FTR (full time result) column indicating if the Home team won (H), the Away team won (A) or if the match was a draw (D).

The first thing we need to do is pivot this data so we have 2 rows per match, with a column displaying the Team and another indicating if the team is the Home or Away team. To do this, in the data source window, select the Home and the Away columns (Ctrl-Click to multi-select – they’ll be highlighted in blue), then from the context menu on either column, select the Pivot option.

This will duplicate the rows, and generate two new fields called Pivot Field Names and Pivot Field Values. Rename the fields by double-clicking into the field heading

  • Pivot Field Names to Home or Away
  • Pivot Field Values to Team

Creating the points calculation

We need to determine how many points each team gained out of the match which is based on whether they won (3pts), lost (0pts) or drew (1pt). Create a calculated field

Pts

IF [Home or Away] = ‘Home’ AND [FTR] = ‘H’ THEN 3
ELSEIF [Home or Away] = ‘Away’ AND [FTR] = ‘A’ THEN 3
ELSEIF [FTR] = ‘D’ THEN 1
ELSE 0
END

Creating the cumulative points line chart

We want to create a line chart that displays the cumulative number of points each team has gained by each week in each season.

Start by adding Wk to Columns and Pts to Rows as these are the core 2 fields we want to plot. But we need to split this by Team and by season, so add Team and Season End Year to the Detail shelf.

This gives us all the data points we need, but at the moment, it’s currently just showing how many points each team gained per week in each season.

To get the cumulative value, we add a running total quick table calculation to the Pts field.

which gives us the display we need

While we can leave the SUM(Pts) field as is in the Rows, I tend to like to ‘bake’ this field into the data set, so I have a dedicated field representing the running total. I can create this field in 2 ways

  1. Create a calculated field called Cumulative Points Per Season which contains the text RUNNING_SUM(SUM([Pts])). Add this field to Rows instead of just Pts.
  2. Hold down Ctrl and then click on the SUM([Pts]) pill in Rows and drag into the left hand data pane and then release the mouse. This will automatically create a new field which you can rename to Cumulative Points Per Season. The field will already contain the text from the calculation used within the quick table calculation, and the field on Rows will automatically be updated.

Filtering the data

Add Team to the Filter shelf and when prompted just select a single entry eg Arsenal.

Show the Filter control. From the context menu, change the control to be a single value (list) display, and then select customise and uncheck the show all value option, so All can’t be selected.

Format the line

To make the line stepped, click the path button and select the step option

To identify the latest season without hardcoding, we can create

Is Latest Season

[Season End Year] = {MAX([Season End Year])}

The {MAX([Season End Year])} is a FIXED LOD (level of detail) calculation, and is a shortened notation for {FIXED: MAX([Season End Year])} which basically returns the latest value of Season End Year across every row in the data set. This calculation returns true if the Season End Year value of the row matches the overall value.

Add this field to the Colour shelf and adjust colours to suit. Also add the same field to the Size shelf

The line for the latest season is being displayed ‘behind’ the other seasons. To fix this, drag the True value in either the colour or size legend to be listed before the False value. Then edit the sizes from the context menu of the size legend, and check the reversed checkbox to make True the thicker line.

If the thick line seems too thick, adjust the mark size range to get it as you’d prefer

Label the line

Add Season End Year to the Label shelf. Align middle right. Allow labels to overlap marks. And match mark colour.

Create the Tooltip

We need an additional calculated field for this, as we want to display the season in the tooltip in the format 2024-2025 and not just the ending year of the season.

Season Start Year

[Season End Year]-1

Add this field to the Tooltip shelf, and then edit the Tooltip to build up the text in the required format. Use the Insert button on the tooltip to add referenced fields.

Final Formatting

To tidy up the display we want to

  • Change the axis titles
    • Right click on each axis and Edit Axis then adjust the title (or remove altogether if you don’t want a title to display)
  • Remove gridlines and axis ruler
    • Right click anywhere on the chart canvas, and select Format. In the left hand pane, select the format lines option and set grid Lines to None and axis rulers to None.
  • Set all text to dark purple
    • Select the Format menu option at the top of the screen and select Workbook. The under the All fonts section, change the colour to that required

  • Update the title to reference the selected team
    • double click in to the title of the sheet and amend accordingly, using the Insert option to add relevant fields.

Intermediate Challenge

For this part of the challenge we’re looking at using a dual axis to display another set of marks – these ones are circular and only up to 1 mark per season should display. As this now takes a bit more thought, and to help verify the calculations required, I’m going to build up the calculations I need in a tabular form first.

Defining the additional mark to plot

On a new sheet add Team, Season End Year and Wk to Rows. Set the latter 2 fields to be discrete (blue) pills. Add Cumulative Points Per Season to Text. Add Team to Filter and select Arsenal.

We need to identify the date of the last match played by each team, so we can use an LOD for this

Latest Date Per Team

{FIXED [Team] : MAX([Date])}

Add this to Rows as a discrete (blue pill) exact date. For Arsenal, the last match was on 27 Dec 2024, whereas for Chelsea it’s 22 Dec 2024.

With this, we can work out what the latest points are for each team in the current season.

Latest Points Per Team

WINDOW_MAX(IF MIN([Date]) = MIN([Latest Date Per Team]) THEN [Cumulative Points Per Season] END)

Breaking this down : the inner part of the statement says “if the date associated to the row matches the latest date, then return the points associated with that row”. Only 1 row in the table of data has a value at this point, all the rest of the rows are ‘nothing’. The WINDOW_MAX statement, then essentially ‘floods’ that value across every row in the data, because the ‘value’ returned by the inner statement is the maximum value (it’s higher than nothing). Add this field into the table.

We’re trying to identify the week in each season where the points are at least the same as the latest points. We’re going to capture the previous week’s points against every row.

Previous Points

LOOKUP([Cumulative Points Per Season],-1)

This is a table calculation that returns the value of the Cumulative Points Per Season from the previous row (-1). If we wanted the next row, the function parameter would be 1. 0 identifies the ‘current row’.

Add this to the table.

We can see the behaviour – The Previous Points associated to 2025 week 18 for Arsenal is 33, which is the value associated to Cumulative Points Per Season for week 17. But we can also see that week 38 from season 2024 is being reported as the previous points for week 1 of season 2025, which is wrong – we don’t want a previous value for this row.

To resolve, edit the table calculation of the Previous Points field and adjust so the calculation for Previous Points is just computing by the Wk field only.

With this we can identify the week in each season we want to ‘match’ against. In the case of the latest season, we just want the last row of data, but for previous seasons, we want to identify the first row where the number of points was at least the same as the latest points; the row where the points in the row are the same or greater than the latest points, and the points in the previous row are less.

Matching Week

//for latest season, just label latest record, otherwise find the week where the team had scored at least the same number of points as the current season so far
IF MIN([Is Latest Season]) AND LAST()=0 THEN TRUE
ELSE
IF NOT(MIN([Is Latest Season])) AND [Cumulative Points Per Season]>= [Latest Points Per Team] AND [Previous Points] < [Latest Points Per Team] THEN TRUE
ELSE FALSE
END
END

Add this to Rows and check the data. In the example below, in Season 2020-21, in week 26, Arsenal had 37 points. The previous week they had 34 points. Arsenal’s latest points are 36, so since 37 >=36 and 34 < 36, then week 26 is the matching week.

Looking at season 2023-24, in both week 15 and 16, Arsenal had 36 points. But only week 15 is highlighted as the match, as in week 14, Arsenal had 33 points so 36 >=36 and 33 <36, but for week 16, as the previous week was also 36, the 2nd half of the logic isn’t true : 36 is not less than 36.

So now we’ve identified the row in each season we want to display on the viz, we need to get the relevant points isolated in their own field too.

Matching Week Points

IF [Matching Week] THEN [Cumulative Points Per Season] END

Add this to the table

We now have data in a field we can plot.

Visualise the additional mark

If you want to retain your ‘Beginner’ solution, then the first step is to duplicate the Beginner worksheet, other wise, just build on what you have.

Add Matching Week Points to Rows to create an additional axis. By default only 1 mark may have displayed. Adjust the table calculation setting of the field, so the Latest Points Per Team calculation is computing by all fields except the Team field.

Change the mark type of the Matching Week Points marks card to Circle and remove Season End Year from the Label shelf (simply drag the pill against the T symbol off off the marks card)

Size the circles

We want the circles to be bigger, but if we adjust the Size, the lines change too, as the Size is being set based on the Is Latest Year pill on both marks cards. To resolve this, create a duplicate instance of Is Latest Year (right click pill and duplicate). This will automatically create Is Latest Season (copy) in the dimensions pane. Drag this onto the size shelf of the Matching Week Points marks card instead to make the sizing independent (you will probably find you need to readjust the table calculation for the Matching Week Points pill to include Is Latest Season (copy)). Then adjust the sizes as required.

Label the circles

Add Latest Points Per Team to the Label shelf of the Matching Week Points marks card. Adjust the table calculation setting, so the Latest Points Per Team calc is computing just by Wk only, so only the latest value displays.

Then format the Label so the text is aligned middle centre, is white & bold and slightly larger font.

Adjust the Tooltip text on the Matching Week Points mark, so it reads the same as on the line mark. You will need to reference the Matching Week Points value instead.

Then make the chart dual axis and synchronise the axis. Remove the Measure Names field that has automatically been added to the All marks card

Remove Label for Latest Season

We don’t want the season label to display for the current year, so create a new field

Label:Season

IF NOT([Is Latest Season]) THEN [Season End Year] END

Replace the Season End Year pill on the Label shelf of the Cumulative Points Per Season marks card, with this one instead.

Final Formatting

To tidy up

  • Remove right hand axis
    • right click on axis and uncheck show header
  • Remove 165 nulls indicator
    • right click on indicator and hide indicator
  • Remove row & column dividers
    • Right click on the canvas and Format. Select Format Borders and set Row and Column Divider values to None

Advanced Challenge

For the final part of the challenge we want to add some additional text to the tooltip and adjust the filter control. As before either duplicate the Intermediate challenge sheet or just build on.

We’ll start with the tooltip text.

Expand the Tooltip Text

For this, we’ll go back to the data table sheet we were working with to validate the calculations required.

We want to calculate the difference in the number of weeks between the latest week of the current season, and the week number of the matching record from previous seasons. So first, we want to identify the latest week of the current season, and ‘flood’ that over every row.

Latest Week Number Per Team

WINDOW_MAX(MAX(IF ([Date]) = ([Latest Date Per Team]) THEN [Wk] END))

This is the same logic we used above when getting the Latest Points Per Team, although as this time the Wk field isn’t already an aggregated field like Cumulative Points Per Season is, we have to wrap the conditional statement with an aggregation (eg MAX) before applying the WINDOW_MAX.

Add this to the table.

And then we need, the week number in each season where the match was found, but this needs to be spread across every row associated to that season.

Matching Week No Per Team and Season

WINDOW_MAX(IF [Matching Week] THEN MIN([Wk]) END)

Add to the table, but adjust the table calculation so the field is computing by all fields except the Season End Year.

Now we have these 2 fields, we can compute the difference

Week Difference

[Latest Week Number Per Team] – [Matching Week No Per Team and Season]

Add to the table. With this we can then define what this means for the text in the tooltip

Less/More

IF [Week Difference]<0 THEN ‘more’ ELSEIF [Week Difference]>0 then ‘less’
ELSE ”
END

and also build the tooltip text completely

Tooltip- other seasons

IF NOT(MIN([Is Latest Season])) THEN
IF [Week Difference] = 0 THEN ‘It took the same amount of weeks to accrue at least the same number of points as the current season’
ELSE ‘It took ‘ + STR(ABS([Week Difference])) + ‘ weeks ‘ + [Less/More] + ‘ to accrue at least the same number of points as the current season’
END
END

Add this to the Tooltip shelf of the Matching Week Points marks card. Adjust the Tooltip to reference the field.

Adjust the Tooltip – other seasons table calculation, so the Latest Points Per Team nested calculation is computing for all fields except Team (this will make the circles reappear)

and also adjust the Latest Week Number Per Team nested calculation to compute by all fields except Team. This should make the text in the tooltip appear.

Filtering by teams in the current season only

We need to get a list of the teams in the current season only, which we can define by

Filter Team

IF {FIXED [Team]: MAX([Season End Year])} = {MAX([Season End Year])} THEN [Team] END

Breaking this down: {FIXED [Team]: MAX([Season End Year])} returns the maximum season for each team, which is compared against the maximum season in the data set. So if there is a match, then name of the team is returned. Listing this out in a table we get Null for all the teams whose latest season didn’t match the overall maximum

We can then build a set off of this field. Right click on the Filter Team field Create > Set and click the Null field and select Exclude

Then add this to the Filter shelf, and the list of teams displayed in the Team filter display will be reduced.

And with that the challenge is completed. My published viz is here.

Happy vizzin’!

Donna

Visualising Ranges

Donna ColesLeave a comment

This week’s challenge was set by Sean and was a recreation of a PowerBI WOW challenge set a few weeks ago. The data set just contains 2 pieces of information – a datetime field and a glucose measurement.

Creating the calculations

On a new sheet, add Device Timestamp to Rows as a discrete (blue) pill at the hour level.

Then create the following fields, which are aggregations of the glucose levels at each hourly time period.

Minimum

MIN([Glucose mg/dL])

5th Percentile

PERCENTILE([Glucose mg/dL],0.05)

25th Percentile

PERCENTILE([Glucose mg/dL],0.25)

Median

PERCENTILE([Glucose mg/dL],0.5)

75th percentile

PERCENTILE([Glucose mg/dL],0.75)

95th percentile

PERCENTILE([Glucose mg/dL],0.95)

Maximum

MAX([Glucose mg/dL])

Add all these fields to the table and then format the Hour field to use the 12hr format by default

Building the Viz

On a new sheet, add Device Timestamp as a continuous (green) pill at the hour level to Columns. Then add Median to Rows. Set the mark type to line and set the path to be a dashed line and colour  black. Format the Device Timestamp pill so the default format on the pane is 12hr format (axis should remain as numeric).

Add Minimum to Rows to create a second marks card. Change the mark type to Area and set the colour to be 100% opacity.

Then drag 5th Percentile to the Minimum axis and drop when the ‘2 green column’ symbol appears.

This will automatically add Measure Values to Rows instead and Measure Names to Colour. Add 25th Percentile, 75th Percentile, 95th Percentile and Maximum to the axis too. Reorder the fields in the Measure Values section so the fields are listed in the appropriate order from Min to Max. Adjust the Colours accordingly. Unstack the marks (Analysis -> Stack Marks -> Off).

Add Measure Names to Label and align middle right. Allow labels to overlap marks.

Add Measure Names to the Label on the Median marks card. Also align middle right and format to be underlined.

Make the chart dual axis, synchronise the axis, and then right click on the right hand axis and move marks to back.

Hide the right hand axis (uncheck show header). Edit the Device Timestamp axis to fix from 0 to 26, set the axis to display a value every 3 hours and remove the title.

Add constant Reference Lines to the left hand axis for the values 80 and 180

Edit the left hand axis title. Remove row and column dividers. Add axis rulers. Add a title.

Add to a dashboard and then publish. My published version is here.

Note – the vertical grid lines that appear on the Tableau Public version are a nuance of Tableau Public. I originally recreated on Desktop thinking it was a requirement of the challenge, by adding multiple constant reference lines for each hour. But when I checked Sean’s solution after publishing, I found he didn’t have any and the intention was not to have any.

Happy vizzin’!

Donna

Can you replicate quick table calcs?

Donna ColesLeave a comment

Erica kicked off the 1st year of #WOW2025 with a table calculation based challenge asking us not to just use quick tableau calculations, to avoid the use of the RUNNING_SUM table calculation and to just create 2 calculated fields.

Defining the calculations

On a new sheet, add Order Date to Rows as a discrete (blue) pill at the Month-Year format and add Sales to Text.

For the running total, we want

Running Total

SUM([Sales]) + PREVIOUS_VALUE(0)

which takes the Sales from the current row, and adds it to the value of the cumulative Sales (ie this calculation) in the previous row

For the moving average, we want

3 Month Moving Average

WINDOW_AVG(SUM([Sales]), -2, 0)

which averages the Sales for the current row plus the previous 2 rows (ie 3 rows in total)

Building the Viz

ON a new sheet add Order Date as a continuous (green) pill at the Month-Year format to Columns and Running Total to Rows.

Change the mark type to Area, and set the Colour to #a16eaf with a 25% Opacity.

Add Order Date to Tooltip, and set to the MIN aggregation. Format the pill on the Tooltip shelf to have the <month year> format when displayed on the pane.

Add another instance of Running Total to Rows. Set the Mark type to be Line and reset the opacity on the colour shelf to be 100%. Make the chart dual axis and synchronise the axis.

Add 3 Month Moving Average to Rows. Set the Colour to be #67c79c at 25% opacity.

The add another instance of 3 Month Moving Average to Rows. Set the colour to #67c79c and the mark type to line and increase opacity to 100%. Make dual axis and synchronise the axis.

Update the Tooltip on the All Marks card, referencing the MIN(Order Date) pill and adding $ before the values

Edit the Order Date axis, to start from 01 Jan 2021 and end on 31 Dec 2024. Remove the axis title.

Format the Order Date axis, and set the Dates on the scale to be formatted with the MMMMM notation to just show the first letter of the month

Add a reference line to the Order Date axis, set to be a constant value of 01 Jan 2021, with a custom label of 2021 and the line is formatted to be a white dashed line of 100%

Then format the reference line so the label is aligned top right

Repeat this step 3 more times, adding Reference Lines for 01 Jan 2022 (labelled 2022), 01 Jan 2023 (labelled 2023) and 01 Jan 2024 (labelled 2024).

Finally, hide the right hand axis (uncheck show header), remove column dividers, but show the axis rules on the rows.

And that should complete the challenge – just pop the viz onto a dashboard and publish. My published version 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

Can you build a Christmas Tree?

Donna ColesLeave a comment

A fun challenge this week from Kyle to test our use of containers on dashboards.

To build anything Tableau, you need to connect to some data. I used Superstore as suggested by Kyle, and within my solution did actually utilise one of the data fields to help build each row.

The challenge relies on the use of some custom shapes that Kyle provided. Refer to this KB to understand how to store the shapes for use in Tableau Desktop.

Building the Star

Create calculated field

Dummy

‘dummy’

Add to the Detail shelf and change the mark type to shape. Select the star shape you have saved. Set the view to Entire View and the format the sheet and set the worksheet background colour to green. Remove the Tooltip.

Name the sheet Star or similar.

Building the Bauble sheets

Duplicate the star sheet. Change the shape to the bauble image. Add Customer ID to the Filter shelf and select 2 records only (doesn’t matter what IDs are selected). Then add Customer ID to Columns.

Uncheck Show Header from the Customer ID pill to hide the column header, then remove all row and column dividers. Name the sheet Bauble 2 or similar.

Then to create the other Bauble sheets, just duplicate the sheet again, and select another entry in the Customer ID filter, and rename the sheet.

Repeat this 6 more times, so you end up with 8 bauble sheets and 1 star sheet.

Building the Christmas Tree

On a dashboard sized to 1000 x 1200, add a vertical layout container, and add all the sheets in the correct order

Remove the title from each sheet and set the outer padding of each object to 0, so everything ‘butts up’ against each other

Add a blank object to the bottom (within the same layout container). Set the background colour to brown and remove all padding.

Select the vertical layout container, and then select the option to Distribute Contents Evenly which will adjust every row within the container to be the same height.

To make the sheet on each row narrower, we use padding for the left and right, with different values on each row. I did some calculations based on how wide the dashboard was (1000) and how wide each ‘image section’ should be – 9 baubles at the widest point, so each section was 1000/9 = 111 px wide.

So for the top row, the padding on each side I calculated to be (1000-111)/2 = 444px each. For the second row, the padding on each side I calculated to be (1000 – (2×111))/2 = 389px and so on.

I then added a title and footer, and my Christmas Tree was complete. My published viz is here.

Happy vizzin’!

Donna

Can you create a scatter plot investigation?

Donna ColesLeave a comment

This week’s #WOW2024 challenge was set by a guest poster, Robbin Vernooij, who wanted us to build a scatterplot with additional features to aid analysis. The main focus was on using Set Actions, so that’s what I used throughout the challenge, although parameters could be also be used.

Modelling the data

I took the simpler route when combining the data sources. After connecting to the Life Expectancy (lex.csv) data source, I deleted all the columns relating to the years except 2022 (Ctl Click to multi select the columns, and then right click and ‘hide’) . I then renamed the column from 2022 to Life Expectancy. The data source just contained 2 fields Country and Life Expectancy.

I then added the Co2 Pcap Cons.csv data source and related it via the Country field. Again I removed all the unnecessary year fields except the 2022 column, and renamed this to  Co2 Pcap Cons.

Building the Scatter Plot

On a new sheet, add Co2 Pcap Cons to  Columns and Life Expectancy to Rows. Add Country to Detail.

Hide the null indicator.

We need to identify a ‘selected’ country. We could use a parameter for this, but as mentioned above, I’ll use a set.

Selected Country Set

Right click on Country > Create  > Set.  Select a single country from the list (I chose Russia).

From this we need to determine the Life Expectancy and Co2 Pcap Con values for the selected country, but this value needs to be associated to every Country in the data set (ie every row of data), so we can use a FIXED LoD.

Selected Country Co2

{FIXED:SUM(IF [Selected Country Set] THEN [Co2 Pcap Cons] END)}

Selected Country Life Expectancy

{FIXED:SUM(IF [Selected Country Set] THEN [Life Expectancy] END)}

With these, we then want to define a min and max range for each measure so we can build the reference bands. The tolerance for this range wasn’t mentioned in the requirements, so I checked the solution to ensure I could validate other calculations later on.

Min Co2

[Selected Country Co2] – 1

Max Co2

[Selected Country Co2] + 1

Min Life Expectancy

[Selected Country Life Expectancy] – 4

Max Life Expectancy

[Selected Country Life Expectancy] + 4

Add all four fields to the Detail shelf.

Add a reference band to the Co2 Pcap Cons axis (right click axis > add reference line). Select band and set it to be from the Min Co2 field to the Mx Co2 field.

Repeat the steps for the Life Expectancy axis, to run from the Min Life Expectancy field to the Max Life Expectancy field.

In the example above, I have Russia as the selected country. We now want to identify all the countries that are falling within the bands.

Within Co2 Band

[Co2 Pcap Cons] <= [Max Co2] AND [Co2 Pcap Cons]>=[Min Co2]

Within Life Expectancy Band

[Life Expectancy]<= [Max Life Expectancy] AND [Life Expectancy] >= [Min Life Expectancy]

And with this, we create another set

Within Band Set

Select the Condition tab, and enter the formula

MIN([Within Co2 Band]) OR MIN([Within Life Expectancy Band])

Add Selected Country Set to Colour, to Size and to Shape. Adjust shape and size to suit. Then add Within Band Set to Detail and then adjust the icon to the left of the pill to the Colour icon, so 2 pills are now on the Colour shelf. Adjust the colours to suit.

Then create

Label – Country

IF [Selected Country Set] THEN [Country] END

And add to the Label shelf. Align bottom centre, and allow labels to overlap other marks.

Hide the Tooltip, Hide all the gridlines and row/column dividers. Format Co2 Pcap Cons and Life Expectancy to 1 dp. Name sheet Scatter or similar.

Building the Average Bar

On a new sheet, add Selected Country Set to Rows. Add Co2 Pcap Cons  and Life Expectancy to Columns and change the aggregation of both from SUM to AVG. Manually reorder the In/Out header so Out is listed first. Show the labels. Add Selected Country Set to Colour on the All marks card and adjust accordingly.

Double click into Columns and type MIN(0.0), then move the pill so it’s the first one listed. Change the mark type of the MIN(0.0) marks card to shape. Add Selected Country Set to shape and adjust.

Create a new field

Header Label

IF [Selected Country Set] THEN [Country] ELSE ‘All others’ END

Add this to the Label shelf of the MIN(0.0) marks card. Align the label middle left.

Edit the MIN(0.0) axis to be fixed from -5 to 1 to shift the display to the right

Then remove the axis title, and set the tick marks to None so the axis for this section is hidden

Add Header Label to the Tooltip on the All marks card, and update the tooltip. Remove all gridlines, row & column dividers and hide the Selected Country Set pill on Rows (uncheck show header). Name the sheet Avg Bar or similar.

Building the Count Bar

On a new sheet, add Within Band Set to Columns and lex.csv(Count) to Rows. Add Within Band Set to Colour and Country to Detail. Adjust Colour and tooltip. Name the sheet Count Bar or similar.

Adding the interactivity

Add the sheets to a dashboard and arrange accordingly, Add a dashboard set action

Select Country

On hover of the Scatter chart, target the Selected Country Set. Only allow single selection. Assign values to the set on hover, and retain the values in the set when the selection is cleared.

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

Happy vizzin’!

Donna

Can you make a candle line chart?

Donna ColesLeave a comment

Sean provided this week’s #WOW2024 challenge, to produce a dual axis chart which he labelled a ‘candle line’ chart. The line displays the 2024 sales per month, while the candles represent the absolute difference from the previous year.

Setting up the calculations

Rather than ‘hardcode’ to 2024, I decided to use a calculation to get the latest year in the data set

Latest Year

{MAX(YEAR([Order Date]))}

which I formatted to a number with 0dp which did not use thousand separators

and then created

Previous Year

[Latest Year] – 1

I moved both of these up into the ‘dimensions’ section of the data pane (above the line).

To get the latest year sales I created

Latest Year Sales

IF YEAR([Order Date]) = [Latest Year] THEN [Sales] END

and then created

Prev Year Sales

IF YEAR([Order Date]) = [Previous Year] THEN [Sales] END

Both these fields I formatted to $ with 0 dp

We need to know the difference between these values, so I created

Sales Diff

SUM([Latest Year Sales]) – SUM([Prev Year Sales])

which I formatted to $ with 0 dp.

and we also need the percentage difference

% Diff

[Sales Diff] / SUM([Prev Year Sales])

which was formatted to a % with 0 dp.

We need to know if the Sales Diff is positive or negative, so create

Diff is +ve

[Sales Diff]>=0

Finally, when we hover on the tooltip we can see the values are coloured based on whether the difference is +ve or not, so we need some additional fields for the tooltip

Tooltip Sales Diff +ve

IF [Diff is +ve] THEN [Sales Diff] END

and

Tooltip Sales Diff -ve

IF NOT([Diff is +ve]) THEN [Sales Diff] END

both these fields I custom formatted to +”$”#,##0;-“$”#,##0 (essentially this is $ with 0 dp, but positive values are prefixed with an explicit + sign).

And then we also need

Tooltip Sales Diff % +ve

IF [Diff is +ve] THEN ‘(‘ + STR(ROUND([% Diff]* 100,0)) + ‘%)’ END

and

Tooltip Sales Diff % -ve

IF NOT(Diff is +ve]) THEN ‘(‘ + STR(ROUND([% Diff]* 100,0)) + ‘%)’ END

Now we have all the fields, we can build the viz.

Building the viz

On a new sheet, add Order Date at the discrete month level (blue pill) to Columns and add Latest Year Sales to Rows.

Then add another instance of Latest Year Sales to Rows. On the second marks cards, change the mark type to Gantt Bar and add Sales Diff to Size. Reduce the Size to make the bars that display narrower.

Add Diff is +ve to the Colour shelf, and adjust accordingly. Change the colour of the line chart on the other marks card. Make the chart dual axis and synchronise the axis.

On the All marks card, add Latest Year and the four Tooltip xxx fields we created to the tooltip shelf, then update the tooltip to reference all the relevant fields, and colour them accordingly

Add Region, Category and Segment to the Filter shelf, selecting all values.

Then finally, tidy up the sheet by removing all row/column dividers, the right hand axis (uncheck show header), and the Order Date label (right click and hide field labels for columns). Rename the left hand axis Sales.

Add to a dashboard and position as appropriate adding a title and updating the filters to be single value drop downs.

That ultimately is the core of the challenge, but Sean did suggest to use the new Google font Poppins. I’m on Windows and that font isn’t visible by default/installed, so after publishing, I then edited on Tableau Public and changed the fonts throughout via the Format > Workbook menu option and setting All fonts to Poppins.

My published viz is here.

Happy vizzin’!

Donna

Top 5 Product Sales vs Other Products

Donna Coles1 Comment

Erica set this week’s challenge and provided multiple levels specifically aimed a newer users of Tableau. My solution is for Level 3.

Setting up the calculations

First, create a parameter to capture the Sub-Category we care about

pSelectedSubCat

string parameter defaulted to Tables

Create a new field

Is Selected SubCat

[pSelectedSubCat]= [Sub-Category]

then create another field

Product to Display – Step 1

IIF([Is Selected SubCat], [Product Name], ”)

On a new sheet add Sub-Category and Product to Display – Step 1 to Rows. Show the pSelectedSubCat parameter. You will see that the Product rows only show for the Sub Category entered in the pSelectedSubCat parameter

We want to show the average of the product sales for each Sub-Category, so we can create

Sales by Sub Cat & Product

{FIXED [Sub-Category], [Product Name]: SUM([Sales])}

Add this to Text. By default it will aggregate this value to SUM, change it to AVG. For the rows associated to the selected Sub-Category the value of this field is the same whether its SUM or AVG, as it has been calculated at the level of detail being displayed on the row (Sub-Category and Product Name). For the other rows, by changing the aggregation to AVG we are getting the required value, which is essentially the sum of all sales associated to the Sub-Category divided by the number of distinct products. Sort the Sub-Category pill by the Average of the Sales by Sub Cat & Product field descending

Additionally, sort the Product to Display – Step 1 field the same way

We need to identify the top 5 records for the products associated to the selected Sub-Category. We will use a set for this. Right click on Product to Display – Step 1 > Create > Set

Product to Display Set

Select the Top tab and select the top 5 by formula

AVG(IF [Is Selected SubCat] THEN [Sales by Sub Cat & Product] END)

Add this to Rows and you should get In displayed against the product rows with the highest values

With this we can start to think about the ‘other’ text we need to display, but for this we need a handle on the number of products in each Sub-Category. Create

Count Products Per Sub-Category

{FIXED [Sub-Category]: COUNTD([Product Name])}

Add to Text so you can see the value, and then subsequently we can create

Product to Display – Step 2

IF NOT([Is Selected SubCat]) THEN ”
ELSEIF [Product to Display Set] THEN [Product Name]
ELSE ‘Other: ‘ + STR([Count Products Per Sub-Category] – 5) + ‘ Products’
END

Add to Rows to see the behaviour

The viz also needs to show an index value against the top 5 rows, so create

Index to Display

IF MIN([Is Selected SubCat]) AND MIN([Product to Display Set]) THEN STR(INDEX())
ELSE ” END

Add this to Rows as a blue discrete pill in front of the Product to Display – Step 2 field. Adjust the table calculation setting so the Sub-Category field is unchecked.

Next, we’re going to need to display a reference line that is the overall average product sales for the Sub-Category. This may sound like it’s what we already have, but that field is at the Sub-Category and Product Name level of detail, and we need to aggregate this back up to be at the Sub-Category level, so we create

Avg Sales by Sub Cat and Product

{FIXED [Sub-Category]: AVG([Sales by Sub Cat & Product])}

which is the average of the field we previous created but per Sub-Category. Pop this into the table to see what is happening. For the rows where the Products aren’t showing, the values match, but for the rows where the Products are displayed, you get the overall average, which is the same for all the rows.

If we now remove Product to Display – Step 1 from Rows (and amend the Index to Display table calc so it is not longer computing by this field too), we should have the data we expect. Format the Sales by Sub Cat & Product field to be $ with 0 dp.

Building the Viz

On a new sheet add Sub-Category, Product to Display Set, Index to Display and Product to Display – Step 2 to Rows and Sales by Sub Cat & Product to Columns and aggregate to AVG.

Sort the Sub-Category pill by the Average of the Sales by Sub Cat & Product field descending and apply the same sort to the Product to Display – Step 2 field. Edit the table calculation of the Index to Display field so it is not computing by Sub-Category.

Create a new field

Colour

IF [Is Selected SubCat] AND [Product to Display Set] THEN ‘Dark’
ELSEIF [Is Selected SubCat] THEN ‘Light’
ELSE ‘Grey’
END

Add this to Colour shelf and adjust accordingly.

Add Avg Sales by Sub Cat & Product to the Detail shelf, then add a reference line based on the Average of this field

Widen each row and from the Label shelf check Show mark labels. From the Tooltip shelf uncheck Show tooltips.

Hide the In/Out Product to Display Set field in Rows (uncheck show header). Format the font and style of the header columns, then hide the header field labels and hide the axis. Adjust the row banding and set all gridlines, zero lines, axis rulers and column dividers to none. Change the title of the sheet.

Test the behaviour by manually changing the value of the parameter.

Adding the interactivity

Add the sheet to a dashboard, then create a parameter dashboard action

Set SubCat

On select of the viz, update the pSelectedSubCat parameter passing in the value from the Sub-Category field.

My published viz is here

Happy vizzin’!

Donna

Datafam Europe Live: London’s Top Attractions

Donna Coles1 Comment

This week’s #WOW2024 challenge was run live at the #Datafam Europe event in London and was a combo with the #PreppinData crew. If you want to have a go at shaping the data required for this challenge yourself, then check out the PreppinData challenge here. Otherwise, you can use the data provided in the excel workbook from the link in the #WOW2024 challenge (I’m building based on this).

Modelling the data

There are 3 data sources for this challenge which we need to relate together. We have

  • Attraction Locations – a list of attractions in London with their lat and long coordinates
  • Tube Locations – a list of tube stations in London with their lat & long coordinates
  • Attraction Footfall – a list of attractions with their annual footfall

Connect to the Excel file and add Attraction Locations to the canvas. Then add Tube Locations and then create a relationship calculation of 1=1 to essentially map every attraction to every tube station.

Then add Attraction Footfall to the canvas and relate it to Attraction Locations by setting Attraction Name = Attraction

Finally, in the viz we have to understand the distance between a selected attraction (the start point) and other attractions (the end point), so we need to have an additional instance of Attraction Locations to be able to generate the information we will need between the start and end. So add another instance of Attraction Locations and set the relationship as Attraction Name <> Attraction Name

To make things a bit easier for reference purposes, rename Attraction Locations to Selected Attraction and Attraction Locations1 to Other Attractions (just right click on the data connection in the canvas to do this).

Building the Footfall Bar Chart

On a new sheet add Attraction Name (from Selected Attraction) to Rows and add 5 Year Avg Footfall to Columns. Change this from SUM to AVG (as the data consists of multiple rows per year and this value is the same for each row associated to an attraction). Sort the chart descending.

Click on the 2 nulls indicator and select to filter the data which will remove the bottom two rows and automatically add 5 Year Avg Footfall to the Filter shelf.

Manually increase the width of each row. Set the format of the 5 Year Avg Footfall to be in millions (M) to 2dp, and then show mark labels and align middle left.

Create a parameter to capture the selected attraction

pSelectedAttraction

string parameter defaulted to St Paul’s Cathedral

show the parameter on the screen.

We need to identify which attraction has been selected, so create

Is Selected Attraction

[Attraction Name]=[pSelectedAttraction]

and then add this to the Colour shelf. Adjust the colours accordingly and set an orange border. Then add Attraction Rank to Rows. Set it to be a discrete dimension (blue pill) and move it to be in front of Attraction Name.

Set the font of the row labels to be navy, hide the row label names (hide field labels for rows), hide the axis (uncheck show header), don’t show tooltips, and remove all row/column dividers, gridlines and zero/axis lines. Set the background of the worksheet to be None (ie transparent). Update the title of the sheet and then name the sheet Footfall or similar.

Building the map

We’re going to use map layers for this, and will build 4 layers

  • the selected attraction
  • the other attractions
  • the tube stations
  • the buffer circle

When using map layers we want to work with spatial data, so we’ll start by creating a point for the selected attraction

Selected Attraction Point

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

Double click on this and it will automatically generate a map. Add Is Selected Attraction to the Filter shelf and set to True so only 1 mark should display, Add Attraction Name to Detail. Show the pSelectedAttraction parameter. Change the mark type to shape and select a filled star. Set the Colour of the shape to navy and add an orange halo. Update the Tooltip.

For the buffer, we need another parameter

pDistance(miles)

float parameter defaulted to 1 that ranges from 0.5 to 2 with a step size of 0.5

Then create

Buffer Attraction (x miles)

BUFFER([Selected Attraction Point], [pDistance(miles)],’mi’)

And drag this onto the canvas and drop when the Add Marks Layer option appears

This will create a new marks layer, which we can rename to Buffer. Reduce the opacity of the colour to 0%. Move the marks layer so it is at the bottom (below the other marks card) , and set the disable selection option so when you move the cursor over the map the buffer circle does not highlight.

Adjust the background layers of the map so only the Postcode Boundaries are visible.

To add the tube stations, we first need to create

Tube Station Point

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

Then drag this onto the canvas to create a new marks layer. Add Station to the Detail shelf of this new marks card, and move the marks card so it is below the Selected Attraction marks card.

We don’t want all the stations to display. We just need to show those up to 1.5x the buffer distance, so we need

Distance to Tube Station

DISTANCE([Selected Attraction Point], [Tube Station Point], ‘mi’)

format to a number with 2 dp and then create

Tube Station Within Range

[Distance to Tube Station]<= 1.5 * [pDistance(miles)]

Add this to the Filter shelf and set to True.

We want the size of the displayed stations to differ depending on whether they’re inside the buffer or not, so create

Tube Station Within Buffer

[Distance to Tube Station] <= [pDistance(miles)]

and add this to Size. Change the mark type to circle, then adjust the size as required. Change the colour to orange and add a white border. Add Distance to Tube Station to Tooltip and update. You may want to adjust the size of the shape on the Selected Attraction marks card too, so it’s bigger than the tube stations.

The stations need to be labelled based on the closest x number of stations that are within the buffer. For this we need a parameter

pTop

integer parameter defaulted to 5 that ranges from 5 to 20 with a step size of 1.

We need to rank the stations based on the distance, so create

Station Rank

RANK(SUM([Distance to Tube Station]), ‘asc’)

We’re also going to label the stations with a letter based on their rank

Rank Stations as Letters

CHAR([Station Rank] + 64)

but we only want to show labels for the ‘top’ ranked stations, so create

Label Stations

IF MIN([Tube Station Within Buffer]) AND [Station Rank]<=[pTop] THEN [Rank Stations as Letters] END

and add this to the Label shelf. Adjust the table calculation settings, so the calculation is computing by both Station and Tube Station Within Buffer.

Set the labels to be aligned middle centre, and allow labels to overlap other marks. If things are working as expected, then if you increase the buffer distance to 1.5 miles and the pTop parameter to 20, you should see that not all stations within the buffer circle are labelled

To add the other attractions, we need to create

Other Attraction Point

MAKEPOINT([Attraction Latitude (Attraction Locations1)],[Attraction Longitude (Attraction Locations1)])

and drag this onto the canvas to Add a marks layer. Move this layer so it is beneath the Selected Attraction marks card, and add Attraction Name (from the Other Attractions) section to Detail

Once again, we want to limit what attractions display, so need

Distance to Other Attraction

DISTANCE([Selected Attraction Point], [Other Attraction Point], ‘mi’)

and then

Other Attraction Within Range

[Distance to Other Attraction]<= 1.5 * [pDistance(miles)]

and add this to the Filter shelf and set to True.

Add Distance to Other Attraction to the Tooltip shelf and update. Change the mark type to shape. The shape needs to differ whether it’s within the top x closest attractions that’s inside the buffer or not. So we need

Rank Other Attractions

RANK(SUM([Distance to Other Attraction]), ‘asc’)

and then

Top X Attraction in Buffer

IF [Rank Other Attractions] <= [pTop] AND MIN([Other Attraction within Buffer]) THEN MIN([Attraction Name (Attraction Locations1)])
ELSE ‘Not Top X’
END

Add this to the Shape shelf. Set the table calculation so it is computing explicitly by both Attraction Name and Other Attraction Within Buffer. Setting the specific shape for each of the named attractions that could show is fiddly, so I just chose to leave as per the default values listed. The only shape I explicitly set was the Not Top X which I set to a filled circle. I set the colour of the shapes to dark grey and added a halo of the same colour to make the shape more prominent. The shapes also need to differ in size based on whether they are in the buffer or not, so need

Other Attraction Within Buffer

[Distance to Other Attraction] <= [pDistance(miles)]

Add to the Size shelf and then adjust sizes to suit.

Set the background of the worksheet to None, remove all row/column dividers and name the sheet Map or similar. Finally remove all the Map Options (Map > Map Options > uncheck all selections) to prevent to toolbar from displaying on hover. Test the map functionality by changing the various parameters and entering a new starting location.

Note – in subsequent testing I found that for some attractions where there were either no tube stations or other attractions within the range, the map would disappear. If I get time I’m going to try to work on a solution for this, but I’ll leave as is for now (Lorna’s published solution has the same issue).

Building the Tube Station Rank Bar

On a new sheet add Station to Rows and Distance to Tube Station to Columns. Add Is Selected Attraction to Filter and set to True. Sort the chart ascending, so closet is listed first.

We only want to display the stations that are within the buffer, so add Tube Station Within Buffer to Filter and set to True.

We also want to restrict this list to just those that are the closest ‘x’ to the attraction based on the pTop parameter. Add Station to the Filter shelf and on the General tab, select Use all and then select the Top tab and add the condition to display the bottom pTop by Distance to Tube Station.

However, this doesn’t quite show the correct results, as the Top n filtering has been applied BEFORE the other filters on the shelf. To resolve this we need to add Is Selected Attraction and Tube Station Within Buffer to context (right click each pill on the filter shelf).

Add Station and Distance to Tube Station to the Label shelf, and adjust the label to display the text as required and align middle left. Change the mark type to bar and manually widen the width of each row so the labels are readable. Adjust the colour of the bars.

For the circle labels, we need a ‘fake’ axis – double click into Columns and manually type MIN(-0.05). Move the pill that is created to be in front of the Distance to Tube Station pill.

Change the mark type of the MIN(-0.05) pill to circle and remove the fields from the Label shelf. Add Rank Stations as Letters to the Label shelf instead and adjust the table calculation so it is explicitly computing by Station. Format the label and align middle centre.

Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card.

Don’t show the Tooltip, remove all row/column dividers, hide the axis and the Station column. Hide all gridlines, axis lines, zero lines. Format the background of the workbook to be None (ie transparent).

Update the title of the sheet referencing the parameters as required, and name the sheet Tube Station Rank Bar or similar.

Building the Tube Station Rank Bar

On a new sheet add Attraction Name (from the Other Attractions data set) to Rows and Distance to Other Attraction to Columns. Add Is Selected Attraction to Filter and set to True. Sort the chart ascending, so closet is listed first.

We only want to display the other attractions that are within the buffer, so add Other Attraction Within Buffer to Filter and set to True.

We also want to restrict this list to just those that are the closest ‘x’ to the attraction based on the pTop parameter. Add Attraction Name to the Filter shelf, on the General tab, select Use all and then select the Top tab and add the condition to display the bottom pTop by Distance to Other Attraction.

Add Is Selected Attraction and Other Attraction Within Range to context.

Add Attraction Name (from the Other Attractions data set) and Distance to Other Attraction to the Label shelf, and adjust the label to display the text as required and align middle left. Change the mark type to bar and manually widen the width of each row so the labels are readable. Adjust the colour of the bars.

Double click into Columns and manually type MIN(-0.1). Move the pill that is created to be in front of the Distance to Other Attraction pill.

Change the mark type of the MIN(-0.1) pill to shape and remove the fields from the Label shelf. Add Attraction Name to the Shape shelf. Set the colour of the shape. Edit the shape for each Attraction so it matches the shapes assigned to the attractions on the Map sheet. Unfortunately, this is a bit fiddly and just a case of trial and error which involves changing the parameters to try to ensure all the options are presented at least once of each of the charts. There is probably a better way, but I’d have to rebuild something so sorry!

Make the chart dual axis and synchronise the axis. Remove Measure Names from the All marks card.

Don’t show the Tooltip, remove all row/column dividers, hide the axis and the Attraction Name column. Hide all gridlines, axis lines, zero lines. Format the background of the workbook to be None (ie transparent).

Update the title of the sheet referencing the parameters as required, and name the sheet Tube Attraction Rank Bar or similar.

Adding the interactivity

Add the sheets onto the dashboard making use of layout containers to get the objects positioned where required. Format the dashboard to set the background to the light peach colour. How I’ve organised the content is show by the item hierarchy below

Create a parameter dashboard action

Select attraction

On select of the footfall bar chart, set the pSelectedAttraction parameter with the value from the Attraction Name field. Keep the value when the mark is deselected.

And at this point, you should hopefully now have a functioning dashboard. My published version is here.

Happy vizzin!

Donna

Can you switch a dashboard theme?

Donna ColesLeave a comment

This week’s #WOW2024 challenge was a guest post by Tomoki Goda. The main focus of the challenge was to be able to switch between light and dark mode, but there’s so much more going on, this blog could take a while!

I also have to admit, I didn’t manage to complete this without help and also looking at the solution workbook. It may be if I’d left it and come back to it another time I’d have figured it out, but time is so precious at the moment, it was more likely if I’d left it, I would have struggled to return to it, and then this blog wouldn’t have got written either. But I’ve learned something, so that’s the win in my book 🙂

Setting up the parameters

There are 3 parameters required for this challenge.

pTheme

This parameter will control the mode to display and I set it as a boolean parameter defaulted to true and aliased as True = Light Theme and False = Dark Theme

pRegion

This parameter will capture the Region associated to the KPI the user has interacted with on the dashboard. This is a string parameter defaulted to <empty string>

pCategory

This parameter will capture the Category associated to the Category Sales bar chart that the user has interacted with on the dashboard. This is a string parameter defaulted to <empty string>.

Building the Region KPI chart

ON a new sheet ad Region to Rows and then double click into the Rows shelf manually type MIN(1.0) to create a fake axis. Increase the Size to the largest possible and set the view to Entire View.

Change the Mark Type to Bar. Add Region to the Label shelf. Format the Sales field to be $K to 1 dp and also add to the Label shelf. Adjust the font size and align middle centre. Edit the MIN(1.0) axis to be fixed from -0.2 to 1.2 to allow some spacing between the colour blocks.

Show the pRegion and pTheme parameters. Add Order Date to the Filter shelf and choose Years , then select all years, Show the Year filter and display as a single value list.

Create a new field

Is Selected Region

[Region] = [pRegion]

Add this to the Colour shelf.

Also create a new field

Show Light Theme

[pTheme]

Add this to the Detail shelf initially, then select the ‘hierarchy’ symbol to the left of the pill and change the symbol to the Colour one – this will add two pills to the colour shelf

Drag the Show Light Theme pill so it is listed above the Is Selected Region pill. Enter the name of a Region into the pRegion parameter (eg East), and then adjust the colours for when pTheme=Light Theme is selected

Now change the pTheme parameter so Dark Theme is selected and adjust the colours again

Hide the Region field and the axis (uncheck show header). Don’t show the Tooltip. Hide all row/column dividers, gridlines, zero lines, axis rulers and axis ticks. Set the border on the colour shelf to None, and most importantly, set the worksheet background colour to None (ie it’s transparent). This will become noticeable later when we add the content to the dashboard.

Finally we will need some additional fields which will help with the interactivity on the dashboard later, when we don’t want the marks that have not been selected to ‘fade’.

Region for Param

IIF( [pRegion]=[Region],””, [Region])

True

TRUE

False

FALSE

Add all 3 fields to the Detail shelf. Name the sheet Region Sales KPI.

Building the Category by Sales bar chart

On a new sheet add Category to Rows. Then go back to the Region Sales KPI sheet and set the Year(Order Date) filter to apply to worksheet > selected worksheets > and select the relevant sheet.

Now, I had a couple of attempts at building this. From what I could tell, the Category label wasn’t a usual ‘row heading’, as we needed to give it a specific coloured background on selection. It also had to be built within the same sheet, as the same technique was applied to the Sales by Sub-Category bar chart which was a scrollable section. I tried using a dual axis of Sales and Regional Sales in conjunction with a ‘fake’ axis for the header, but found the width of the fake axis had to match the width of the dual axis, so my header section was too wide. After a lot of trial and error, I ultimately had to ask my colleague, Sam Parsons, if he could figure it out, which he did in 5 minutes using dual axis and Measure Names.

Add Sales to Columns and sort descending. Show the pRegion, pCategory and pTheme parameters and ensure pRegion has a value (eg East).

Create a new field

Selected Region Sales

IF [Region] = [pRegion] THEN [Sales] END

format this to $k to 1dp, and then drag onto the canvas and drop on the Sales axis when the two ‘green column’ icon appears.

This will automatically add Measure Names and Measure Values into the view. Move Measure Names from Rows to the Colour shelf, and also add another instance of Measure Names to the Size shelf. Add Show Light Theme to the Detail shelf, and then set to be an additional field on the Colour shelf. Move it so it is listed above the Measure Names colour pill. Adjust colours of the bars for the light and dark them modes as before.

Reorder the measures in the Size legend box so Selected Region Sales is listed first and so is smaller. Manually increase the width of each row, and then adjust the sizes from the size legend so the difference between the bar widths is not so great.

Create a new field

Label Splitter

IF [pRegion] <> ” THEN ‘ / ‘ END

and add this, Sales and Selected Region Sales to the Label shelf. Arrange the pills as required, align middle left and ensure Allow labels to overlap other marks is selected

Set the pRegion parameter to <empty string> and verify the label displays as expected, Hide the Null indicator. Update the Tooltip as required.

For the header, we need another measure we can use which is on the same axis as the Measure Values, but is negative, so it sits to the left of the bars we already have.

Header Plot

Window_MAX(SUM([Sales])) * -0.25

This takes the maximum value of the Sales bar that is displayed in the chart and applies a proportion, so we don’t need to attempt to ‘fix’ the axis in anyway. Add this to Columns, set to dual axis and then synchronise axis. Set the mark type on the All marks card to bar. You’ll probably have something like…

On the Header Plot marks card, remove the three fields on the Label self, and add Category to the Label shelf instead.

Enter the name of a Category into the pCategory parameter (eg Technology). Create a new field

Is Selected Cat

[Category] = [pCategory]

and drag this and drop it directly onto the Measure Names pill that is on the Colour shelf of the Header Plot marks card. Adjust the colours as required, changing the pTheme parameter to dark mode too.

Delete the text from the Tooltip of the Header Plot marks card.

Hide the Category field and the axis (uncheck show header). Don’t show the Tooltip. Hide all row/column dividers, gridlines, zero lines, axis rulers and axis ticks. Set the border on the colour shelf to None, and once again, most importantly, set the worksheet background colour to None (ie it’s transparent).

The title of the sheet will also need to change colour when the mode differs, so create

Title Light

IIF([Show Light Theme],”Sales by Category”,””)

Also create

Show Dark Theme

NOT([pTheme])

and then

Title Dark

IIF([Show Dark Theme],”Sales by Category”,””)

Add Title Light and Title Dark to the Detail shelf of the All marks card and then update the title of the sheet so both pills are listed and coloured based on the mode – the text for Title Light should be black and the text for Title Dark should be white (though at this point you won’t see this show up when you change the mode).

Finally, as before, we will need some additional fields which will help with the interactivity on the dashboard later, when we don’t want the marks that have not been selected to ‘fade’.

Category for Param

IIF( [pCategory]=[Category],””, [Category])

Add this and True and False to the Detail shelf of the All marks card. Label the sheet Sales by Cat or similar.

Building the Sub-Category by Sales bar chart

The simplest way to build this sheet is to start by duplicating the Category by Sale bar chart sheet, and then drag Sub-Category and drop it directly on top of the Category pill on the Rows shelf. Re-sort by Sales descending.

On the Header Plot marks card, also drag Sub-Category and drop it directly onto the Category pill on the Text shelf.

Manually increase the width of each row and then hide the Sub-Category column (uncheck Show Header)

Create new fields

Title Light Sub Cat

IIF([Show Light Theme],”Sales by Sub-Category”,””)

and then

Title Dark Sub Cat

IIF([Show Dark Theme],”Sales by Sub-Category”,””)

and drag these to directly on top of the Title Light and Title Dark pills on the Detail shelf of the All marks card. Update the title of the sheet to reference these new pills.

Name the sheet Sales by Sub Cat or similar.

Building the rounded borders

The rounded borders displayed on the dashboard are based on utilising annotations on a ‘dummy’ sheet, as described in these blog posts (here and here).

I created a new field

Dummy

“”

And added this to the Detail shelf on a new sheet. I set the background of the worksheet to none, the mark type to polygon and the sheet to entire view. I then added an annotation, resized it to be as large as possible, and set the properties so the shading was set to none, the corners to very rounded and a dark thin border was applied.

I named this sheet Rounded Edge Light 1. I then duplicated to create a 2nd one and named it Rounded Edge Light 2. I then duplicated again, but this time changed the shading of the annotation to be dark grey/ brown, and named this sheet Rounded Edge Dark 1

Duplicate this sheet again and name Rounded Edge Dark 2.

We now have all the components needed to build the dashboard.

Building the core dashboard

Start by creating the layout for the 3 core ‘chart’ sheets and the parameter/filter controls. I used a combination of horizontal and vertical layout containers and adjusted padding to get the layout required. The image below shows how I laid out the display in the item hierarchy section. Note that all the background of all the containers and the objects on the dashboard are set to None (ie transparent).

Adding the rounded borders

With the theme set to Light Theme, set the option to be Floating and drag on the Rounded Edge Light 1 sheet and position it over the Sales by Category chart. Adjust the height and width until you’re happy, and remove the sheet title. From the context menu of the object, set the floating order so the border sheet is ‘behind’ the bar chart (send backward). This allows the bars to still be clicked on and interacted with.

Then with the border object still selected, set the visibility to only show when Show Light Theme is true

Repeat the same process with the Rounded Edge Light 2 sheet, floating it over the top of the Sales by Sub-Category bar chart.

Then switch the theme to Dark Theme. The borders should disappear. Now repeat the above process with the two Rounded Edge Dark sheets, but this time when controlling the visibility of each sheet, select the Show Dark Theme field instead.

If you’ve followed the steps, you hopefully should have something that looks like

Setting the overall dark background

The final step to get the completely dark background is to float a blank object onto the dashboard. Resize the blank object to be positioned at 0,0 and sized 1000 x 800 (ie the same size as the dashboard)

Adjust the floating order of this object and this time set it to Send to Back so it is the very bottom ‘layer’. Then set the background colour of the blank object to the relevant dark brown/grey colour, and then finally set the visibility to only display when Show Dark Theme is true.

Test the display by switching the theme in the parameter.

Adding the interactivity

We need multiple dashboard actions to control the behaviour of the dashboard.

Set Region

Parameter dashboard action that on select of the Region Sales KPI sheet only, sets the pRegion parameter with the value from the Region for Param field.

Set Category

Parameter dashboard action that on select of the Sales by Cat sheet only, sets the pCategory parameter with the value from the Category for Param field.

KPI Deselect

Dashboard filter action that on select of the Region Sales KPI sheet on the dashboard targets the Region Sales KPI sheet directly passing the fields that set True = False.

Set Category Deselect

Dashboard filter action that on select of the Sales by Cat sheet on the dashboard targets the Sales by Cat sheet directly passing the fields that set True = False.

Set Sub-Cat Deselect

Dashboard filter action that on select of the Sales by Sub  Cat sheet on the dashboard targets the Sales by Sub Cat sheet directly passing the fields that set True = False.

And with all this, hopefully you have a fully interactive workbook. My published viz is here.

Happy vizzin’!

Donna