Tag: Floating

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

Let’s Build a Map!

Donna ColesLeave a comment

Inspired by a viz from Klaus Schulte, Sean Miller set this week’s challenge to recreate a hex map with state shapes using Superstore.

Building the data model

I referenced this Tableau blog post and downloaded the HexmapPlots excel file included. I then used a relationship to ‘join’ the Sample – Superstore excel file I was using with the HexmapPlots file, joining on State/Province = State

Since I had the other blog post already open, I then followed the steps included to start building the map.

Building the hex map

Add Column to Columns and aggregate to AVG, and add Row to Rows and also aggregate to AVG. Add State/Province to Detail. Edit the Rows axis and set to be Reversed.

Note – it’s possible you may have extra States showing. As I’m writing I’ve realised I’m rebuilding against an extracted data source that has a filter I originally applied as a global filter, which has now been included in the extract. So you may need to add State/Province to the Filter shelf, and set to exclude NULL and District of Colombia. This filter will need to be applied to all sheets you build.

Change the mark type to Shape and select a Hex shape. I already have a palette full of Hex shapes, but the blog post provides a shape to use and add as a custom shape if you haven’t got one. Increase the size of the marks.

Create a new field

Profit Ratio

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

format this to % with 1 dp, and then add to the Colour shelf.

Now add a second instance of Row to the Rows shelf and set to AVG again. Set the mark type of this marks card to Map. Remove the Profit Ratio field from Colour on this card too. Assuming your Map location is set to USA, you should have State outlines depicted (Map -> Edit Location).

Set the chart to be dual axis and synchronise the axis. The State shape axis should now be inverted. Independently adjust the sizes of the Hex shape and the state shapes, so the states sit inside the hexagons.

Right click on the right axis and move marks to back. The adjust the Colour of the hex shapes so its around 85% transparent.

Now adjust the Tooltip on the hex marks to match the requirement.

To fill-out the available area, I also chose to fix both the axis (right click axis -> edit axis). The Column x-axis I set to range from 1 – 12, and the Row y-axis, I set to range from -0.9 – 9. Then hide all axis, and remove all row/column gridlines, divider lines, axis lines and zero lines. Hide the 10 unknown indicator, and set the background colour of the whole worksheet to a pale grey.

Building the Line Chart

This is super simple, Tableau 101 🙂

Add Order Date to Columns and set to be a continuous month (green pill showing month-year). Add Sales to Rows. Change the colour of the line to grey. Hide the x-axis and remove all gridlines, dividers, axis lines and zero lines. Set the background colour of the worksheet to None (ie transparent). Update the tooltip.

Building the Scatter Plot

Add Sales to Columns, and Profit to Rows. Add State/Province to Detail shelf and add Profit Ratio to Colour. Change the mark type to circle. Remove all gridlines, row column dividers and axis rulers. Only the zero lines should remain. Adjust the tooltip and set the worksheet background to None (transparent).

Putting it all together

Create a dashboard and set the size as stated. Set the background of the dashboard to pale grey (Dashboard – > Format).

Add the Hex map, and hide the title. Click on the Profit Ratio legend object and set to be floating. Then remove the right hand vertical container. Move the Profit Ratio legend to a suitable location.

Then add a text box as a floating object and use it to create the title. Add both the trend line and the scatter plot charts as floating objects without titles. Just position them as required. You can always use gridlines (Dashboard -> Show Grid) to help you line things up.

Finally add the interactivity.

Add a highlight dashboard action which highlights the hex map and the scatter plot when either of the other is selected ‘on hover’, and just targets the State/Province field.

Then add a Filter action which on hover of the Trend chart, targets the remaining charts.

And hopefully that’s it. My published viz is here.

Happy vizzin’!

Donna

Can you create a common starting point?

Donna ColesLeave a comment

Kyle set the challenge this week, revisiting his favourite topic – baseball. The aim was to build what I’ve often referred to as a ‘rocket chart’, as it charts progress from a single ‘launch’ date/point. However having had a quick google, I can’t see any other reference to this being used for this type of chart….no idea where it came from <shrug>.

Anyway, the requirement was to compare the profiles of when home runs (HRs) had been accumulated over the course of a player’s career, restricting to just the players who are in the all-time top 10. These players hadn’t necessarily played during the same years or even decades, so there was a need to baseline the information according to the days since they started. Kyle also threw in the requirement that this was to be an LoD based challenge only, with no use of table calculations.

Build the basic chart

As mentioned above, we first need to ascertain how many days have passed between when the player hit their first home run, and the subsequent dates. We use a FIXED LoD to work out the minimum date per player

Min Date Per Player

DATE({FIXED [Player] : MIN([Date])})

And with that we can the work out the number of days that have passed

Days Since Min Date

DATEDIFF(‘day’,[Min Date Per Player], [Date])

And with this, we can quickly build out the main crux of the chart. Add Days Since in Date to Columns, and change to be a continuous dimension. Add Career HR to Rows and amend the aggregation to use AVG rather than SUM, as I found there looked to be duplicate records for some dates for the same player. Add Player to Detail.

Colouring the lines

Kyle provided a custom colour palette to use based on the team colours of the player. I updated by preferences.tps file with this data, and closed and reopened Tableau Desktop to ensure it picked it up. For more information on working with custom colour palettes see this Tableau help article.

Along with the player colours, we also need to identify which player has been selected.

For that we need a parameter to define who the selected player is

pPlayer

string parameter using a List where the values are added from the Player dimension. this causes the default to be set to Albert Pujols.

Show the parameter on the display.

We can now create

Is Selected Player?

[Player] = [pPlayer]

which will return a boolen true/false.

Kyle stated that we should be able to set the colours without having to manually click against every Player|T or F combination.

Now I managed this when I first built my solution, but in writing this blog and trying to replicate the steps, I’m not getting the same behaviour. So I have managed to come up with another way. The gif below hopefully demonstrates, but I’ll list the steps too.

Move the Player pill from Detail onto Colour

Edit the Is Selected Player field to just return True (use // to just comment out the original calculation)

Add Is Selected Player to the Detail shelf, then click the detail icon to the left of the pill and change it to Colour. This is a way to get multiple pills on the Colour shelf. Dragging will just replace the field being used for colour.

The colour legend dialog box should display a list of <Player>, True entries (if the legend isn’t displaying go to Worksheet > Show Cards > Reset Cards – you may then have to add the parameter to the display again).

Edit the colour legend, select the MLB HR Top 10 colour palette and click Assign Palette. This will automatically assign the relevant colour to each entry, since they were added based on alphabetical order.

Re-edit the Is Selected Player field, so it is back to [Player] = [pPlayer].

The entries in the colour legend will now only list one <Player>, True entry and the rest all false.

Edit the colour legend, and multi-select (ctrl-click) all the False entries, and then select the lightest shade of grey from the Seattle Grays palette. This should give you the desired display.

Select Alan Rodriguez from the parameter control. Both Albert, False & Alan, True should now be coloured. Edit the colour legend again and manually set the Albert Pujols, False entry to the same grey shade.

Now if you select any other player, only 1 line should be coloured, and it should be coloured to the corresponding player’s colour.

Setting the Tooltip

Add Season HR to Tooltip and change the aggregation to AVG. Add Date to Tooltip too and set it to be an Attribute. Amend the tooltip accordingly.

Adding the highest season HR indicator

Firstly we need to determine what the maximum Season HR value is per player

Max Season HR Per Player

{FIXED [Player]: MAX([Season HR])}

With this, we then want to get the corresponding Career HR value for that same time.

Career HR | Max Season HR

IF [Season HR] = [Max Season HR Per Player] THEN [Career HR] END

Add this field to Rows and change the aggregation to Avg.

Set to Dual Axis, Synchronise Axis and then set the mark type to Circle. Adjust the size of the circle mark slightly if need be.

Labelling the lines

On the Line marks card, add Player and Career HR to the Label shelf. Adjust the aggregation of Career HR to Avg. Edit the label, so only line ends are labelled. Adjust the font size to something quite small, and set the colour to Match Mark Colour.

Finally remove all gridlines, row & column dividers, and hide the axis. Title the chart.

When added to a dashboard, I then used a floating text object for the introductory text and positioned the parameter as a floating object underneath the text.

My published viz is here.

Happy vizzin’!

Donna

Let’s build a Marrimeko Chart!

Donna Coles2 Comments

Sean set the challenge this week and went retro, revisiting a challenge from 2017 originally set by Emma Whyte to build a Marrimeko chart.

Hang on… a what chart? Marrimeko…???

A good place to understand what a Marrimeko chart actually is, is this blog post by Tableau Visionary & Ambassador, Jonathan Drummey. This leads onto this post which explains the steps to help build.

The main concept of this type of chart is to show part-to-whole relationships across two variables at once.  In the above for each job title, we have a split vertically based on proportion by gender; but the proportion of people with each job title is also being represented horizontally by the width of the ‘bars’. Both the x and the y axis are up to 100%.

I was actually around when the original challenge was set, so have my solution from then already on my Tableau Public profile. But it was a long time ago, with an older version of Tableau, so I built this from scratch (using the referenced blogs as a quick refresher). This blog will take you through the steps I took (which actually didn’t end up that different from the last time).

The data

The data set isn’t very large and contains all the information we will need, but not quite in the structure we might expect

The Sub-Type = Total field contains the % we will need to define how the width of the bars should be split (all the Total values add up to 100% or just about).

Whereas the combination of the Sub Types of Male and Female define how the height of the bars should be split (Male + Female = 100% for each Job Type).

So when we come to build, we ideally want to ‘filter out’ the Sub-Type = Total field, but we still need to retain this information to build the viz.

We need to get the % values associated to each Total row to be reflected against the Male/Female rows.

We’ll create an LoD for this

Job Type Percentage

{FIXED [Job Type]: SUM(IF [Sub-Type] = ‘Total’ THEN [Percentage]END )}

format this to % with 0 dp

If we pop the data out into a tabular format as below and add Sub-Type to the Filter shelf so it excludes the Total row, we can see we have essentially transposed the values which were stored against the Total row into it’s own column.

Building the Marrimeko chart

As stated above, the Job Type Percentage field defines the width of the bars we’ll be displaying. But before we can get to the point, we also need to define where on the x-axis each mark should be positioned. This too is based on Job Type Percentage, but is the cumulative value.

On a new sheet add Job Type to Rows, Filter by Sub-Type to exclude Total, and add Job Type Percentage to text. Sort by Job Type Percentage descending.

Now add a Running Total Quick Table Calculation to the Job Type Percentage field, and the cumulative values will display.

It is these values we need to plot on the x-axis.

Duplicate the sheet.

Edit the table calculation against the Job Type Percentage field, so that it is explicitly set to compute using Job Type. This is important to ensure the calculation is retained regardless as to where we put the pill on the canvas.

Now move this pill from the Text shelf to Columns, and change the mark type to Gantt Bar. Move Job Type from Rows to the Detail shelf. Reapply the Sort to the Job Type field so its sorting by Job Type Percentage descending

The markers for the Gantt should all be positioned at the correct position.

Now add Percentage to Rows and change the mark type to Bar.

Add Job Type Percentage to the Size shelf, then click on the shelf, and change to be Fixed and aligned Right.

Now add Sub-Type to Colour. Manually drag the values in the COlour legend to re-order (so Male is listed first) and adjust colours to suit.

Ta dah! The crux of the chart.

Now to add the ‘bells and whistles’.

The chart is labelled, but only for the Entry Job Type. We need a calculated field to manage this.

Label: Sub Type

IF [Job Type] = ‘Entry’ THEN [Sub-Type] END

Add this to the Label shelf, and set to Show Mark labels. If it doesn’t show (like it didn’t on mine), change the field to be an attribute (I need to do a dig on why this is required… I think it’s something to do with the table calcs….).

I tried to use the alignment setting of the label to set to ‘top left’, but while they would left align they wouldn’t move to the top. I manually moved them instead – simply click on the label and when the cursor changes to a cross, drag the label to the desired position.

Do this for both labels, and adjust the formatting of the label too (I set it to 12pt bold).

Remove all gridlines, axis lines, zero lines etc.

Add a 50% reference line on the y-axis. Right click on the Percentage axis ->Add Reference Line. Add a table level constant of 0.5 which is a thin dotted grey line that is almost invisible (due to the colour selected).

Hide the axes (uncheck show header).

The tooltip has a minor nuance in that it refers to ‘Women’ & Men rather than Male & Female, so we need a field for this

Gender

CASE [Sub-Type]
WHEN ‘Female’ THEN ‘Women’
WHEN ‘Male’ THEN ‘Men’
END

Add this to the Tooltip shelf and adjust the tooltip accordingly.

Labelling the axes

The final viz has labels on both the x and y axis, but these are all managed by text/image objects positioned ‘cleverly’ on the dashboard. It’s a bit of trial and error to get everything aligned as required.

To label the Job Type, I used a horizontal container, positioned beneath the chart, with several text objects, some of which had the text rotated.

The Equal Proportions and Less Equality text are both floating text objects. I saved an ‘arrow’ image from the internet and added a floating image object too.

My published viz is here.

Happy vizzin’!

Donna

Can you make a hexbin map?

Donna ColesLeave a comment

Sean Miller was back this week to set this challenge to recreate a ‘rat sighting’ map using hexbins. I’ve only used hexbins in other #WOW challenges, so needed a bit of a refresher (the previous challenges pre-dated my own blog, so I couldn’t use myself as a reference). A quick google for ‘tableau hexbins’ and I found a variety of articles that provided the refresher needed.

Sean also used the opportunity to apply some other crucial skills – adding custom shapes and custom colour palettes, which I recommend is the first step you do in completing this challenge.

Adding custom shapes

Download the hexagon shape provided by Sean, and then save it into a folder in your …My Tableau Repository\Shapes directory. I have a folder called ‘Custom’ where I place random shapes I need. This post will help you out if you’re having difficulty with any of this.

Adding custom colour palette

Open a text editor such as Notepad, then open the preferences.tps file that is located in your .. My Tableau Repository directory. Copy & paste the block of code provided by Sean between the opening and closing <preferences> tag. Save the file and close the text editor. This post will help if you’re having trouble.

Building the map

The provided rat sighting data set contains a Longitude and Latitude value for every rat sighting since 2010.

Hexbins provide a way to group (bin) these Lat & Long values together. The size of the bin is typically determined by a parameter, so lets first set this up

pRatio

integer or float parameter which I set to a default value of 250 (Sean didn’t specify the value he’d used, but trial and error suggested to me this looked ‘about right’).

Now we can build the bins

HexbinX

(HEXBINX([Longitude]*[pRatio], [Latitude]*[pRatio])) / [pRatio]

Edit the geographic role of this field to be mapped to Longitude (right click on field -> Geographic Role).

HexbinY

(HEXBINY([Longitude]*[pRatio], [Latitude]*[pRatio])) / [pRatio]

Edit the geographic role of this field to be mapped to Latitude.

Add HexbinX to Columns and HexbinY to Rows. Modify each field so that it is a continuous dimension.

Change the mark type to Shape and select the hexagon shape you saved earlier.

Add the auto generated ‘count of dataset’ (Count of Rows) field to Colour, and adjust the colour to use the OrRd-5 palette you added earlier. Ensure to set the palette to Reversed.

Set the Tooltip so it doesn’t display anything on hover, and add Borough to the Detail shelf (this is needed for the interactivity later). Hide the nulls indicator that displays in the bottom left (right click -> hide).

On the Map menu, select Map Layers, and set the background style to dark.

Using the map controls, zoom in and pan to the left slightly, so the coloured area is mainly central and there’s less ‘sea’ at the bottom. It’s likely that you may need to adjust further once you’ve placed the map on the dashboard. But before publishing, we want to turn the map controls off, to prevent a user from shifting the display (Map -> Map Options -> uncheck all options).

Building the Bar Chart

On a new sheet, add Borough to Rows and Count of Rows to Columns. Add Borough to Filter and exclude Null and Unspecified. Sort the rows descending. Adjust the colour to suit.

Show mark labels, but only display the max & min values. Format the Count of Rows pill so the labels are displayed in K to 2 dp.

Hide the axes, right align the row label headings, adjust the tooltip. Hide the column heading. Remove zero lines and axis rulers. Set the background colour of the worksheet to ‘None’ (ie transparent).

Further formatting is required, but this is best done after the sheet is added to the dashboard, as you’ll lose visibility of the text at this point.

Creating the dashboard

Add the map to a dashboard, and remove all the additional containers/legends etc that are added, and the sheet title. Then add the bar as a floating object and position bottom left. Fit to entire view. Edit the title so it contains the ‘on hover’ instruction and format in light grey font.

Now you can format the row labels, the row headings and the gridlines to be appropriate colours – light grey rather than white.

Add a dashboard highlight action that on hover of the bar chart, highlights data in the Map

Then add floating text boxes and add the title and description. I ended up adding a text box just containing the OH, and then another position just below containing RATS! and then the description, as otherwise the carriage return between OH & RATS! made the spacing too wide. I used the controls on the layout tab to ensure both text boxes were positioned at the same x-coordinate.

Hopefully, you should now have a beautiful looking viz. My published version is here.

Side note – When I first started building this I tried from memory, and didn’t quite get things right. I then adjusted various fields as described above, but when I then tried to add the Count of Rows to Colour, I was only ever getting a value of 1 against each bin. I double & triple checked all the calcs and couldn’t see any issues. It was very weird. I simply ended up closing down my workbook and starting again from scratch and all was fine. I’m just letting you know this in case you too come across any oddities during your build, and things don’t behave as expected. It meant, what was ultimately quite a straight forward build (once I got the calcs right), ended up involving more time and head-scratching than really required 😦

Happy vizzin’!

Donna

Let’s analyse wildlife strikes

Donna ColesLeave a comment

Sean Miller began the start of #WOW2022 with this challenge where the focus was on layout, interactivity and maps.

  • Building the map
  • Building the bar
  • Building the area chart
  • The Unknown indicator
  • Adding interactivity

Building the map

I’m starting with this as in order to build the map, I found after a bit of trial and error I needed to build a data model which related the wildlife strike data source provided by Sean with the spatial file data source provided via the link to the community post. I downloaded all the files, but found the link to the us_ak_hi_territories_shift_conformal_faux_WM.hyper.zip was the file I needed once unzipped (the middle file to download from Sarah’s post on 19 July 2020 (see below)

Once I unzipped the downloaded file I copied the us_ak_hi_territories_shift_conformal_faux_WM.hyper file to my usual data sources repository on my laptop.

I then connected to Tableau and built a data model by first connecting to the wildlife strikes csv file, then adding a relationship to us_ak_hi_territories_shift_conformal_faux_WM on State Name = Name

To make things clearer, I created a fields to store the number of incidents

Wildlife Incidents

COUNT([2022_01_05_WW01_FAA Wildlife strikes (1990-2021).csv])

This is simply referencing the ‘count’ field that is automatically generated that is related to the wildlife strike data source.

I then built the map by

  • add Geometry and Name to Detail
  • add Wildlife Incidents to Colour

This should have created the below

Remove all the background imagery via the Map > Map Layers menu – uncheck all the options from the left hand pane.

Add Name to the Filter shelf and exclude American Samoa, Guam, Northen Marianas, Puerto Rico and the Virgin Islands. This will remove the cluster of shapes to the right (I’m not sure if this is the expected method or not..).

Change the colour palette to use the red-gold colour range.

Finally amend the Tooltip accordingly and also remove the row and column dividers.

Building the bar

The bar chart displays the top 10 incidents by species type, with the rest all grouped under ‘other’, and displayed at the bottom. We need to create a set for this. Right click on Species Type and Create > Set. Create a set based on the top 10 of the count of the wildlife incidents data source.

Species Type Set

We then need a field to display the info in the bar

Species Type to Display

IF [Species Type Set] THEN [Species Type] ELSE ‘Other’ END

ie if the Species Type is in the Species Type Set then display the Species Type, otherwise display Other.

Add Species Type Set and Species Type To Display to Rows and Wildlife Incidents to Columns and sort descending (just click the sort descending button in the toolbar)

Add Species Type Set to the Colour shelf and adjust accordingly. Remove the column and row dividers and the row gridlines. Adjust the Tooltip.

The final step we need is to make the title dynamic and display a state name if filtered.

Firstly we will need a parameter to capture the selected state

pSelectedState

A string parameter defaulted to <empty string>

We will use a parameter action to populate this parameter later. We need an additional field to use in the chart title

Title: Selected State

IF [pSelectedState] <> ” THEN ‘in ‘ + [pSelectedState] ELSE ” END

Add this field onto the Detail shelf, then adjust the chart title

Building the area chart

Add Incident Year to Columns and Wildlife Incidents to Rows and change to mark type = Area. Adjust colour accordingly, and remove the column gridlines. Adjust the Tooltip.

Again the chart title needs to be dynamic based on state name and the species type selected, so we’ll need another parameter

pSelectedSpecies

string parameter defaulted to <empty string>

Add Title: Selected State to Detail and adjust the title as below

The Unknown indicator

On a new sheet double click in the space below the marks card to create a ‘type in’ pill

Enter the text ‘Unknown Location’ (including the single quotes) and the add this pill onto the Text shelf.

Change the mark type to square and adjust the Size to the maximum.

Add Wildlife Incidents to the Tooltip shelf. Then add Name to the Filter shelf and filter to only show the Null values. Adjust the colour and the Tooltip.

Adding interactivity

Create a dashboard and use layout containers to position the charts in the relevant places. The colour legend and the ‘unknown’ indicator will need to be ‘floated’ into position.

We’re going to need 4 dashboard actions; one to set the selected State, one to set the selected Species Type, one to filter the bar and area chart based on the the selected state, and one to filter the map and area chart based on the selected species.

Select Species

this is a parameter action to set the pSelectedSpecies paramater on selection of the bar chart, using the value in the Species Type To Display. The parameter should be reset to <empty string> when unclicked.

Select State

similar to above, but runs on selection of the map chart, and passes the Name field into the pSelectedState parameter.

Filter by State

This is a filter action that runs on selection of a state in the map chart. It affects all other charts on the dashboard except the unknown sheet. It should only filter on the Name field and not All fields.

Filter by Species

Another filter action, that runs on selection of the bar chart. This one does impact all the other charts on the dashboard, but again only filters based on the Species Type to Display field rather than all fields.

Hopefully, with all this, you should have a working solution. My published viz is here.

Happy vizzin’!

Donna

Can you recreate the work of Hans Rosling?

Donna Coles4 Comments
This week, Lorna set the challenge to recreate an iconic data viz by the legend, Hans Rosling. You ABSOLUTELY MUST watch the TED talk link from the challenge if you haven’t ever seen this before. Hans is utterly engaging in making this data ‘come to life’!

The challenge involves the following key components

  • modelling the data
  • building the scatter chart
  • displaying the year in the chart
  • highlighting the regions

Modelling the Data

The data provided consisted of 4 files

  1. Life Expectancy
  2. Population
  3. Income
  4. Region Mapping

The first 3 files are stored as a matrix of Country (rows) by Year (columns) with the appropriate measure in the intersection. This isn’t the ideal format for Tableau so the data needs to pivoted to show 3 columns – Country, Year and the relevant measure (life expectancy, population or income depending on which file is referenced).

In Tableau Desktop, connect to the Life Expectancy file. If you find the column names all seem to nave generic labels, then right click on the ‘object’ in the data source pane, and select Field names are in the first row.

This will change so all the years are now listed as column headings

Now we need to pivot this data; click on the 1800 column, then scroll across to the end, press shift and click on the final column to select all the year columns. While highlighted, right-click and select Pivot.

Your data will be reshaped into 3 columns

I then renamed each column as

  • Country
  • Year
  • Life Expectancy

I also chose to change the datatype of the Year column to be a number (whole), as I knew I’d be needing to relate the data on this field later, and working with numeric data is more efficient than strings.

Now I want to bring in the next file – Population (via the Add connection option). But before following these steps, just read on a bit, as I hit a hurdle, which at the point of writing I’m still a bit unclear about…..

I added the Population file into the data source pane. By default it showed the Edit Relationship dialog, but I had the same issues with the column headings.

So I closed the dialog, and fixed the headings by setting the Field names are the first row option. Trying to apply the pivot at this point wasn’t available – no Pivot option when I selected all the columns. So I then manually edited the relationship and set County = country (population)

This populated the data, but again when I tried to apply the pivot option, it wouldn’t display 😦

I’m not sure whether this is possible (Google hasn’t shown me that it isn’t, but it hasn’t shown me that it is either…). I’ve tried using csv and excel versions of the file…..

So until I see the official solution (or I find out otherwise), I carried out the following approach instead :

I removed Population object, so I was just left with the Life Expectancy object. I then went to sheet 1, right clicked on the data source and selected Extract Data -> Extract and when prompted saved the file as Life Expectancy.hyper.

I then started a new instance of Desktop, and connected to the Population file, and went through the steps outlined above – set the Field names are first row, selected all the years to pivot, renamed the fields, set the datatype of the Year field to a whole number, then extracted and saved as Population.hyper.

I then repeated the exact same process with the Income file.

Once I had the 3 hyper files created, I then instantiated another instance of Desktop, and connected to the Life Expectancy.hyper file.

This automatically added an object called Extract into the pane. I renamed this (via right click) to DS-Life Expectancy. I also found my field names hadn’t been retained, so I renamed these too.

I then added the Population.hyper file. I set the relationships as below (once again I’d lost field names 😦 )

I then renamed the object & columns – note it won’t let you add fields with the same name as in the other object in the data pane, so I suffixed the columns

The process was once again repeated to add the Income data

and finally the Region data was also added matching Country to Name

And now, after all that, I have the data in the format I need to build the scatter. Phew!

[Side Note] – whilst writing this blog, I have been messaging my fellow #WOW participant Rosario Gauna. We often check in with each other, if we’ve noticed something odd with the challenges, to sense check whether its an issue with the data/the requirement, or just our own personal interpretation/development. In this instance we confirmed we were both using v2020.4.2, BUT Rosario had been able to do exactly what I set out to do at the start of this blog!! She had a pivot option on her 2nd connection. We’ve tried to figure it out, but have been unable to… I am using Windows, but Rosario is on a Mac, so there might be something there….. It will be interesting to understand if anyone else encountered my problem…. I really didn’t think the modelling part of the challenge was intended to be as fiddly as I’ve described above.

Building the Scatter Chart

The data we have goes beyond 2021, so we need to filter. Add Year to the Filter shelf and set the condition as below

We also have some countries that aren’t mapped to a region (Eight Regions field), so all add this field to the filter shelf and set to exclude Nulls.

Set both these filters to Apply to All Using this Datasource as they’re relevant across all sheets that will be built.

Now build the scatter by

  • Add Year to the Pages shelf
  • Add Income to Columns and change to Dimension (rather than Sum) – still leave to be Continuous
  • Add Life Expectancy to Rows and change to Dimension (rather than Sum) – still leave to be Continuous
  • Add Country to Detail

If you change the Year on the pages control to 2021 you should get something like

It’s roughly the right shape, but not spread as expected.

If you examine the Income axis on the solution, you’ll see the scale isn’t uniform. This is because it’s using a logarithmic scale instead, which you can set by right-clicking on the Income axis -> Edit Axis and selecting the relevant checkbox.

Now we can add Population to Size, change the mark type to circle, and we also want to colour by the region, but we need a new field for this to get it in the format we need for tooltips etc:

Region

UPPER(REPLACE([Eight Regions],’_’,’ ‘))

Add this to Colour and adjust accordingly, reducing the transparency a bit. Format the tooltips and you should have the main chart

Displaying the Year in the Chart

On a new sheet build a very simple view that just shows the Year in the Text shelf, that has been formatted to a very large (72pt) point, centred in the middle, and a pale grey colour.

On the dashboard, add this Year sheet. Then when you add the Scatter sheet, add it as a Floating object and position directly on top of the Year sheet. The Year sheet won’t be visible, so navigate to your Scatter sheet, right click on the canvas and format. Set the background of the worksheet to None ie transparent

If you return to the dashboard, the Year sheet should now be visible.

Note, as you add more objects to your dashboard, you will have to move things around a bit to get the layout as desired.

Highlighting the Regions

Create a simple table that displays the Regions in a row as below (the Show Header on the Region pill on the Columns shelf is unchecked).

Add this to the dashboard, then add a Highlight Dashboard Action that sources from the Region view and affects/targets the Scatter chart.

The final comment to make is that you can’t remove the ‘speed control’ from the page control display. However, when published to Tableau Public, it doesn’t show, so I assume is not enabled (or broken) there.

Hopefully this now gives you what’s needed to rebuild this fabulous viz. My published version is here.

Please do let me know if you had issues with your data modelling like I did!

Happy vizzin’! Stay Safe!

Donna