Sunday, 22 December 2013

Change your view on Parameters

Parameters like their older brother Table Calculations are a great way of adding functionality to your dashboards. Parameters give you control of pretty much anything you can think of and are pretty straightforward to use.

Two ways that i like to use them is to control what dimensions or measures are in a viz and to use them to control what sheets are displayed in a dashboard.

Dimension Control

Say you have a dashboard with three sheets

Sum of sales per category
Sum of sales per Customer Segment
Sum of sales per state plotted on a map.



What you would like to do is enable the user to select whether they are looking at sales, profit or discount, heres how you do it. 

Step 1. Create a parameter.

Right click on the Parameters shelf and select create new parameter.
Name it "Display Selection"
Change data type to string and select list
Enter, Sales, Profit and Discount in the value list and hit ok.



Step 2. Create a calculated field based on the parameter.

Right click on our new parameter and select create calculated field. 
Change the name to Display.
We now need to tell Tableau what to do with each of the possible values of the parameter, I find using a CASE statement the easiest, so something like this

CASE [Display Selection]
WHEN "Sales" then SUM([Sales])
WHEN "Profit" then SUM([Profit])
WHEN "Discount" then SUM([Discount])
END



Change it from descrete to continuous

Step 3 Add this to the viz

Replace the SUM(Sales) with our new calculated field



Step 4. Add the parameter Control

On your dashboard, go to Analysis->Parameters and then select our new paramter.

You can now select what value you want to display in the display.

Step 5. Edit the title of each of the sheets to be :

<Parameters.Display Selection> <Sheet Name>

So that the selected parameter will show up in the dashboard views.



We can go further if we like...

Step 5. Select whether we show SUM or AVERAGE or COUNT Sales,profit of discount

Create another parameter with the values SUM,AVERAGE and COUNT, call it calculation selection

Step 6. We can now change our display calculated field to include the new parameter values

IF [Display Selection] ="Sales" and [Calculation Selection]="SUM" then SUM([Sales])
ELSEIF [Display Selection] ="Profit" and [Calculation Selection]="SUM" then SUM([Profit])
ELSEIF [Display Selection] ="Discount" and [Calculation Selection]="SUM" then SUM([Discount])
ELSEIF [Display Selection] ="Sales" and [Calculation Selection]="AVERAGE" then AVG([Sales])
ELSEIF [Display Selection] ="Profit" and [Calculation Selection]="AVERAGE" then AVG([Profit])
ELSEIF [Display Selection] ="Discount" and [Calculation Selection]="AVERAGE" then AVG([Discount])
ELSEIF [Display Selection] ="Sales" and [Calculation Selection]="COUNT" then COUNT([Sales])
ELSEIF [Display Selection] ="Profit" and [Calculation Selection]="COUNT" then COUNT([Profit])
ELSEIF [Display Selection] ="Discount" and [Calculation Selection]="COUNT" then COUNT([Discount])
END


Step 7. Now we add the new parameter to our dashboard.

Step 8 Edit the title of the sheets to include the extra info :

<Parameters.Calculation Selection> of <Parameters.Display Selection> <Sheet Name>

We've now got a dashboard that will let us change both the aggregation and the measure, in one single dashboard rather than 3 separate ones.







Matt Francis

Author & Editor

Tableau Zen Master, Social Ambassador, Wrangler of Data, Vizzer of Data

2 comments :

  1. I am happy that you are here, understanding this, as opposed to viewing the news about terrorism. Your inside compass is valid, looking for information, peace or motivation as opposed to trouble. This helps out the world's peace and prosperity than viewing the news does. State one's views

    ReplyDelete
  2. In the bar chart, I see the X axis as 'Display'. I would rather like the axis to be named as 'Sales' or 'Profit' or 'Discount' based on the selection of my parameter. How can this be achieved?

    ReplyDelete

 
biz.