Filtering with Parameters

By Ross Perez July 5, 2012
This guest post is from Matt Hull, a Business Consultant on Tableau's Professional Services team.

Recently, one of the customers I work with had an interesting problem to solve. The dashboard he was working on utilized multiple datasources and he wanted to filter all of the sheets in the dashboard by Year. Ordinarily when using multiple datasources, he would have to utilize a Quick Filter for each individual datasource. Given that he had four datasets, he requested that I help him find a way to achieve the same functionality, but with only one Quick Filter instead of four.

Note: if you are new to Tableau, this is what a Quick Filter looks like. It allows users to slice and dice data interactively.

There are many ways of solving this, whether through Data Blending or Dashboard Actions. In this case though, those options would cause problems with existing functionality, so I decided to go with parameters as filters.

Parameters are extremely simple – they allow users to select or input a value. That value can then in turn be used in calculations, and subsequently, filters. You can even set the values that users can select to be automatically created from a field in your data, which saves time.

In this situation my goal was simple. I wanted to create a parameter that would allow users to select a year, and then create a calculation that would use that parameter to filter to the selected year. Here is what I did - if you want to practice on your own, you can follow these directions using the Superstore and CoffeeChain datasets that come pre-loaded in Tableau (just click “Connect to Data”). Begin on the CoffeeChain datasource.

1. Create a parameter called ‘Year’ and give it a list of years applicable to the worksheet

  • Right-click the Data Window and select “Create Parameter”
  • Set the Data Type to “String” and the Allowable values to “List”
  • Note: Because the two data sources have two different date data types (Superstore Sales: Date and Time, CoffeeChain: Date) we cannot set our parameter to a date data type for it won’t be compatible to both. In cases where all data sources have the same date data type, the parameter can be set to that data type.
  • Inside the list, set the years the Year parameter should have access to within the data source and click OK to create the parameter.

2. Create a calculated field which will get the string version of the date’s year

  • Because the data source’s date field has a date and time data type, we need to create a string version of the year to be able to compare it to the parameter just created, which was set to string.
  • Right click the field “Date” under dimensions and select “Create Calculated Field”
  • Name the Calculated field the data dimension with ‘Year’ added at the end. Wrap the date dimension field in the Formula window in string (STR()) and year (YEAR()) functions and select OK to create the calculated field. This calculation will always result in the year of the Date field.

3. Use the calculated field as a filter and link it to the Year parameter

  • Drag the newly created calculated field (“Date Year”) to the Filters view card and a filter box will appear.
  • Under the General tab, select “Use All” to use all of the different years in the data source and select the Condition Tab.
  • Select the “By Formula” option and click the “…” button to open the formula creation window. This is where the calculated field and parameter will be linked.
  • Set the calculated field (Order Date Year) to equal the parameter (Year) and click OK.
  • Click OK once more to create the Filter based on the calculated field which is linked to the parameter.


4. Enable users to select values using the parameter

  • Right click the Year parameter and select “Show Parameter Control”.
  • Select the black dropdown arrow located at the top right of the Parameter Control panel and select the “Slider” option.
  • Open the dropdown menu again, select customize, and select “Show buttons” to remove the buttons from the control panel.
  • Note: There are many options to choose when customizing the control panel. Explore the different options until a panel is created that is appropriate for the purposes of the filter.

5. Repeat step 2 for every data source that will have a date dimension that needs to be filtered.

6. Repeat step 3 for every sheet that will need to be filtered by its date.

In the dashboard, every sheet that has a filter linked to the Parameter will be affected when the parameter control is changed. It takes a couple moments to set up, but the end result is clean and easy to use, as you can see here.

Matt Hull is a Business Consultant with the Tableau Professional Services team. He helps customers across a variety of industries achieve their goals and hone their analytical skills. He can be reached by email at mhull@tableausoftware.com.

By Ross Perez

Comments

Submitted by Tom Walter (not verified) on

You mention that "You can even set the values that users can select to be dynamically updated from a field in your data, so when the data updates and changes users will always have the correct set of values to select from.". However you don't really explain how this is achieved. My understanding is that when you use 'add from field' as you have here, the values are set based on the values at that point is time, but they won't subsequently be updated if the source field changes?

Submitted by Thierry J. on

Indeed, I would be very curious as to how this can be done.

This behaviour is requested in the Idea section (said idea is the fourth most popular one).
The Top 1 idea is also related to Global Filters on Dashboards by the way.

Definitely an area of improvement for a future Tableau release! ;)

Submitted by Tom Cook (not verified) on

Why convert the year to a string? Wouldn't it just be easier to create the parameter as an integer?

Then use DATEPART('year',[Date])=[Year] and only need one calculation.

Submitted by Matthew Hull (not verified) on

My apologies, you are correct in that the parameters are not dynamic after you create the parameter. We have taken that sentence out.

Submitted by Joe M. on

Matt, I believe it is still a little misleading when it says:
"You can even set the values that users can select to be dynamically created from a field in your data, which saves time."
It may be better to rewrite this sentence, and remove the word "dynamically", because it is not dynamic in my opinion. Maybe something more to the effect of a "snap-shot of current values" would be a more accurate description of the current behavior of the "Add from Field" button.

Submitted by Andy Kriebel (not verified) on

Matt,

This is more or less identical to what I posted on my blog back on June 7.

http://vizwiz.blogspot.com/2012/06/create-global-filter-in-tableau-across.html

Andy

Submitted by Nicholas H. on

Getting past the dynamic updating of parameter values (which I also hope to see), using parameters as filters between data sources has been very helpful for me. Even when working with one data source I still use them as partial-global-parameters. Great blog topic!

Submitted by Chris F. on

This is a great solution to a problem that was driving me nuts.... But a challenge for you, I have used this approach to filter three various data sources by a shop I.D. code, however using the parameter I can only select one shop at a time. How can I select more then one?

Submitted by Joe M. on

Chris Fisher, you can use a worksheet on a dashboard, and use a Filter Actions setup with Selected Fields to filter across data connections. It is not a multi-select check-box interface, but you can multi-select marks to get the result you are looking for. Today's Think Data Thursday #3 http://community.tableausoftware.com/message/185359#185359 will hopefully cover this approach (it will be recorded if you can't make it live).

Submitted by Chris F. on

Joe as ever thanks for your guidance, will give that a shot. FYO still need to get that Omni demo planned. Chris

Submitted by Sapan D. on

I am also using parameter to filter out my data. But parameter list is not updating automatically when I refresh the data extract. How can I update the parameter list upon refreshing the data?

Submitted by Tom W. on

Hi Sanip,

I eventually figured out a workaround that functions somewhat like a dynamically refreshed parameter. One of these workarounds may help in your case. They are described in detail in the last couple of comments of my dynamic parameters idea thread.

http://community.tableausoftware.com/ideas/1178

Cheers
Tom