Tag: Week 9

Binary Parameters

Donna ColesLeave a comment

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

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

Setting up the parameter

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

Binary Parameter

string parameter containing the string 01000101000010000

Building the Sales by Subcategory viz

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

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

Sub Cat Rank

RANK_UNIQUE(SUM(Sales), ‘desc’)

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

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

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

Bucket

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

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

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

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

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

Value for Param

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

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

Add this to the Detail shelf.

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

Adding the selection interactivity

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

Set Bucket

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

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

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

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

Building the Sales by Bucket bar chart

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

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

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

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

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

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

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

My version of this challenge is published here.

Bonus Challenge

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

For this I created a parameter

# of Buckets

integer parameter from 1 to 5

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

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

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

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

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

My published version of this viz is here.

Happy vizzin’!

Donna

Can you create these KPI Cards?

Donna ColesLeave a comment

Lorna set a table calculation filled challenge this week to recreate some KPI cards using an aggregated and amended version of the Superstore data set.

Lorna purposefully used a string Period field to define the timeframe to encourage the use of table calculations. No doubt, there are ways you could use string functions/regex to extract the relevant year and month number to come up with a different solution, but I’m going to head down the intended route.

And as with any table calculation based challenge, I find it best to always build out all the calculations I need into a tabular display to start with before building the viz. So let’s get started…

Building out the Calculations

We need to have a handle on more of the data than just that associated to the Period we’re interested in, so we can’t use a simple Quick Filter on the Period field to restrict the data, otherwise we can’t ‘access’ data for the previous period. So to manage which Period we want to focus on, I created a parameter

pSelectedPeriod

string field that uses a list where the values are added from the Period field. Default to P12 Y2022/23

On a new sheet, add Period to Rows and then add the Sales , Profit and Quantity measures to Text. Show the pSelectedPeriod parameter

What we’re going to do is get the values for each measure that is associated to the pSelectedPeriod, and display that value over every row.

Curr Sales

WINDOW_MAX(SUM(IF [Period] = [pSelectedPeriod] THEN [Sales] END))

If the Period matches that in the parameter, then get the Sales value and then use Window_MAX to ‘spread’ that value over every row

Add this to the table, and edit the table calculation to ensure the value is being computed by Period.

Repeat the same for Profit and Quantity

Curr Profit

WINDOW_MAX(SUM(IF [Period] = [pSelectedPeriod] THEN [Profit] END))

Curr Qty

WINDOW_MAX(SUM(IF [Period] = [pSelectedPeriod] THEN [Quantity] END))

If you change the parameter, you should see all the values in the last 3 columns changing to reflect the value from the first 3 columns of the relevant row.

Now we need to get the value from the previous period, ie the data from the previous row

Prev Sales

WINDOW_MAX(IF MIN([Period]) = [pSelectedPeriod] THEN LOOKUP(SUM([Sales]),-1) END)

The LOOKUP function is taking the Sales value from the previous 1 row (-1), and then WINDOW_MAX is once again ‘spreading’ this value across every row. We also need

Prev Profit

WINDOW_MAX(IF MIN([Period]) = [pSelectedPeriod] THEN LOOKUP(SUM([Profit]),-1) END)

Prev Qty

WINDOW_MAX(IF MIN([Period]) = [pSelectedPeriod] THEN LOOKUP(SUM([Quantity]),-1) END)

Add these to the table, and again adjust the table calc settings for every field to compute by Period.

Now we have the current and previous values for each measure, we can work out the % difference

Diff Sales %

([Curr Sales]-[Prev Sales])/[Prev Sales]

Format this to a % with 1 dp

Diff Profit %

([Curr Profit]-[Prev Profit])/[Prev Profit]

Diff Qty %

([Curr Qty]-[Prev Qty])/[Prev Qty]

Add all these onto the sheet, and remember the table calc settings (for these, there are nested table calcs, so make sure both are set properly).

We now need an arrow indicator to display up or down depending on the % value, and this needs to display as a different colour, so we need two fields per measure.

Diff Sales Indicator +ve

IF [Diff Sales %] >= 0 THEN ‘▲’ END

Diff Sales Indicator -ve

IF [Diff Sales %] < 0 THEN ‘▼’ END

I use this site to copy the text symbols from

Diff Profit Indicator +ve

IF [Diff Profit %] >= 0 THEN ‘▲’ END

Diff Profit Indicator -ve

IF [Diff Profit %] < 0 THEN ‘▼’ END

Diff Qty Indicator +ve

IF [Diff Qty %] >= 0 THEN ‘▲’ END

Diff Qty Indicator -ve

IF [Diff Qty %] < 0 THEN ‘▼’ END

Add these to the sheet if you wish too (apply the table calc settings), but you’ll only get a value for one or the other field depending on whether the difference was +ve or -ve. Below I’ve just added the two Sales indicators

The Tooltip displays the value of the two Periods being compared. One of these is in the parameter, but we need to capture the other

Previous Period

WINDOW_MAX(IF [pSelectedPeriod] = MIN([Period]) THEN LOOKUP(MIN([Period]),-1) END)

So now we have all the values we need for the KPIs captured against every row in the dataset. So now we want to just show a single row. It could be the first, it could be the last… based on Lorna’s hint, let’s filter to just show the row related to the pSelectedPeriod value.

Filter Selected Period

[pSelectedPeriod] = LOOKUP(MIN([Period]),0)

Using the offset of 0 with the LOOKUP, returns the value for the row you’re on, so adding this to the FIlter shelf and selecting True, filters the display to the row where the Period matches the parameter. NOTE if you adjust the table calc settings of this field after adding to the filter shelf, you’ll need to reselect the option to filter to True.

As this is a table calculation, the ‘filter’ is applied later in the order of operations, so information about the other rows in the table can be referenced. Filtering just by Period as a quick filter, is essentially a dimension filter and that happens earlier on in the process, meaning the data about the other rows would be inaccessible.

So we have all the fields, now build the cards.

Building the KPI Cards

On a new sheet, double click into Columns and type in MIN(0). Repeat this 2 more times. This gives us 3 axis to build each of the 3 cards.

On the All marks card, add Period to the Detail shelf. Show the pSelectedPeriod parameter. Add the Filter Selected Period to the Filter shelf and set to true (adjusting the table calc and resetting the filter value as required).

Change the mark type of the All marks card to shape and select a transparent shape (see here for more details).

On the first MIN(0) marks card, add Curr Sales, Prev Sales, Diff Sales %, Diff Sales Indicator +ve and Diff Sales Indicator -ve to the Label shelf. Remember to apply the table calc settings for all of the fields!

Adjust the text within the label, so it is formatted and positioned as required and then align middle centre.

On the middle MIN(0) marks card, do similar by adding the equivalent profit fields onto the Label shelf, and then repeat again for the bottom MIN(0) marks card, adding the quantity fields to the Label shelf.

On the All marks card, add Previous Period, Curr Sales, Curr Profit, Curr Qty Prev Sales, Prev Profit, Prov Qty, Diff Sales %, Diff Profit % and Diff Qty % to the Tooltip shelf (remember to set those table calc settings!) Then adjust the Tooltip to display the text as required. I used the ruler to shift the starting position, along with tabs (the tab keyboard button) to ‘try’ to get everything to align, and it works for most circumstances….

Finally format the KPI card

  • Set the worksheet background colour to light grey
  • Remove all gridlines, zero line, axis lines
  • Set the column divider to be a thick white line
  • Set the row divider to be a thick white line
  • Hide the axis

Add the sheet onto a dashboard, and you should be done.

My published viz is here.

Happy vizzin’!

Donna

A tricky filter

Donna Coles5 Comments

In this week’s challenge, Erica set us the task of building a filter that only contained a subset of the dimension values – ie a set of core values always had to remain in the view, and weren’t available to be filtered out.

Erica advised there were hints available, and that she had solved the problem herself via an existing Tableau Knowledge Base article.

The requirements stated Sets were involved and so I attempted down this path, creating a set to store the ‘core’ cities (as per the requirements), and then using a combined set of all cities and ‘core’ cities to just display the values not in ‘core’. However I couldn’t get things working, so I checked out the hints.

The first hint alluded to 2 sheets, which initially I thought one for the ‘core’ cities and one for the rest, but quickly realised this would only work if there hadn’t been the additional ‘bonus’ requirement to sort the data based on the sales (ie the core cities and the rest could become interspersed in the viz).

So after further fiddling, and unsuccessful ideas, I ended up referencing the KB article and built out a solution. After publishing, my good friend and fellow #WOW participant Rosario Gauna, published her solution which she managed in a single sheet, and in a manner that was much more elegant. So it’s a double solution guide today – what I did based on the KB article and a recreation of Rosario’s solution (so I have this to reference and remind myself if ever I have the need to recreate).

Solution 1 – The 2 sheet solution

Firstly, create a set called Key Cities (right click on City > Create > Set) and select the 5 cities listed in the requirements.

Key Cities

On a new sheet, add State/Province to the Filter shelf and choose Ohio, then add City to Rows and Sales to Columns and sort descending. Add Key Cities to Colour and adjust accordingly.

Call this sheet Sales by City

On a new sheet, add State/Province to Filter and select Ohio, then add City to Rows. Call this sheet Filter Sheet.

Duplicate the City field (right click field in the data pane and select Duplicate). This will create a new field called City (copy) in the data pane.

Add City (copy) to the Filter shelf of the Filter Sheet sheet, select the 5 core cities and then check the Exclude checkbox.

Add the original City field to the Filter shelf as well and select All. Show the filter on the sheet, and adjust so it displays Only Relevant Values

The list of options in the filter list should only show the cities in Ohio that aren’t one of the five key states.

Set the City filter to Apply to Worksheets > Selected worksheets and select the Sales by City worksheet

Customise the City filter in the Filter Sheet sheet so that the All option does not display. From the context menu of the City filter control, select Customise and ensure Show ‘All’ Value is unchecked.

Navigate back to the Sales by City sheet and show the City filter values, ensuring all are displayed. This list will include the key cities, but don’t worry. Uncheck a value that isn’t in the list of key cities eg Bowling Green. The city will disappear from the viz, but if you navigate to the Filter Sheet, you should also see the value is unselected in that list too.

This is the filtering behaviour we’re after – selections made to the City filter on the Filter Sheet affect the values in the City filter on the Sales by City sheet.

Now we need to address the sorting.

Again I think I ended up doing something a bit more complicated than needed – check out the sorting described in the 2nd solution, as that would apply here too – it just isn’t what I did at the time.

Firstly, we need a parameter to determine which sort selection to use

pSort

string parameter containing two list entries Key Cities and Sales, defaulted to Key Cities

I decided I wanted to sort by a number, which for Sales was fine, but when Key Cities was selected, I needed to ensure the values for the Key Cities were always greater than the maximum value for the non key cities. For this I needed to get a handle on the value of sales for the non key city that had the largest sales.

Max Non Key Sales

{FIXED : MAX(IF NOT [Key Cities] THEN ({FIXED [State/Province], [City] : SUM ([Sales])})END)}

If the city is not a key city, then get the total sales for each State & City (potential that a city can exist in multiple states, hence the need to declare the State), and then return the max of those.

To see what this is doing, on a new sheet, add State/Province to Filter and select Ohio, then add Key Cities and City to Rows and Sales to Text and sort by Sales descending

We’re looking for the value 8203 as this is the largest sales for the cities not tagged as a Key City.

Add Max Sales Non Key City to the view…. the value doesn’t match what we expected.

This is because a FIXED level of detail (LOD) calculation works across the entire data set, so the fact we’ve filtered by Ohio is being disregarded. To resolve this, set the State/Province field on the Filter shelf so it is Add To Context

This pill will change to grey, and the values should update, as now the LOD is being applied after the context filter has been applied.

With this we can work out a sort field

Sort

CASE [pSort]
WHEN ‘Sales’ THEN SUM([Sales]) * -1
ELSE
(IF ATTR([Key Cities]) THEN SUM([Sales]) + SUM([Max Sales Non Key]) ELSE SUM([Sales]) END) * -1

END

If we’re sorting by Sales then use the total Sales value * -1, otherwise, if we’re sorting by Key Cities then, if the City is a key city, then add the total sales to the max sales value, otherwise just use the total sales value. Multiple the result by -1. By adding this value, it ensures the values for the Key Cities are always larger than those for the non key cities. The -1 means the sort will be descending.

Test this out, by adding the Sort field as a discrete (blue) field to the Rows of the test sheet we’ve been using above. Ensure the Sort field is listed first, and move the Key Cities field to be third. Show the parameter control, and test switching between the options. The values in the Sort field are always in an ascending order, but the displayed Sales values will be ordered depending on the sort option chosen

Back on the Sales by City sheet, add the Sort pill to the Rows before the City pill, and add the State/Province filter to context.

Hide the Sort field.

The labels need to be displayed inside the bars, so for this we need a dual axis.

Add another copy of Sales to the Columns. On the second Sales marks card, set the option to Show mark labels from the Label shelf. We need the text of the label to be different to the existing bars, so create a duplicate of the Key Cities field, so we have Key Cities (copy) and add this to the Colour shelf of the second marks card. Adjust the colours accordingly to white and black.

Change the mark type of the 2nd marks card to Gantt bar, reduce the opacity of the Colour to 0% and reduce the Size to as small as possible. Adjust the alignment of the Label to left middle, and set the font to be bold and match mark colour.

Make the chart dual axis and synchronise the axis. Set the mark type of the first marks card back to a bar if it changes.

Remove all row and column dividers, and hide the top axis. Hide the City column label too. Edit the bottom axis, and fix to start from 0 and end automatic. Adjust the tick marks to display every 5000 values.

Add this sheet to a dashboard. Remove the colour legends that automatically get added and remove the City filter control too. Leave the sort parameter.

Then add the Filter Sheet as a Floating object and position bottom right. The City filter for this sheet should also automatically display. If it doesn’t show it (click the context menu of the Filter Sheet object > Filters > City).

Change the City filter to be a multiple values dropdown control and set it to be fixed (unselect the Floating option on the context menu).

Now hide the title on the Filter Sheet object and resize to make it teeny tiny, so you can’t see anything

Now you have the core objects needed for a functional dashboard – you’ll just want to take some time moving them into place, and excluding other Cities.

My workbook that matches this solution is here.

Solution 2 – the 1 sheet solution

So shout out again to Rosario Gauna, as this is actually her solution!

We’ll build this out in a table first, so we can see what’s going on.

On a new sheet add State/Province to Filter and select Ohio, then add City and Key Cities (the set created above) to Rows. Add Sales to Text.

What we’re going to do is create another set which will just contain the cities not identified as key cities. For this, we need to store against every row (including the key cities) the name of a City that isn’t a key city. For those that already aren’t a key city, that is just its own City name, but for those that are key cities, we want to store a non key city…. sounds confusing right…. let’s build this up.

Firstly, let’s just get those non key cities

Non Key City

IF NOT [Key Cities] THEN [City] END

Add this to the sheet. It shows NULL against all the Key Cities and the City value for all the others

We’re going to use this to set a ‘default; value against the key cities.

Min Non Key City

{FIXED: MIN([Non Key City ])}

Theis returns the value from the Non Key City field which is alphabetically first. Using MAX would work just as well. When we add this to the sheet, we also need to set the State/Province filter to be Add to Context, otherwise we get a City from the whole data set, and not just Ohio.

We can now create a field that will just contain a distinct list of the non key cities

Other Cities

IF NOT [Key Cities] THEN [City] ELSE [Min Non Key City] END

Add this to the sheet

For every City, the Other Cities field contains a non key city value. Now we have this, we can create a set from this field

Other Cities Set

Ensure all values are selected – don’t worry that you can see cities that aren’t relevant at this stage

Add Other Cities Set to the Filter shelf and also add to the Rows shelf next to the Key Cities field. From the context menu of the Other Cities Set on the Filter shelf, select Show Set. The list of non key cities should be displayed. If there’s more than you expect, ensure the control is set to All Values in Context.

If you uncheck Bowling Green from the list, all the key cities and Bowling Green will disappear, but we don’t want this. We only want the row where City=Bowling Green to disappear. For this we need

Records to Keep

[Key Cities] OR [Other Cities Set]

Add this to the Filter shelf and set to True. Remove the Other Cities Set from the Filter shelf (the list should remain). Now if you remove Bowling Green, only that row should disappear, and if you uncheck all, so no city is selected, the key cities should remain

Adjust the display so the rows are sorted by Sales descending (either use the Sort button in the toolbar, or set the sort on the City field).

Then create a field

Sort Order

CASE [pSort]
WHEN ‘Key Cities’ THEN [Key Cities]
ELSE TRUE
END

Add this to the Rows in front of City, and show the pSort parameter (see above for details if you haven’t created this yet). When pSort is set to Key Cities, manually change the order of the values displayed so True is listed before False. The rows are displayed in descending Sales value for each Sort Order Value. When Sales is selected to sort by, the Sort Order is true for all rows.

Now we’ve got all the components needed to build the viz, and you should be able to adapt the steps above to get it to work. The key difference is using the Records To Keep field on the Filter shelf, displaying the members of the Other Cities Set to control the filtering, and managing the sort using the Sort Order field instead.

My workbook showing this solution is published here.

Happy vizzin’!

Donna

How many patients were admitted every 15 minutes?

Donna ColesLeave a comment

This week, Luke set the challenge which is focussed on manipulating time. Medical admissions over many days is represented in a bar chart which spans a 24 hour period. All admissions needs to be ‘bucketed’ into 15 minute intervals over the 24 hours ie admissions between midnight and 12:14am is counted within the same 12:00am ‘bucket’.

The data for this challenge is embedded within a workbook which you need to download via the challenge page. I did as Luke instructed; downloaded the workbook, deleted all the existing sheets, then re-saved as my own file.

The first step that is required is to ‘baseline’ / normalise the admission dates so they all look to be on the same day.

There’s different ways to do this; on this occasion I used the 3rd method from this Tableau KB, although I simply hardcoded a date of 1 Jan 2023 rather than use TODAY(). It doesn’t matter what this particular date is, its just an arbitrary date.

Baseline Admission Date

DATEADD(‘day’, DATEDIFF(‘day’, [Admission Date], #2023-01-01#), [Admission Date])

Once we’ve got this, we then need to manipulate this date again to ‘group’ into the 15 min interval. This isn’t something I know ‘just to do’, but I know I’ve done it before. So a quick google was needed and I used this blog for the required calculation.

Baseline Admission Date 15 mins

DATETIME((INT(FLOAT([Baseline Admission Date])*96))/96)

Pop these fields out into a table to see how these calculated fields are working

The Baseline Admission Date 15 mins is what we’ll use for the x-axis. The next step is work out the value being plotted Stays per Day.

Now when the challenge was first placed, a couple of the requirements were missing, so there was a bit of head-scratching trying to figure out what numbers were being used to get the values presented.

The following fields need to be added to the Filter shelf:

  • Stay Type = Outpatient
  • Admission Date starting from 30 Aug 2017 00:00

When on the filters shelf, both these should then be added to Context, as the data needs to be filtered before the LOD calc we need to use gets calculated (defined below).

Count Days with 15 min interval

{FIXED [Baseline Admission Date 15 mins]: COUNTD(DATETRUNC(‘day’, [Admission Date]))}

This is counting the distinct days when there was admission within each 15 minute period ie if there were 2 admissions on the same day within the same 15 minute window, the day would only count as 1.

From this we can then compute

Stays per Day

SUM([Number of Records])/SUM([Count Days with 15 min interval])

Now we’ve got the data we need, so we can build the viz.

Add Baseline Admission Date 15 mins to Columns as a green continuous pill, and Stays per Day to Rows. Don’t forget to add Admissions Date and Stay Type to the Filter shelf as mentioned above.

The bars look ‘blocky’. You can manually adjust the size, but you might notice that the widths between isn’t exact – the whitespace looks larger between some bars than others. To resolve this, I created a field to control the size, which is based on the number of 15 minute intervals there are in a 24 hour period – 96.

Size

1/96

Add this to the Size shelf, change the aggregation to MIN, and adjust the size to be Fixed and aligned Centre.

Add another instance of Stays per Day to the Rows shelf. Then make it dual axis and synchronise axis. Change the mark type of the 2nd Stays per Day instance to Gantt and change the colour. Then change the colour of the 1st Stays per Day.

Show mark labels, and set the Label just to show the max value.

Right click on the time axis, and format, and custom format to h:nn am/pm

And essentially, that’s it. There’s formatting to do to remove the secondary axis, column & row banding etc and add tooltips, but the core of the viz is complete.

My published instance is here. Note the time formatting seems to be an issue on Tableau Public. Someone did comment that this was an issue with the MAKETIME function, but I didn’t actually use this function.

Happy vizzin’! Stay Safe!

Donna

Can you hide a chart in map layers?

Donna ColesLeave a comment

Candra set the challenge this week to use the new map layers to build a map display which, on click of a country, filtered the display to that country and additionally displayed a donut chart indicating the percentage of urban dwellers in that country.

If map layers are very new to you, then the webinar by Adam McCann referenced in the challenge, has some VERY useful pointers for this challenge (the workbook for that can be downloaded from here, as I found some things needed closer inspection).

I think this challenge is going to be best described by walking through the steps.

Building the 1st map layer

Double-Click on Country/Region to load a map, and change the mark type to (filled) Map. Add Region to the Colour shelf and assign the appropriate colours. Adjust the map background via the Map -> Map Layers menu and set the Style to dark, and remove all the selections against the Map Layers list

The intention is when a country is selected the map will ‘drill into’ /filter that country, and display additional information. We will drive this by a parameter, which will get set via a parameter action, but for now we’ll manually set the value.

Create a new string parameter, that is default to nothing/empty string/ ”, and then show this on your sheet.

pSelectedCountry

We need the 1st layer of the map to display, when there is no value in the parameter. We need a calculated field to help drive this.

All Countries

IF [pSelectedCountry]=” THEN [Country/Region] END

By default this will create a field of type ‘string’ but we need it to be a geographic data type. so change this as below.

Add this field to the Detail shelf of the map, and remove the Country/Region field that was automatically added when we first built the map.

Enter the name of a country, eg China, into the parameter. The map should essentially go blank (black screen).

Building the 2nd map layer

We need the country to display, if it’s entered into the parameter. For this, we need another calculated field

Selected Country

IF [pSelectedCountry]=[Country/Region] THEN [Country/Region] END

Once again change this to a geographic role data type of type County/Region.

Next click and drag this field onto the map, and drop it onto the Add Marks Layer option that displays. This will create a new Selected Country marks card, although nothing will obviously change on the map display itself.

Move the Selected Country pill to be on the Detail shelf instead, and add Region onto the Colour shelf. Change the mark type from circle to Map.

Now if you enter a county into the parameter, eg China, the display should ‘filter’ & ‘zoom in’ on China.

So what we have is the 1st layer only showing when no countries have been selected, and vice versa, the 2nd layer only showing when a country has been selected.

We need to now add further layers for the donut chart, which only want to show the country has been selected as well.

Building the 3rd map layer

A donut chart, in the past, is traditionally created by building a pie chart, then using a dual axis to add a circle, sized smaller that the pie chart, on top (see this Tableau KB for info). Rather than a dual axis, we’re going to use map layers – 1 layer for the pie chart, and then another layer for the central circle.

Keeping a country selected (so we can see what we’re building), drag Selected Country onto the map again to create another map layer. Change the mark type to Pie and increase the Size to as large as possible. Move Selected Country to Detail.

In the data set we have a field called Population Urban which stores the ‘percentage’ value of urban dwellers eg 0.17 is 17%. To create the angles for the pie chart, we need to know

Population Non-Urban

1-[Population Urban]

Drag Measure Values onto the Angle shelf. This will automatically add Measure Names to the Filter shelf. Edit the filter to just select the Population Urban and Population Non-Urban measures. Drag the Measure Names field that was also automatically added to the Detail shelf, to the Colour shelf. Adjust colours accordingly, and set the border of the pie chart to white (under the Colour shelf options).

Verify that if you set the parameter to empty again, the whole world map displays, and you can’t see any pie charts.

Building the 4th map layer

Now we need to make the donut hole. Once again, ensure a country is selected, so your pie chart is visible, then drag Selected Country onto the map again, and drop to add another map layer.

This time, move the Selected Country field onto the Detail shelf, add Region to the Colour shelf, and adjust the size of the circle, so its smaller than the pie. Set the border of the circle to be white again too,

Add Population Urban onto the Label shelf, and format to a percentage with 0 dp. The best way to do this, is to format the Population Urban measure in the data pane (right click->default properties -> number format).

At this point you’ll notice the number is huge… we need to add Year to the Filter shelf, and select 2012.

Align the label to be middle centre, and adjust the font to be much bigger text. Add ‘urban dwellers’ underneath.

Once again, verify you get the expected behaviour as you change the values in the parameter from nothing to Russia or China etc.

The final step on this sheet is to add text to the Tooltips. Unlike when working with dual axis, you don’t have an All marks card, so you’ll need to add the required fields (Country/Region, Region, Population Total (formatted to Millions with 0dp), Population Urban to the Tooltip shelf on each of the relevant layers.

Setting the parameter interactively

Create a dashboard sheet, and add the map sheet you’ve built. Then create a dashboard action which sets the pSelectedCountry parameter, impacting the All Countries field, and that when the selection is cleared, the value is reset to ”.

My published viz is available here. Enjoy!

Happy vizzin’! Stay Safe!

Donna