Everyone who at least once tried to build more complex dashboard in Tableau reach the point when he/she had to make a decision which sheet stays on dashboard and which has to be dropped to at least keep the slight feeling of clean layout. Tableau provides various ways to organize workbook layout and to make sure that data/graphs stay readable.
One of the most notable approaches is a ‘container’ one. It’s based on creating smaller ‘silos’ inside main layout pieces.
Those silos usually consists of 2 sheets that are not displayed simultaneously but rather on demand – depending on selectors / current details level.
I will recycle dataset I created for another post: Business Hours calculation in Tableau
First of all we need to create 2 sheets we would like to show exchangeably. I’ve created 2 sheets (DE dept and and UK dept) by simply filtering one of the teams. Yes, this selection/split can also be achieved using simple filter but for the case purposes let’s stick to this approach.
Add both sheets to dashboard – this is the moment when ‘container’ approach kicks in. Make sure that layout is split vertically. In this way both sheets should use exactly 50% of container width (make sure you have selected fit width or fit Entire View). Container layout should look more less like this:
There are at least 2 major ways to handle sheets displaying:
1. Using parameter
2. Using action and filtering
2a. Using switch to hide sheet
Parameters give a lot of control over what is happening on the workbook. They let you change the granularity of date pill, change the date range
In short, changing parameter will set value in calculated field which is used to filter visibility of sheet. Let’s create parameter we will be using to display / hide sheets:
To make it work, we need a Calculated field which will gather current parameter value”
Now let’s place this calculated field in the Filter shelf on both of Dept Sheets. What is important is to set filter condition to use Custom Value list as a filter conditions. For UK sheet enter 1 in ‘Enter Text to Search or Add’ in the top section. Don’t forget to press little plus sign on the right.
Right now you should have UK sheet with filter setup on 1 and DE sheet with filter setup on value 2.
After adding parameter to the dashboard, by changing it, you should be able to hide certain sheet as on ‘Parameter Selector’ tab in the embedded example above.
Using action and filtering
There is also another way to handle sheet visibility. And actually it is more native to Tableau than previous approaches. As You might already noticed, showing or hiding certain worksheet relies mostly on the fact of having anything to how or not. If there is no data that meet certain criteria then whole sheet will not be rendered. It takes this trick of replacing the data to the different level. Why not use it for showing details of currently analysed data? This time one of the sheets (the ‘selector’) will always remain visible. It’s the second sheet in ‘container’ that will be turned on and off. Goal here is achieved by simple actions which filters top table level. Refer to show ticket details tab. As you can see there’s on sheet with summary and additional (with details) is being shown upon clicking team on summary. Magic is done using department filter on details tab:
Action is defined on the ‘department’ sheet then as:
Using switch to hide sheet
Idea behind this approach is similar to the parameter one. However this time, interacting with switch located on dashboard does all the magic. Almost everything clickable can be a switch – a small sheet with just an icon (shape) on it or any other already existing sheet. Clicking this switch will set value to a calculated field which will make rendering sheet impossible and thus making it invisible. Let begin with creating 2 Calculated Fields – ‘off’ and ‘on’. Let’s make it simple. Just put 0 for off field and 1 for on field:
What You have to to right now is add this on field to detail shelf in sheet you want to hide and setup proper actions to switch. In my case switch is just a sheet with bug icon. Action assigned looks as follows:
Idea here is to simply replace field which allows sheet to be visible with the one that will clear it. Note that ‘show all values’ option is selected which will assure that when You deselect the bug icon, your sheet will be show again. This solution can also be partially used as a multiple sheets switch. Adding contrary actions should work.
Note that ‘containers’ approach is not perfect. First of all You will always have a small gap in layout even if You hide title of switched sheets. Having multiple hidden objects will result in even bigger gap! On top of that, showing and hiding sheets causes Tableau to render bigger chunk of information in short period of time. This can lead to performance issues. As You can see, such approach definitely has pros but, as everything, it must be used with common sense.
photo by Abigail Keenan