Swap multiple sheets in Tableau
This blog will tell you about how to switch multiple sheets in tableau using a parameter that will act as a filter. It will also explain how to create a sheet, calculated field, parameter and dashboard step by step.
The motive is to swap the sheets according to the option selected in the filter. The first option will be showing sheets related to Discount. The second option will be showing sheets for Sales and the third data will be showing Profit related sheets.
When you select Discount in the filter then the sheets related to Discount will appear and similarly for Profit and Sales.
Below are the steps to create the dashboard as shown in the video.
1. Creating sheets
Create two or more sheets that you wish to swap. Here we will be creating sheets for three different types of categories (Discount, Sales and Profit).
Steps to create sheet:
a) Create sheets for showing the metrics of Discount.
a. Discount by Region: Drag and drop Discount field into rows and Region into columns as shown below.
a. Discount by Category: Drag and drop the Discount field into rows and category into columns as shown below.
b) Similarly, create different sheets for Sales and Profit. (i.e. Sales by Region, Sales by Category, Profit by Region, Profit by Category). Now drag and drop Sales or Profit in rows instead of Discount field.
c) In total, we will have six sheets now. Two for Discount (i.e. by category and by region), two for Profit and two for Sales.
2. Create a parameter that will act as a filter
After creating the sheets, we will now create a parameter that will control what category of sheets the user wants to see.
Steps to create parameter:
a) Go to the dropdown near the search option. Then click on Create Parameter.
b) Then a dialog box will be displayed. Now set the name of the parameter (I have named it as Metric).
c) Choose the datatype as string and in Allowable values select List.
d) Now enter the options for metrics in the parameter. Values on the left will be used further in the calculated field to show the required metric. The ‘Display As’ section will have the labels that will be shown to the end-user. If you wish to change Metric’s label to some other value then it can be done from here.
e) Click Ok.
Hurray! We have completed the second section.
3. Create a calculated field
Now calculated field needs to be created to control the filter by parameter.
Steps to create calculated field:
a) Go to the dropdown near the search option and click on ‘Create Calculated Field’.
b) Rename the field as per the choice. (I have kept it ‘Show sheet by Metric’)
c) Write the calculation as shown below-
IF [Metric] = ‘Discount’ then 1 ELSEIF [Metric]= ‘Sales’ then 2 ELSE 3 END
Here Metric is the parameter that we have created above.
If there are only 2 options in parameters then you would not require ‘elseif’ statement, simply use: If [Metric] = ‘Discount’ then 1 ELSE 2 END
4. Display parameter
Now we will show the parameter in sheets that we wish to swap.
Steps:
a) Right-click the parameter (Metric) you have created above and click show parameter.
b) It will get displayed on the right just like a filter with 3 values.
5. Adding metric filter
Now we have to add the calculated field to the filter pane to show only selected metrics.
Steps:
a) Drag and drop the calculated field (Show sheet by Metric) created above to filter pane.
b) Then click on All values and click next.
c) For Discount sheets, enter 1 in range of values as shown below and click Ok.
d) Follow similar steps for other sheets. For ‘Sales by Region’ & ‘Sales by Category’ sheets, select the range of values as 2, and for ‘Profit by Region’ & ‘Profit by Category’ sheets choose the range as 3 as they are coming at the second and third position in Parameter respectively.
6. Creating dashboard
Now we will combine all the sheets and show them in a dashboard.
Steps:
a) Include both the sheets of Discount on the dashboard. Keep the sheets floating when you add them to the dashboard for ease of alignment.
b) Keep the metrics parameter on top of the dashboard.
a) Then add ‘Sales by Region’ and ‘Profit by Region’ sheets on top of ‘Discount by Region’ sheet.
b) Similarly, add ‘Sales by Category’ and ‘Profit by Category’ sheets on top of ‘Discount by Category’ sheet.
The final dashboard will look like
Note: Hide the title of sheets that you have used in the dashboard as the title will not be hidden when you change the parameter.
Hurray! You have completed this project. When you select a parameter from the filter, related charts of the metric will be shown automatically.
Happy Learning!😊
