Tag: Navigation

Excel at Tableau Interactivity with Dynamic Zone Visibility

Donna ColesLeave a comment

Erica set the #WOW2024 challenge this week, asking us to recreate this ‘drillable’ functionality within a single dashboard.

To achieve this, I used 4 sheets

  • A large map
  • A small map
  • A table of data
  • A navigational control

along with parameter actions to drive the interactivity, and dynamic zone visibility to control what displayed when.

Building the large map

On a new sheet, double click on State/Province to automatically generate a map of the United States – if this doesn’t display by default, then make sure the location of your map settings are referencing the USA (Map menu > Edit Locations). Hide the ‘unknown’ indicator displayed bottom right. Change the mark type to map to create a filled map. Change the colour to a grey (I used #c0c0c0). Add State/Province to the Filter shelf and select Arkansas. Show the filter.

Click on the Tooltip button, and delete all the text in the dialog.

Now drag City onto the map and drop it on the section that appears labelled Add a Marks Layer. On the ‘City’ marks card that now appears, change the mark type to circle. Move City to the Label shelf and then also add State/Province to Detail. Change the colour of the circles to dark grey (I used #767f8b).

Update the Tooltip on the City marks card. Turn off all the map options to stop the pan/zoom map control options from appearing (map menu -> map options -> deselect all the options). Remove all row/column dividers.

Name the sheet Map-Large or similar.

Building the Small Map

Duplicate the Map-Large sheet, and name the new sheet Map-Small. Remove all background layers so just the state outline remains (Map menu -> background layers -> set washout to 100%).

On the Label shelf of the City marks card, uncheck show mark layers so the name of the city is not displayed and click on the Tooltip shelf and uncheck show tooltips, so they aren’t displayed either.

On this map, we need to highlight a selected City. For this we need a parameter to capture the City, and a calculated field to identify which City has been selected. We will also need a parameter to capture the State/Province to ensure the interactivity works as required.

pSelectedState

string parameters defaulted to ” (ie empty string)

pSelectedCity

string parameter defaulted to ” (ie empty string)

Is Selected City

[City] = [pSelectedCity]

Add Is Selected City to the Colour shelf of the City marks card.

Show the pSelectedCity parameter and type in ‘Fayetteville’. The colour legend should display values for True & False. Adjust colours to suit.

Also add Is Selected City to the Size shelf and adjust the sizes accordingly.

Building the table

On a new sheet, add

  • State/Province to Filter and filter to ‘Arkansas’
  • Add Is Selected City to Filter and filter to True
  • Add Customer Name, Order ID and Product Name to Rows
  • Right-click on the State/Province field in the Filter shelf and apply to worksheets > selected worksheets and select the Large Map & Small Map sheets. This makes the filter shared between all those sheets.

Create a new field

Unit Price

SUM([Sales])/SUM([Quantity])

and add to Text.

Drag Quantity onto the canvas and drop on the ‘unit price’ column when you see show me appear. This will automatically add Measure Values to Columns and Measure Names to Filter.

Do the same for Sales. Reorder the pills in the Measure Values box so they are arranged as required.

Add subtotals (Analysis menu > Totals > Add all subtotals), then display at the top (Analysis menu > Totals > Column Totals to Top).

Right-click on the Customer Name pill in Rows and uncheck Subtotals from the context menu.

Format the table: set the worksheet background colour to light grey and set the total (pane & header) background colour to a shade darker.

Adjust the width and height of the columns and rows to suit.

Uncheck show tooltip from the Tooltip shelf, and name the sheet Table or similar.

Building the Navigation sheet

The ‘back to main map’ navigational display is actually a worksheet rather than a navigational object on a dashboard, as we need to apply parameter actions to it in order to reset some parameters.

On a new sheet, double click into the space on the marks card below the shelf buttons, and type ‘X’

Change the mark type to Text and move the X field to the Text shelf. Update the text to include the additional words, adjust the font size and then align middle centre. Uncheck Show tooltip.

Create the following fields

City – Reset

”

State – Reset

”

and add both to the Detail shelf. Name the sheet Navigation or similar.

Building the dashboard

Using horizontal and vertical layout containers, arrange all the objects on a dashboard. You will need to adjust the background colour of some objects and the padding to get everything looking as expected. This can be quite tricky and is very hard to explain in a blog. The item hierarchy in the image below will help give you an idea

To draw horizontal or vertical lines, add a blank object to the layout container, change the background colour, reduce the padding to 0 and then adjust the height or width of the object so it is very narrow.

These are the properties associated with the red ‘tab’ in the heading – the background colour is red, inner and outer padding is 0, width is 4px and the object is contained within a horizontal container

For the City/State title, reference the pSelectedState and pSelectedCity parameters, along with a unicode symbol which I copy and past from here.

Adding the interactivity

Add the following dashboard actions

Set State

on select of the Map-Large sheet, set the pSelectedState parameter passing in the value of the State/Province field. When the selection is cleared, retain that value.

Set City

on select of the Map-Large sheet, set the pSelectedCity parameter passing in the value of the City field. When the selection is cleared, retain that value.

Reset State

On select of the Navigation sheet, set the pSelectedState parameter passing in the value of the State – Reset field. When the selection is cleared, retain that value.

Reset City

On select of the Navigation sheet, set the pSelectedCity parameter passing in the value of the City- Reset field. When the selection is cleared, retain that value.

Click around on the dashboard and sense check the parameters appear to be behaving – ie if you click on a city on the large map sheet, the table and small map should update, and if you then click the navigation sheet, the table shouldn’t show anything, and the small map shouldn’t show anything highlighted.

Hide and show the objects

Finally, we need to only show the required objects depending on the actions taken. For this we need some additional calculated boolean fields. Go back to any sheet, and create the following calculated fields

City is Selected

[pSelectedCity]<>”

City Not Selected

[pSelectedCity]=”

Note, these fields just have to exist, they don’t need adding to any sheet.

Navigate back to the dashboard.

Click on the Map-Large object, and on the Layout tab, check the control visibility using value option, and choose the City Not Selected value.

This means that when City Not Selected is true (ie the pSelectedCity parameter is empty), this object will display. When City Not Selected is false (ie the pSelectedCity parameter has a value), this object will be hidden.

Depending on what you’ve been clicking, this object might disappear immediately.

Click on the Table object, and do the same steps, but this time choose the City is Selected field. This means the table will only show then the pSelectedCity parameter has a value.

Apply the same settings so the small map, the table title, and the navigation sheet only show when City is Selected. Note – depending on how you have built the dashboard, you may find you can apply the setting once against a container which contains all the objects you want to hide rather than against each individual object.

I also chose to only display the Select a State filter control when the City Not Selected was true.

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

Happy vizzin’!

Donna

Can you navigate a hierarchy?

Donna ColesLeave a comment

Sean Miller set this week’s #WOW2022 challenge based on a common requirement – how to allow users to navigate a hierarchy of data while capitalising on the the real estate available to display the data.

The charts required for this challenge are very simple, so I’m not going to spell out how to build these. I created 4 charts

  • Trend – Sales by Month line chart
  • by Category – Sales by Category horizontal bar chart
  • by Sub-Category – Sales by Sub-Category horizontal bar chart
  • by Product – Sales by Product Name horizontal bar chart

Now all the remaining functionality to drive the navigation through the hierarchy, how the charts are filtered at each level and whether the chart should display or not, will be driven by parameter actions. So for this we will need 3 parameters

pCategorySelected

string parameter defaulted to <nothing> (empty string)

We need similar parameters for pSubCategorySelected and also pProductSelected.

Controlling the sheet swap & filtering the charts

On a dashboard, add the Trend sheet, then below it add a vertical container.

Within the vertical container add the by Category sheet, the by Sub-Category sheet and the by Product sheet. Remove the title from all these sheets. Show the 3 parameters.

We’ll now set up some calculated fields to determine when each of the bar chart sheets should display or not.

Filter: Show Category

[pCategorySelected]=”

If this parameter is empty, we want to show the category bar chart. Add this field to the Filter shelf of the by Category sheet and set to True.

Type the word ‘Furniture’ into the pCategorySelected parameter box and press return. The by Category sheet should disappear from the dashboard.

We now do a similar calculation for the by Sub-Category sheet

Filter: Show Sub-Category

[Category]=[pCategorySelected] AND [pSubCategorySelected]=”

This field is filtering the bar chart based on the selected category

Add this to the Filter shelf of the by Sub-Category and set to True. The bars should now just display the sub-categories associated to the Furniture category.

Now type the word ‘Chairs’ into the pSubCategorySelected parameter box. The by Sub-Category sheet should also now disappear from the dashboard.

Finally we also need to now ensure the by Product sheet is filtered to the relevant Sub-Category.

Filter: Show Product

[Sub-Category]=[pSubCategorySelected]

Add this to the Filter shelf of the by Product sheet and set to True. Only products associated to Chairs should now be listed.

Now we’ve set all this up, we also need to ensure the Trend sheet is getting filtered based on all the selections being made.

Filter : Trend

([Category]=[pCategorySelected] OR [pCategorySelected]=”)
AND
([Sub-Category]=[pSubCategorySelected] OR [pSubCategorySelected]=”)
AND
([Product Name]=[pProductSelected] OR [pProductSelected]=”)

Add this to the Filter shelf of the Trend sheet and set to True.

Type in an appropriate value into the pProductSelected parameter box (eg Global Task Chair, Black) and see how the trend changes.

Setting the parameters

This will all be done with parameter actions – there’s a few 🙂

+ Drill down to show Subcategories within <Category>

Use the Insert link to add the <Category> field to the action title – this will then be set dynamically based on the bar being selected.

Set the action to apply to the by Category sheet only, and via the Menu option. It should impact the pCategorySelected parameter and retain it’s value when unselected. The Category field should be passed into the parameter.

Delete all the values from the parameter boxes, so they’re all empty. This should reset the dashboard so only the by Category sheet is displayed under the trend. Hover/click on a bar to show the tooltip and click on the link. The pCategorySelected parameter should be populated and the bar chart displayed now changes.

We’re going to create a similar parameter action for the drill down from by Sub-Category to the by Product sheet

+ Drill down to show Products within <Sub-Category>

This time the action applies to the by Sub-Category sheet on the Menu action, and sets the pSubCategorySelected parameter with the Sub-Category value, again retaining the value when cleared.

On this sheet, we also need an action to allow us to ‘drill up’. We need to set the pCategorySelected parameter back to nothing. For this we need an additional calculated field

Level Up : Category

”

Add this field to the Detail shelf on the by Sub-Category sheet.

The back on the dashboard, add a further parameter action

– Drill Up to show all Categories

The action runs on the Menu of the by Sub-Category sheet only, setting the pCategorySelected parameter with the value from the Level Up: Category field. Again the value should be retained when deselected.

Test the functions. The drill down should display the by Product sheet. Then manually delete the value in the pSubCategorySelected parameter, and test the drill up action.

We now need to deal with the actions from the by Product sheet

+ Filter dashboard to <Product Name>

This action runs on the Menu of the by Product sheet only and passes the Product Name field into the pProductSelected parameter. This time though, when the bae is unselected, the parameter should be cleared to ‘blank’.

Next we’ll add the drill up function back to the sub-categories.

Similarly we need to set the pSubCategorySelected parameter back to empty string, so we need

Level Up: Sub-Category

”

Add this to the Detail shelf of the by Product sheet. Also add the Category field to the Detail shelf.

+ Drill Up to show Subcategories within <Category>

The action applies to the Menu of the by Product sheet only, passing the Level Up : SubCategory field into the pSubCategorySelected parameter. The value should be retained when cleared. Note the Category field was required so it could be added to the menu action title.

Test the actions, and verify the behaviour of the parameter boxes as each selection is made.

The dynamic title

The title of the trend line keeps track of the options selected during the navigation. For some reason, I used a separate sheet, but that’s not needed and actually goes against the requirements on 4 sheets only. So I’ll describe how to dynamically set the title on the Trend sheet instead. We’ll need some additional fields

Title – Category

IF [pCategorySelected] <> ” THEN ‘for ‘ + [pCategorySelected] ELSE ” END

Title – Sub-Category

IF [pSubCategorySelected] <> ” THEN ‘-> ‘ + [pSubCategorySelected] ELSE ” END

Title – Product

IF [pProductSelected] <> ” THEN ‘-> ‘ + [pProductSelected] ELSE ” END

Add all these fields to the Detail shelf of the Trend sheet, then update the title

All of this should now mean the core requirements of the challenge have been met.

Bonus – Extending the tooltip width

The bonus step was to extend the width of the tooltip so no word-wrapping existed. I did this by creating a Viz in Tooltip.

On a separate sheet I added Category and Sales to the Text shelf and formatted so they were aligned as required.

This sheet was then referenced from the by Category tooltip where I then adjusted the width to 350 and the height to 75

I repeated this creating similar sheets for Sub-Category and Product Name.

You just now need to tidy up the dashboard – add a text box to act as a title for the bar charts section, format the titles to be grey and remove the parameters from the display. My published viz is here.

Happy vizzin’!

Donna

Drill Up and Down with Parameter Actions

Donna Coles1 Comment

The final guest #WorkoutWednesday challenge for August was set by zen master, ambassador and fellow #WorkoutWednesday enthusiast Rosairo Gauna.

The challenge is here and built on a challenge set earlier in the year by Luke Stanke which involved Set Actions. Rosario was challenging us to use Parameter Actions instead to navigate the hierarchy (they weren’t around when Luke set his challenge), as well as not duplicate the data set.

I have to say, this really stumped me. Huge kudos to Rosario for managing to figure out how to use Parameter Actions in this way.

I had to reference Rosario’s own blog post to get to the bottom of this, so I won’t be documenting this myself.

This is going to be one of those challenges that demonstrates a technique that if I ever want to use again, I’ll be referencing this post (well Rosario’s post via this post), rather than remembering all the steps/calculations… a bit like I do when I have a need to build a trellis chart or if I ever had to build a radial chart again – I have my ‘go to’ reminder posts.

My solution is here.

Happy vizzin!

Donna

Creating a Navigating KPI Block

Donna Coles2 Comments

You know you get those days when your mind just isn’t in the right place?  Well, that was me yesterday – even forgot to collect my daughter from her holiday club… I’d driven home before I remembered – eeek!  She was none the wiser though, as I wasn’t late – phew!

After reading through Ann Jackson’s challenge for Week 30 of #WorkoutWednesday2019 (here), I thought this should be pretty straightforward (Ann had even said so herself), so found it quite perplexing to then be sitting staring at my screen trying to figure out how to get 4 independent measures into a single view in the grid format displayed…..

So I closed my laptop lid, and walked away… well actually went and worked up a sweat in my weekly Zumba class.

After a good physical workout, shower and food, I set about readdressing the mental workout challenge, and promptly had that ‘doh!’ moment…

The focus of the challenge was to utilise some of the new features in Tableau to aid in navigation between dashboards.  Subsequently you’ll need Tableau v2018.3 to be able to complete this challenge.

The solution is built of some very simple views/worksheets, which are then placed on a number of dashboards.  The Go To Sheet dashboard action and Button dashboard object are then utilised to provide the navigation – these are the features introduced in v2018.3.

All relatively straightforward, and I have no idea why I had such a mental block….

My solution consists of 8 worksheets and 5 dashboards, and to build really ended up involving a lot of right-click -> duplicate worksheet. 

1. 4 Block KPI dashboard

This consists of 4 separate views, 1 for each measure. I built the first view (Customers) as follows:

a. Dragged Customer_ID to Text shelf, then changed the aggregation to Count (Distinct) by clicking on the white arrow (carrot) to the right of the pill.

b. Changed the mark type to Square, changed the Size to be as large as possible, and changed the ‘fit type’ to Entire View

c. Edited the text and formatted the size, set the font to ‘match mark colour’ and adjusted the alignment to middle centre

d. Coloured the box, using a recent tip I believe I got from Lorna Eden…

If you just select the Colour shelf with the view above, the colour palette below is displayed

Ann mentions in her blog that she uses the Hue Circle colour palette.  The easiest way to access this specific palette is to create a ‘dummy’ pill to put on the Colour shelf.  To do this, simply double click in the space on the marks shelf, below where the text measure is shown, and type the word ‘dummy’ (including quotes) into the area

This creates a new blue (discrete) pill, which you can then add to the Colour shelf, and this will then display the Colour Legend, which you can edit, find the Hue Circle colour palette and select the appropriate colour.

e. Finally edit the tooltips to uncheck the ‘show tooltip’ option.

f. Once done, this sheet can then be duplicated to create the other KPI blocks; you just then need to change the field on the text to be CNTD(Product Name), CNTD(Order ID) or CNTD(City State), where [City State] is a calculated field I created of UPPER([City] + ‘, ‘ + [State]).

g. NOTE – You do need to remove then recreate the ‘dummy’ field on each duplicated sheet though, as otherwise, if you change the colour, it will affect all the sheets.  Simply remove the field, then create a new one by typing directly in the same manner described above.  Whilst you may name this field the same ie ‘dummy’, the removal and recreation actually creates a new instance of the pill ‘under the bonnet’, which is therefore independent of the ‘dummy’ pill on the other sheets, so can be coloured differently.

h. Add these 4 views to a dashboard.

2. Bar chart dashboard

On clicking on each block on the 4 block KPI dashboard, a new dashboard is presented showing a sorted Sales by xxxx bar chart.  Let’s work through creating the Sales By Customer dashboard.

a. Ann likes her UPPERCASE, so first create a calculated field to store UPPER([Customer Name]), and add this to rows and SUM(Sales) to columns and sort.

b. Add both fields to the Label shelf, edit the font sizes to suit, match mark colour and align left middle. Untick ‘show tooltips’ from the Tooltip shelf.

c. Apply formatting to the chart to remove gridlines, columns, rows etc, but keep the axis rulers on rows – make the line thicker/darker to suit.

d. Add a ‘dummy’ pill to set the colour again, and choose the appropriate colour from the hue circle palette again.

e. Untick ‘Show Header’ from the pills on the rows & columns shelf to remove the axis and the Customer column.

f. Change the title to SALES BY CUSTOMER, adjusting the font size & colour to match (you should hopefully see the colour you’ve recently selected out of the hue circle palette in the ‘recently used’ section of the text colour selector

g. Create a new dashboard and add this view to it, and set to ‘fit width’ if it isn’t already

h. Add a floating button object to the dashboard and position top right

i.  Edit button, by clicking the carrot and changing the option

Navigate to – select the name you’ve given the 4 KPI block dashboard

 Button style – Text

Title – GO BACK (and adjust font to suit)

Background – select the relevant colour to match

j. Back to the KPI dashboard, and select Dashboard -> Actions from the menu, and add a ‘Go To Sheet’ action.

Give it a suitable title, then select the source sheet on the KPI dashboard as the one displaying the Customer Count KPI, and set the Target sheet to be the name of the Customers dashboard created above.

Once you’re happy all is working as expected, then repeat the steps outlined above to create a Sales by Product, a Sales By City and a Sales by Order dashboard.  You may need to create some further calculated fields to handle the UPPER case formatting, and don’t forget to recreate the ‘dummy’ field to help with the colouring.

My version of the challenge is published here.

Happy vizzin’

Donna