Can you join two spatial files to find the intersection?

Donna ColesLeave a comment

It was Kyle’s turn to set the challenge this week. Like him, I don’t have a need to use map / spatial data much, so whenever there’s a WOW challenge involving them it always makes me think a bit harder (and usually refer to some documentation).

Connecting to & modelling the data

I followed the links in the challenge requirements and downloaded the Shapefile option from each page

This downloaded zip files (one did take some time to download). I then extracted the zip files which generated several files.

In Desktop, I then chose to connect to the Spatial file option and when I navigated to the file location where I had unzipped the data, only the .shp file was available for selection.

I connected to the School District Characteristics data source first, then clicked the ‘carrot’ to access the context menu of the data source, and selected open to access the physical layer of the data canvas

I then clicked Add against the connections section to add another spatial file data source, selecting the School Neighbourhood Poverty file this time and changed the join type between the two data source fields to use the intersects option.

Building the bar chart

On a new sheet add Statename to the Filter shelf, and select Washington. Add Lea Name to the Rows. Create a new field

# Schools

COUNTD([OBJECTID (School Neighborhood Poverty Estimates%2C 2020-21.shp)])

and add this to Columns and sort descending. Widen each row slightly, and increase the width of the Lea Name column a bit. Remove all gridlines, and remove the axis title, and hide the Lea Name column heading. Update the Tooltip as required and update the sheet title.

Building the map

Create a new sheet. Add the Geometry field from the School District Characteristicsset of data to the Detail shelf.

Go back to the bar chart sheet, and update the Satename filter so that it also applies to the sheet you’re building the map on. The map should now be filtered to Washington too. Add Lea Name to the Detail shelf and # Schools to the Tooltip and adjust accordingly.

From the Map > Background Layers menu option, uncheck the options on the Background Map Layers section, so just the Cities and Streets, Highways/Motorways.. options remain selected. Adjust the Colour of the map (via the colour shelf)

Then drag the Geometry field from the School Neighbourhood Poverty data source section onto the canvas and drop it when the Add a Marks Layer section appears

This will add a second marks card. Name this marks card Schools and the other one Districts.

On the Schools marks card, add Name to the Detail shelf and then update the tooltip as required. Remove the row & column dividers.

Adding the interactivity

Add the 2 sheets onto a dashboard side by side and show the Statename filter. Add a dashboard filter action

Filter District

On Select of the bar chart, target the Map passing all fields. Show all values when selection is cleared.

Clicking on a bar should now filter the map and ‘zoom in’ just to that district with the relevant school marks visible.

My published viz is here.

Happy vizzin’!

Donna

Can you show total sales and filtered sales in the same view?

Donna ColesLeave a comment

A guest post this week from Hannah Bartholomew asked us to show a filtered and unfiltered view of data in the same viz.

Building the calculations

We need to show the total sales and the total sales by Sub-Category which won’t be impacted by any filters. We can use a level of detail (LODs) for this.

Sales per Sub Cat

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

format to £ with 0 dp

Pop this and Sales into a table. The total value is identical.

Create a new field

Order Date (Year)

Year([Order Date])

Add Region, Customer Name, Manufacturer and Order Date (Year) to the Filter shelf and show all the filters (display as multiple value drop down to save space). If you now adjust any of the filter values, the Sales value will change, but Sales per SubCat won’t.

We need to show the % of total, so create

% Total

SUM([Sales]) / SUM([Sales per Sub Cat])

and format to % with 0 dp. Add to the table.

This essentially are the measures needed for the overall bar at the top of the display.

Now add Sub-Category to Rows. and you can see the data is now simply broken down to this level without any adjustments to the calculations.

If you reset all the filters back to ‘All’ the Sales per Sub Cat and Sales columns should match with 100% .

Lastly, we need to be able to filter by a start and end date. We need some parameters and additional calculated fields for this.

Create new fields

Max Date

{MAX([Order Date])}

Min Date

{MIN([Order Date])}

Create a new parameter

pStartDate

date parameter that refers to the Min Date field when workbook opens, and displays a range of values based on the values in the Order Date field when the workbook is opened.

Create another parameter pEndDate which is similar except it refers to the Max Date field when opened.

Show these 2 parameters. We then need

Filter – Order Date

[Order Date]>=[pStartDate] AND [Order Date]<=[pEndDate]

Add this to the Filter shelf and set to True.

Then set all the filters to ‘apply to worksheets > all using this data source‘ so that when you create new sheets, they will get automatically added. Test changing the dates and the Sales and %Total will adjust.

Building the Total Sales Bar

On a new sheet, add Sales to Rows and add Sales per Sub Cat to Rows. Show the relevant filter controls and the two parameters.

Add % Total to the Tooltip shelf of the All marks card, then adjust the tooltip on each of the Sales and Sales per SubCat marks cards as required.

Make the chart dual axis and synchronise the axis. Change the mark type on the All marks card to be bar. Adjust the colours of the Measure Names legend to suit. Click the top axis and select move marks to back. Reduce the size of the Sales bar.

Hide the top axis, edit the title of the bottom axis, remove all column and row dividers, make the column axis rules & tick lines slightly more prominent. Edit the title of the sheet.

Building the Sub-Category by Sales Bar

The simplest way for this is to duplicate the above sheet. Then add Sub-Category to Rows, and apply a sort to sort by the Sales per Sub Cat field descending.

Then update the title on the sheet.

Building the ‘Filtered Options’ List

There is a section on the display that shows the values selected in the filters. For this, create a new sheet and then double click in to the area below the marks card and type ‘dummy’ (including quotes) and press enter/return. At this point a mark should display and all the filters get added.

Change the mark type to polygon. Then update the text of the sheet title to reference the filters. Format the background of the whole worksheet to be pale blue.

Now the sheets all need to be arranged on a dashboard. Use layout containers and outer/inner padding to help build the design. I just took a screen snip of the logo Hannah used and then added that as an image to my right hand vertical container. I also created another sheet using a similar ‘dummy’ technique but changed the mark type to shape and added a ‘help’ icon shape I already had.

My published viz is here.

Happy vizzin’!

Donna

Let’s play with Table(au) Extensions!

Donna ColesLeave a comment

Hot of the press with the release of 2024.3, Sean set this challenge to focus on the ability to use table extensions. As a result you will need at least v2024.3 of Tableau Desktop or Tableau Desktop Public installed. At the point of writing, this challenge cannot be completed on Tableau Public itself via web authoring though.

Build the scatter plot

Format Sales and Profit to be $ with 2 dp. Add Sales to Columns and Profit to Rows and add Customer Name to Detail. Change the mark type to circle, reduce the opacity to around 30%, add a blue border and increase the size of the marks. Format the zero lines to be more prominent. Add Segment to the Filters , select all options, and set to apply to worksheets > all using this data source

Build the table extension

On a new sheet, choose Add Extension from the marks type drop down, and on the add an Extension dialog, select the built by Tableau + Salesforce option and then select the Tableau Table option

Select Open on the next screen, and then select OK to the next dialog box.

Add Customer Name, Order Date (as a continuous exact date – green pill), Sales and Profit to the Detail shelf.

Move your mouse to be in front of the SUM(Sales) heading text, and then click on the sort icon that appears a couple of times to get the data sorted by Sales descending. Double click on the SUM(Sales) heading label and edit the label to just Sales. Repeat with the SUM(Profit) heading label.

Click on the context menu associated to the Sales column and select Format

Set the Formatting Type to be Data Bars and change the Fill colour to green

Format the Profit column to have a Formatting Type of Colour Scale and select a diverging colour palette

Click the Format Extension button on the Marks card shelf or the Table Settings icon on the formatting toolbar to load the Format Extension dialog

Change the options so Show Toolbar is Off, Show Column Filters is On and Show Excel Download is On

Adding the interactivity

Add the 2 objects into a horizontal container in a dashboard. Float the Segment filter control and verify changing the value affects both the scatter and the table.

Then add a dashboard filter action

Filter Table

On select of the Scatter, target the Table passing all fields. Show all values when selection cleared.

And that should be it. Unfortunately as Tableau Public doesn’t yet support the extension, I don’t have my published version to share.

Note – I did have some issues getting the table to ‘fit’ completely into the dashboard. I found if I used my larger second screen, ensured the application was maximised, then it would fit properly. Using the application on my laptop screen, it was sometimes a bit hit and miss. This has been raised to the development team.

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 create a brush filter?

Donna ColesLeave a comment

This week’s #WOW2024 challenge was set by Yusuke, challenging us to create a filter for the line chart, using selections from the bar chart. The main aim was to make it as easy to select months with low sales as it is to select months with higher sales.

Building the bar chart

Create a new field

Monthly Sales

[Sales]

and format to $ Thousands (K) with 0 dp.

Also create

Order Date Month

DATE(DATETRUNC(‘month’, [Order Date]))

Add Order Date Month to Columns at the continuous month level (green pill) and add Monthly Sales to Rows. Change the mark type to Bar and set the size of the bar to as wide as possible. Edit the date axis, and remove the title, then fix the tick marks to start on 1st Jan 2021 with an interval of every 1 year.

Create a set call Order Date Month Set, based off of the Order Date Month field (right click the field > create > set, and select a set of dates). Add Order Date Month Set to the Colour shelf and adjust the colours accordingly. Add a dark grey border to the bars too. Modify the Tooltip to suit.

Create a new field

Max Monthly Sales

{MAX({FIXED [Order Date Month]: SUM([Sales])})}

(for each month, get the sum of sales, then return the maximum of all these).

Add this field to Rows. On the Max Monthly Sales marks card, reduce the opacity to 25% and remove the border (all on the Colour shelf)

Set the char to Dual Axis and synchronise the axis. Remove the Measure Names field from the All marks card.

Hide the right hand axis, remove all row and column dividers. Darken the row gridlines slightly, and add the instructional text as the title of the sheet.

We are ultimately going to make use of set actions to define the dates selected by the user. For this we will need to pass the exact date selected, so add Order Date Month to the Detail shelf of the All marks card as a continuous exact date (green pill).

We’re also going to not want the bars to be ‘highlighted’ when selected, so create fields

True

TRUE

and

False

FALSE

and add both to the Detail shelf of the All marks card.

Building the Line Chart

Create a new field

Selected Sales

IF [Order Date Month Set] THEN [Sales] END

and format to $ Thousands to 2dp.

On a new sheet add Order Date to Columns at the continuous day level (green pill), add Region to Rows and add Selected Sales to Rows.

Change the colour of the line and set line markers (via the colour shelf) and reduce the size of the line. Show mark labels, and set to just label the maximum value per pane.

Adjust the row banding so the band size is set to 1

Remove the column dividers, but set the row dividers to be darker grey. Adjust the row gridlines to be a slightly darker grey. Adjust the title of the Selected Sales axis, and remove the title from the date axis. Format the data axis, so it displays a custom date format of mmm dd. Right click the Region label at the top of the chart and hide field labels for rows.

Create fields

Min Date

{MIN(IF [Order Date Month Set] THEN [Order Date Month] END)}

which will return the date of the earliest month selected in the set and

Max Date

DATE(DATEADD(‘day’, -1,
DATEADD(‘month’, 1, {MAX(IF [Order Date Month Set] THEN [Order Date Month] END)})
))

which finds the maximum month selected in the set (which will be 1st of the max month), adds on a month, and takes off a day to get the last day of the maximum month.

Add these to the Detail shelf as continuous exact dates, and then update the Title of the sheet to reference the fields.

Then create

Tooltip: Date

[Order Date]

and add to the Tooltip and adjust the Tooltip to suit.

Finally, depending how the user selects the dates, there may end up being a break in dates. Right click on the Order Date Month Set and select Show Set. Adjust the dates, so there is at least 1 unselected value between the dates.

To make a continuous line between the dates, click the context menu against the Selected Sales pill on Rows and select Format. On the options on the left hand side, select Pane and at the Special Values option, select Marks: Hide (Connect Lines).

Adding the interactivity

Put the 2 sheets onto a dashboard. Create a dashboard set action

Select Months

On select of the bar chart, target the Order Date Month Set by assigning values to the set when the action is run, and keeping set values when the selection is cleared.

To stop the bars from highlighting on selected, create a dashboard filter action

Deselect Marks

On select of the bar chart on the dashboard, target the Bar Chart sheet, passing in the fields True = False.

And this should now complete the challenge. My published viz is here.

Happy vizzin’!

Donna

Can you use the Sankey Viz Extension?

Donna ColesLeave a comment

For this week’s challenge, Lorna set the relatively straightforward task of creating a Sankey using Tableau’s Viz Extension. The challenge may not have the complexity/nuances you might sometimes find, but at #WOW HQ, giving you the opportunity to try out new features is one of the key benefits of running this community project.

This blog will be brief:-)

Connect to the data source, and and drag Number from the Dimensions section of the data pane (above the line) to the Measures section (below the line).

On the Marks card, select to Add Extension

in the Add an Extension dialog window, select the Sankey (by Tableau) option which is likely to be the first listed (if not, search for it).

then click Open on the following screen and the marks card will change and give different buttons/shelves

Add Age group to Level. Then add Mode of former study to Level, followed by Level of qualification obtained and then Domicile.

The Levels listed from top to bottom on the marks card is represented from left to right in the Sankey on the canvas.

Add Number to Link to adjust the size of the flows between each Level.

Then press Format Extension and adjust settings as required. I set the Level Padding and the Edge Padding to 10 to increase the spacing between each ‘row’ and ‘column’. I set the colour palette to Purple-Pink-Gray and set the Level Labels font to bold.

I then adjusted the Tooltip to suit and added the sheet to a dashboard. Simples 🙂

Tableau’s documentation on Viz extensions here and my published viz is here.

Happy vizzin’!

Donna

Can you create multi-select parameters?

Donna ColesLeave a comment

For this week’s #WOW2024 challenge, Kyle simulated a real-world challenge he’s faced at work where he wanted the ability to select multiple parameters as he was sourcing data from multiple data sources, so using traditional filters didn’t work.

Connecting to the data

The challenge required connecting to the Superstore data twice but applying a data source filter to each connection to restrict the Order Date to Year 2023 or 2024.

After making the 1st connection and filtering to 2023, I renamed the data source and appended 2023.

I then added a data source and made the 2nd connection to Superstore, this time adding a data source filter to 2024. I then renamed this data source and appended 2024. So I ended up with 2 data sources at the top of the data pane window.

Building the line charts

Starting with the Superstore – 2023 data source, put Order Date on columns and change to be the continuous (green) Week number option. Add Sales to Rows.

Then

  • format Sales to be $ with 0 dp
  • format Order Date to be mmm dd custom date format
  • to match the solution, set the Date Property of the data source to start a week on Sunday (right click data source > date properties)
  • Remove the titles against both axis
  • adjust the Tooltip
  • Remove all gridlines, zero lines, axis rulers & axis ticks
  • Name the sheet 2023 Sales

Create a new sheet, and the repeat all the steps but source the fields from the Superstore 2024 data source.

Building the Category Selection

On a new sheet, manually type MIN(1) into the Columns and add Category from the Superstore 2023 data source to Rows. Amend the axis to fix it from 0 to 1. Set to Entire View. Increase the Size to 100%. Add Category to Label. Adjust the font and align centrally. Hide the Category headers and the Min(1) axis. Remove the Tooltip.

To build the multi-select parameters, we’re going to use a sheet to capture the interactions the user makes into a parameter that will store a delimited string of selected values. This is using the same principles discussed in a previous challenge I created and blogged about here.

We need a parameter

pSelectedCategories

string parameter defaulted to empty string

We’ll use a parameter action to capture the user selection and add it into the pSelectedCategories parameter.

When using delimited strings, you need to choose a character to separate the field values that isn’t going to appear within the field value itself; ie choose a character that won’t appear within a Category name. In this instance I’m choosing | (pipe), and I’m going to build up the parameter as follows

ActionpSelectedCategoriesDisplay
Initial state<empty string>All Categories selected – coloured green
1 category selected eg Furniture|Furniture|Furniture is green, others categories grey
2 categories selected eg Furniture then Office Supplies|Furniture||Office Supplies|Furniture & Office Supplies are green, Technology is grey
3 categories selected eg Furniture, then Office Supplies, then Technology|Furniture||Office Supplies||Technology|All Categories selected – coloured green
Existing category is selected again eg Office Supplies|Furniture||Technology|Furniture & Technology are green, Office Supplies is grey

We need a calculated field to populate the parameter, which will get modified by comparing what’s already in the parameter with the Category being selected.

In the Superstore 2023 data source create

Category for Param

IF CONTAINS([pSelectedCategories], [Category]) THEN REPLACE([pSelectedCategories],’|’ + [Category] + ‘|’, ”) //selected category is already in the parameter, so remove it
ELSE [pSelectedCategories] + ‘|’ + [Category] + ‘|’ //append current category selected to the existing parameter string
END

Add Category for Param to the Detail shelf.

We need to set the colour of the bars. Show the pSelectedCategories parameter and manually type in |Furniture|

Then create

Category is Selected

[pSelectedCategories] = ” OR
CONTAINS([pSelectedCategories], [Category])

Add this to the colour shelf, and adjust the colours accordingly

Remove the text from the pSelectedCategories parameter, and all the bars should be green.

Format the bars so there is a light grey thick row divider, and set the background of the worksheet to the same light grey. Reduce the Size slightly, so there is a noticeable gap between the bars.

When added to the dashboard, we won’t want the unselected bars to ‘fade’, so we’ll use the True/False trick, which means we’ll need to create

True

TRUE

False

FALSE

and add both these fields to the Detail shelf.

Name the sheet Category.

Building the Region and Ship Mode Selections

Basically repeat the above steps on a separate sheet for each selector. You may find it easier to duplicate the Category sheet and then replace the various fields.

You’ll need to create a pSelectedRegions and a pSelectedShipModes parameter, and calculated Region for Param, Region Is Selected and Ship Mode for Param and ShipMode Is Selected calculated fields.

Name the new sheets Region and Ship Mode.

Filtering the line charts

On the 2023 Sales sheet, add Category Is Selected, Region Is Selected and Ship Mode Is Selected to the Filter shelf, and set all to be True.

Switch to the 2024 Sales sheet.

Recreate the 3 ‘Is Selected’ fields in the Superstore 2024 data source. You can either do this manually, or select the fields in the Superstore 2023 data source (ctl-click to multi-select), the right click and Copy

then switch to the 2024 Sales sheet, and right-click anywhere in the right hand data pane and paste.

Then add each of the fields to the filter shelf and set to True.

Adding the interactivity

Make sure all the parameters are empty, then add all the objects to a dashboard. I used a vertical layout container to place the Selector objects, as I could then set them to be distributed evenly. I also set the background of the layout container to the same light grey as the worksheet, and centrally aligned all the sheet titles.

6 dashboard actions are required, 2 for each selector.

Select Categories

Parameter action that on select of the Category sheet, sets the pSelectedCategories parameter with the value from the Category for Param field.

Deselect Categories

Filter dashboard action that on select of the Category sheet on the dashboard, targets the actual Category sheet, passing the values of True = False.

Create a version of each of these dashboard actions for the Region sheet and the Ship Mode sheet, and that should complete the challenge.

My published version is here.

Happy vizzin’!

Donna

Paris Olympics 2024 – What medals did each country win each day?

Donna ColesLeave a comment

For this week’s #WOW2024 challenge, I asked the community to rebuild this unit chart depicting the medals won each day by country. I built this out while the Olympics was on, curating the data myself, so there is a chance it may not match ‘official’ records (some events got delayed, some medallists may have since been disqualified or reinstated).

Creating custom shapes

The challenge requires a set of custom shapes representing the sports. Download all the image files from the Olympic Sports directory here and save them into a new folder in your …\My Tableau Repository\Shapes directory (as discussed here).

Building the viz

Add Day as a discrete (blue) pill to Columns. Change the mark type to Shape and add Event to Shape. Choose the Olympic Sports shape palette you created above (click reload shapes if it isn’t visible), then click Assign Palette. As the images are named exactly as the events are, they should all match without the need to manually assign each shape to the event. Also note, doing this step first, ensures all events are listed and assigned.

Add Country to Filter and select Great Britain. Show the filter and change to a single select drop-down and customise so you can’t select the ‘All’ option.

You can immediately see the basic layout we’re after. However, as we ned to display shapes and circles to represent the medal types, we need to use a dual axis chart. But at this point there is no axis.

Change the Day field on Columns to be continuous (green). This gives us a axis, but the marks for each event are on top of each other.

Create a new field

Index

INDEX()

Add to Rows and adjust the table calculation to compute by Event.

Edit the Index axis and set it to be reversed. Then add Medal, Country and ID to Detail (to ensure distinct marks are displayed), and readjust the table calculation of the Index field to also compute by these fields as well.

Add Event Detail, Athlete and Notes to the Tooltip shelf, and adjust accordingly.

If you change the Country filter to another entry, eg Albania, the display won’t show every day as they only won medals on days 15 & 16. They only won 1 medal on those days too, so the y-axis has also altered. We don’t want this to happen – the ‘frame’ of the display should remain regardless of the country selected. To resolve this, change the data type of the Day field to Number (decimal) (right click field > change data type). Then edit the Day axis and fix from 0.5 to 16.5 – changing the data type means we can fix using decimal numbers which means we don’t get 0 and 17 displayed on the axis.

To fix the height of the chart, we could ‘hardcode’ it as well, but while the number of days in an Olympic Games cycle is always static for cycle, the maximum medals won per day could change – so if I wanted to reuse this chart on a set of data for a different Olympic Games, I’d have to find out what the max was to hardcode. So instead, we’ll make this dynamic using a nested LoD calc.

Max Medal Count

{FIXED: MAX(
{FIXED Country: MAX({FIXED Day, [Country]: COUNT([Daily Medal Winners])})}
)}

breaking this down…

{FIXED Day, [Country]: COUNT([Daily Medal Winners])} returns the count of medals per day per country

{FIXED Country: MAX(<code above>)} then returns the max number of the above per country

then the outer (FIXED: MAX()} statement gets the maximum of all of these

Add this to the Detail shelf, then add a Reference Line to the Index axis that shows the average of this field, and doesn’t display any line or values, or tooltips. The axis should extend.

If you select other countries, the axis should remain the same.. until you choose USA and it moves to show 20, as the maximum number of medals in one day has been hit.

Again we don’t want this causing any shift in the ‘frame’. So to resolve, double click into the Max Medal Count field and type + 1 at the end, so the reference line is actually 1 higher. The 20 is still visible, but now it’s visible for all countries. This axis won’t be displayed anyway, but now it won’t shift at all either. Chang the

Now the main framework is in place, we can add the ‘medals’. Add another instance of Day to Columns. On the Day(2) marks card, change the mark type to circle then add Medal to Colour and adjust accordingly. I used bronze: #ce8451, silver: #b3b7b8, gold: #edc948 and the reduced the opacity to 50% and added a dark grey border. Re-order the values in the colour legend and then edit the table calculation on the Index pill again, and ensure Medal is listed first. This should make any bronze medals won on a day be listed at the top, followed by silver and then gold

NOTE – I noticed at this point, that adjusting the Index meant I lost the reference line, so I had to reapply.

Make the chart dual axis and synchronise the axis. You may need to make adjustments to the size of each of the marks cards so the event shapes are within the circles, but this is probably best done after you’ve added to the dashboard.

Hide all gridlines, zero lines, axis rulers and row & column dividers. Hide the Index axis (uncheck show header). Edit the bottom Day axis, delete the title and set the tick marks to None for both major & minor tick marks. Edit the top axis and hide the title. Increase the font of the top axis labels.

Finally we need to show the count of the medals each day. Create field

Count Medals by Country Per Day

{FIXED Day, [Country]: COUNT([Daily Medal Winners])}

then

Label: Medal per Day

IF INDEX()=SIZE() THEN SUM([Count Medals By Country Per Day]) END

Add this to the Label shelf of the ‘circles’ marks card, and adjust the table calculation so it’s computing by all fields except Day and that Medal is listed at the top. The label should display underneath the last circle.

Format the font to be a bigger/bolder style and explicitly align bottom centre, then add to a dashboard and that should be it.

My published viz is here.

Happy vizzin’!

Donna

Can you build a Measure Names Waterfall?

Donna Coles1 Comment

I’ve been on my holibobs, so haven’t blogged a solution for a few weeks. It’s been a bit of a struggled to get my head re-engaged to be honest, as I’m sure you can all relate to.

Anyway this week’s challenge was set by Sean, to produce a waterfall chart depicting specific measures without any pivoting.

I had a little bit of an initial struggle with this… firstly I assumed from the title of the challenge that I would need to be using Measure Names/Measure Values, and secondly, as nothing was mentioned, that I just had to use the data provided. This is how far I got…

but I couldn’t figure out how to get the sizes of the gantt bars inverted for some of the measures…

So I had a bit of a Google, and came across this video by one of our old WOW alumni, Luke Stanke. It made use of a scaffold data source which basically provide placeholders for each of the specific measures we want to display. Sean hadn’t explicitly said we’d need a scaffold, but then he hadn’t explicitly said we couldn’t use one either… so I had a quick peak at his solution, and I found he had used one.

So I went about recreating the challenge just by following Luke’s video. As a result, this blog won’t be as detailed, but I’ll detail the core information needed.

The scaffold data set

I created a simple excel sheet on 1 column called Points with values 1 to 5 listed.

This was then related to the Financials.csv data Sean provided using a relationship calculation of 1=1 as demonstrated in the video.

The calculations

4 calculations are created in the video

Label

CASE [Point]
WHEN 1 THEN ‘Gross Sales’
WHEN 2 THEN ‘Discounts’
WHEN 3 THEN ‘Net Sales’
WHEN 4 THEN ‘COGS’
WHEN 5 THEN ‘Profit’
END

Start

CASE [Point]
WHEN 1 THEN 0
WHEN 2 THEN [Gross Sales]
WHEN 3 THEN 0
WHEN 4 THEN [Gross Sales] – [Discounts]
WHEN 5 THEN 0
END

Value

CASE [Point]
WHEN 1 THEN [Gross Sales]
WHEN 2 THEN [Discounts] * -1
WHEN 3 THEN [Gross Sales] – [Discounts]
WHEN 4 THEN [Cogs] * -1
WHEN 5 THEN [Profit]
END

format this to $, millions with 2 dp.

Colour

SIGN(SUM([Value]))

convert this to discrete

Note on the date field

When I connected to the csv, I found the dates were being displayed to me in the UK format so a date in source of 06/01/2024 was reporting as 6th Jan, when it was intended to represent 1st Jun. There’s probably something I could have done with regional settings etc, but the quickest way for me to resolve as create

Date Adjust

MAKEDATE(YEAR([Date]), DAY([Date]), MONTH([Date]))

which just transposed the month & day and gave me the dates expected.

Building the Viz

Add Date to Filter, select Month-Year and select May 2024. Add Label to Columns and apply a Sort to sort by Point ascending. Add Start to Rows and change the Mark type to Gantt

Add Value to Size and Colour to Colour and adjust colours to suit

Add Value to Label and adjust font to match mark colour and increase size and style, Set the sheet to Entire View. Uncheck Show Tooltip.

Double click into the Rows shelf and type SUM([Start]) + SUM([Value]). This will create a second marks card. Change the mark type of this to line and remove all fields from the marks card shelf. Set the line type (via the Path shelf) to stepped and manually adjust colour to black.

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

Finally tidy the display up by hiding both axis, removing row & column dividers, hiding the Label title (right click and hide field label for columns) and formatting the Measure Name labels to a larger, darker font style.

Add fields Country, Product and Segment to the Filter shelf, then add to a dashboard.

My published viz is here.

Happy vizzin’!

Donna

Can you compare an Olympic medallist’s age to a user’s age?

Donna ColesLeave a comment

As the Paris 2024 Olympic Games continues, then #WOW continues with another Olympic themed challenge, this time set by Kyle.

Defining the core calculations

There’s a lot of LoD calcs used in this, so let’s set this up to start with.

Avg Age Per Sport

INT({FIXED [Sport]: AVG([Age])})

Avg Age Per Sport – Gold

INT({FIXED [Sport]: AVG(IF [Medal]=’Gold’ THEN [Age] END)})

Avg Age Per Sport – Silver

INT({FIXED [Sport]: AVG(IF [Medal]=’Silver’ THEN [Age] END)})

Avg Age Per Sport – Bronze

INT({FIXED [Sport]: AVG(IF [Medal]=’Bronze’ THEN [Age] END)})

Oldest Age Per Sport

INT({FIXED [Sport]: MAX([Age])})

Youngest Age Per Sport

INT({FIXED [Sport]: MIN([Age])})

Format all these fields to use Number Standard format so just whole numbers are displayed.

Let’s put all these out in a table – add Sport to Rows and then add all these measures

Kyle hinted that while the display looks like a trellis chart, it’s been built with multiple sheets – 1 per row. So we need to identify which row each set of sports is associated to, given there are 5 sports per row.

Row

INT((INDEX()-1)/5)

Make this discrete, and add this to Rows, and then adjust the table calculation so that it is explicitly computing by Sport, and we can see how each set of sports is ‘grouped’ on the same row.

Building the core viz

The viz is essentially a stacked bar chart of number of medallists by Age. But the bars are segmented by each individual medallist, and uniquely identified based on Name, Medal, Sport, Event, Year.

Add Sport to Columns, Age to Columns (as a continuous dimension – green pill) and Summer Olympic Medallists (Count) to Rows.

Change the mark type to Bar and reduce the Size a bit. Add Year, Event and Name to the Detail shelf, then add Medal to the Colour shelf and adjust accordingly. Re-order the colour legend, so it’s listed Bronze, Silver , Gold.

For the Tooltip we need to colour the type of medal, so need to create

Tooltip: Bronze

UPPER(IF [Medal] = ‘Bronze’ THEN [Medal] END)

Tooltip: Silver

UPPER(IF [Medal] = ‘Silver’ THEN [Medal] END)

Tooltip: Gold

UPPER(IF [Medal] = ‘Gold’ THEN [Medal] END)

Add all these to the Tooltip shelf, then adjust the Tooltip accordingly, referencing the 3 fields above and colouring the text.

The summary information for each sport displayed above each bar chart, is simply utilising the ‘header’ section of the display. We need to craft dedicated fields to display the text we need (note the spaces)

Heading – Medal Age

“Avg Medalling Age: ” + STR([Avg Age Per Sport])

Heading: Age per Medal

“Gold: ” + STR(IFNULL([Avg Age Per Sport – Gold],0)) + ” Silver: ” + STR(IFNULL([Avg Age Per Sport – Silver],0)) + ” Bronze: ” + STR(IFNULL([Avg Age Per Sport – Bronze],0))

Heading: Young | Old

“Youngest: ” + STR([Youngest Age Per Sport]) + ” Oldest: ” + STR([Oldest Age Per Sport])

Add each of these to Rows in the relevant order. Widen each column and reduce the height of each header row if need be

Adjust the font style of all the 4 fields in the header. I used

  • Sport – Tableau Medium 14pt, black, bold
  • Heading – Medal Age – Tableau Book 12pt, black, bold
  • Heading: Age per Medal – Tableau Book 10pt, black bold
  • Heading: Young | Old – Tableau Book 9pt, black

The title needs to reflect the number of medallists above a user defined age. To capture the age we need a parameter

pAge

integer parameter defaulted to 42

Then we can create

Total Athletes at Age+

{FIXED:COUNTD( IF [Age]>= [pAge] THEN [Name] END)}

Add this to the Detail shelf, then update the title of the viz to reference this field. Note the spacing to incorporate the positioning of the parameter later.

The chart also needs to show background banding based on the selected age. Add a reference line to the Age axis (right click axis > add reference line), which references the pAge parameter, and fills above the point with a grey colour

Ultimately this viz is going to be broken up into separate rows, but we need the Age axis to cover the same range. To ensure this happens, we need

Ref Line – Oldest Age

{FIXED: MAX([Oldest Age Per Sport])}

Add this to the Detail shelf, then add a reference line which references this field, but doesn’t actually ‘display’ anything (it’s invisible).

Tidy up the visual – remove gridlines, zero lines, row & column dividers. Hide the y-axis (uncheck show header). Remove the x-axis title. Hide the heading label (right click > hide field labels for columns). Hide the null indicator.

Filtering per row

Add the Row field to the Filter shelf and select the only option displayed (0). Then edit the table calculation and select all the fields to compute by, but set the level to Sport.

Show the Row filter and select the 0 option only. – only the first 5 sports are now displayed.

Name this sheet Row1. Then duplicate the sheet, set the Row filter to 1 and name the sheet Row2. Repeat so you have 7 sheets.

Building the dashboard

Use vertical containers to help position each of the 7 rows on the dashboard. For 6 of the sheets, the title needs to be hidden. I used a vertical container nested within another vertical container to contain the 6 sheets with no title. I then fixed the height of this ‘nested’ container and ‘distributed” the contents evenly. But I did have to do some maths based on the space the other objects (title & footer) used up on my dashboard, to work out what the height should be.

You’ll then need to float the pAge parameter onto your layout, and may find that after publishing to Tableau Public, you’ll need to edit online to tweak the positioning further.

My published viz is here.

Happy vizzin’!

Donna