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