Is it possible to create a dashboard that allows you to both swap sheets and to conditionally hide and show parameters depending on which sheet is in display? Yes, of course! In this post, I am going to walk you through how to combine these two tricks and show you how I applied them to your dashboard.
Our goal is to produce something like this:
You can check out the viz I use to explain this process on my Tableau Public, where you can download the workbook to see my calculations and parameters. If you want to create your own dashboard, head over to Makeover Monday Week 26 and download the data.
Let’s get started!
Swapping sheets
Using parameters to swap sheets is a great solution when we want to avoid cramming a dashboard with too many graphs, but we still want to make them all available to our users. Here’s how:
1. Create two (or more) sheets
One of the two sheets need to include the parameter(s) we would like to hide. In my case I created
- a map displaying all the countries at once (Complete map)
- a map only displaying the top or bottom ranking countries (Top/bottom map), which came with:
- 2 parameters (one for selecting countries either at the top or at the bottom of the ranking, and the other to select the amount of countries to display, check out Valantis’ blog to learn this trick). These two are the parameters we will try to hide in the next session.
2. Create a parameter to swap the sheets
Create a parameter called “View complete map or Top/bottom map”. Set data type to string and allowable values equal to the names of the sheets we want to swap:
3. Show parameter control
Show parameter control (by clicking on the parameter in the data pane and selecting “Show parameter”) and display it as a single-value list
4. Use the parameter in a calculated field
Use the parameter in a calculated field that you can call “Swap sheets” by simply writing the parameter name into it, like so:
5. Use the calculated field as a filter
Drag the calculated filed “Swap sheets” onto the filter shelf of our two sheets. and select only the name of the that specific sheet. In the case of my viz
- I dragged the “Swap sheets” calculated field it on the filter shelf of the Top/bottom map sheet and select “Top/bottom map”.
- Then drag it on the filter shelf of the Complete map sheet and select “Complete map”.
6. Create the dashboard.
- Add a layout container
- Drag one sheet into it
- Drag the second sheet in the same container, at the bottom of the first sheet (you can also place it at the top, it doesn’t really matter).
- Please note: When you drag the second sheet into the container, a gray see-through rectangle will appear. Make sure that the rectangle stays within the container. If the rectangle is langer than the container, you are dropping the sheet directly into the view, and the trick won’t work. If that is the case, you are doing it right, and you can drop the sheet in!
- Make sure to show the “Swap sheets” parameter: if you succeed in dropping the sheets on top of each other, when using the parameter one sheet will be shown in its entirety, whereas for the other only the title will be visible, and viceversa. I like to hide both titles for a clearer view.
This part is a bit tricky, so I am adding a video to illustrate how it works:
Hiding parameters
Now that we have learn how to swap sheets, let’s go ahead and hide our parameters. We have our dashboard with swappable sheets and three parameters:
- The first parameter allows us to swap the sheets, and we would like it to be visible at all times.
- The other two parameters make sense for one sheet but not for the other. We would like to hide them when we select the “Complete map” sheet, and to display them when we select the “Top/bottom map” sheet.
The method: pushing the parameter out of the view
What we want to achieve is to literally push our parameter up and out of the view when the Complete map sheet is showing, and down into the view when the Top/bottom map is showing. Due to this method, it is important to put our parameter on top of our viz. (Of course, you can adapt and apply this method for the sides and bottom of the viz, too. If you wish to keep your parameters in the middle of the sheet, that is also possible, but it comes with some limitations, since the users might inadvertently click on the blank sheet and disturb/confuse their experience).
I draw heavily from Andrew’s post in this section, definitely check out his blog for more.
How does it work?
This can be achieved by creating two Blank sheets, and setting them so that they react to our Sheet-swapping parameter in opposite ways. I am going to follow Andrew’s advice and color the sheets in green and red for the time being, so that the explanation is visually more clear. Of course the colors are completely arbitrary and in any case they will need to be white/transparent in the end.
STEP 1: Create two blank sheets
To create the blank sheets, create a blank calculation by typing “ “ into the editor and name it “Blank”.
Add the Blank calculated field to rows. Change the background color by right-clicking on the view, selecting “Format” and changing the Default worksheet color. Repeat for the second sheet.
STEP 2: Drag the blank sheets and the parameter into a floating layout container
In the dashboard, add a floating layout container and drag the parameter into it. Then drag the green blank sheet at its top and the red blank sheet at its bottom. For now, it should look something like this:
STEP 3: Use the sheet-swapping parameter to create a filter for the blank sheets
Create a calculated field that will be used as a filter, by using our Sheet-swapping parameter “View complete map or top/bottom map” and setting it equal to the name of the sheet for which we want to hide the parameters. In this case, I want to hide the two parameters when the dashboard shows the Complete Map:
STEP 4: Add the calculated field to the filter shelf of both sheets and set the filters to opposite values
After dragging our “Complete Map Filter” calculated field to the filter shelf of both blank sheets, make sure to
- set it to false for the sheet at the top of the parameter (green) and
- set it to true for the sheet at the bottom of the parameter (red).
This will make sure that when we select the “Complete map” in the Sheet-swapping parameter, the green sheet, which is on top of the parameter, will disappear. Having more space in the container, the red sheet at the bottom of the parameter will expand and push the parameter up.
Conversely, the red sheet, which is at the bottom of the parameter, will disappear when we select the Top/bottom map, and this will give space to the green sheet to expand and push our parameter down and into the view.
If you are still confused, check out how the sheets appear and disappear in this video:
STEP 5: Change layout container’s y-coordinate to make sure the parameter is pushed out of the view when needed
Select the layout container and change its Y position to a negative number big enough for the parameter to be pushed out of the viz when the sheet-swapping parameter displays the complete map, but small enough so that the parameter is visible when the top/bottom map is displayed.
Play around with the Y-coordinate’s position until you are satisfied, then change the background color of the blank sheets to white or transparent.
STEP 6: Repeat steps 2, 3 and 4 for the other parameter
Duplicate the green and red blank sheets and repeat steps 2, 3 and 4 for the second parameter.
STEP 7: Enjoy your new viz!
Congrats for making it so far! Now you can swap sheets and hide parameters whenever you want!
Here my final viz. What will you create?
Wanna learn more about Tableau or Alteryx? Do you want to use these tools to their full potential? Check out our trainings and consultancy services!