Sets and Filters the Condition and Top N Tabs - What do they do?

My blog has moved to jimdehner.com 

Thank you for all the support over the past 2 years - I recently updated my blog to make it easier to read, improve the rendering of images and tie back to video presentations that are associated with the content - Check it out at  Jim's new blog  -   

 Using  Filters and Sets is nothing new  - but how often do you use the "Condition" or "Top N" tabs - and have you ever used them together.

Here we are going to look at how to use Conditions and Top N in combination with Sets and Filters - 

The Condition and the Top N tabs are on the setup for Sets and Filter - They look the same on each and provide the capability to further filter the dimension used in the set or filter.  The can interact with each other - in an   AND  fashion and can be used in combinations with filters on other dimensions or sets -  (Presented at VizConnect Link to Recording)

Example 1 - Simple Filter

Using Superstore data, I've created a parameter on Region and a filter on State with this formula  on the condition tab - 

Sum([Sales])>10000  and Max(Region) = [Region Parameter]

The Formula window works like and LOD - dimensions and measures must be aggregated and you can not use ATTR(). The condition will filter out all States with total sales less tha10000 and only states in the Region selected in the parameter will be included.

Now add in a Top N type filter ( I used the Bottom 4)

And the Bottom N is applied in addition ( AND filtering )  to the filters applied on the condition tab.

Before seeing more complex examples we need to see how the Order of Operation will affect the filtering. 

The Top N filter takes place in Step 4 in the Order of Operations along with the creation of Sets and Fixed LOD's expressions.   That is after Context Filters but before Dimension filters are applied.  It is also before any other type of calculations including Table Calculations are determined in steps 7 through 10 - 

Example 2 - Top N Filter with Context Filter

We will start with a Superstore example to find the Top 7 States based on sales 

 

With Region in Context the filter will return in 7 state with the greatest sales from the regions in the view

if Region is removed from Context the top 7 are determined before the Region filter is applied and 

Example 3 - Multi Dimension Top N  Sets 

Now let's look at combining additional dimensions in the Top N.  We still want the top 7 States but the users also wants the top 7 based on the total sales of selected subcategories - 

First we need an LOD to determine the sales at the combination of State and Subcategory

{ FIXED [Sub-Category],[State]:sum([Sales])}

Next create a set with this condition 

sum({ FIXED [State],[Sub-Category]: sum([Sales])} )= 

sum([3 LOD sales by subcat and state])  

and add in the Category and subcategory Filter to Context -  and the set will include the 7 states that have the max total sales based on the filter subcategories 

 

when the subcategories are changed the Top 7 adjust :

Example 4 - Fixed 7 States find the Top N subcategories

Users want to find the top categories within the 7 overall best States. 

Now the Order of Operation and the use of Table Calculations come into play.  First, we need to first determine the top 7 states and then within each state find use Rank to order the subcategories -

Ranking functions are table calculations that are determined in Step 10 of the OOO after the sets and lods are determined in Step 4.   Using the LOD we calculated in the previous example 

{ FIXED [Sub-Category],[State]:sum([Sales])}

add that to a Ranking function

RANK_UNIQUE(sum([3 LOD sales by subcat and state]),'desc')

and replace the set with the ranking function the number of categories to show

It is easier to see the result in a text table format 

The top 7 States overall are first determined then the top 3 subcategories are identified in each state

Example 5 - the Reset Button 

Ever have a dashboard where you've applied a several filters and you would like to "Reset them"  with a single click - It can be done with a "Reset Button" - No magic here just the creative use of an action filter 

Create a worksheet with a shape and a label - 

then on a dashboard add an Action Filter - from the Rest button to sheet where the filters are applied  - the select each filter you want to reset - you will get a Warning Message  but that is not a problem

Select the rest button will return all values to the filters

Example 6 Default Values for filters

Some users want set default values for filters and it can be done with some creativity using a parameter ( Credit for this goes to Joshua Milligan - Tableau Zen Master Hall of Fame -  Link to Joshua's work  )  It is done by first creating a parameter on Region and adding in 2 values - All and Default

Next create a case statement for Default   In this example there are 4 States we want for the when the user selects Default - they are connected with OR

CASE [Region Param with all and default]

When "Default" then

[State] ="New York" or

[State] = "Illinois" or

[State]= "Florida" or 

[State]= "Washington"

end

then we just need a TF filter 

[Region Param with all and default] = "All" or 

[Region]=[Region Param with all and default]

or CASE [Region Param with all and default]

when "Default" then [8 Default state from region]

end

When All is selected in the Region parameter the statement returns a True.  if the user selects and single region just that region will return a True. When Default is selected the Default case statement returns a True for the 4 states and returns this 

Example 7  Cascading Parameters

Frequently users want to have the drop down values in one parameters be dependent on the value of another parameter.   Currently that is not capable in "out of the box Tableau" and I have responded to users to use a parameter and a "Relevant Value Filter or Set" on the Condition Tab like this:

Which would create a set base on the parameter and only show the IN values in the set 

and from there you can use the set to drill down 

If you can use extensions there is another option

First you need to create 2 parameters - one to drive the extension - here on Category

  

Then a second parameter that will receive the values from the cascading filter and place both on the worksheet

Search Parameter and select Data Drive Parameter

the extension needs to be configured - on the first page Identify the parameter that will accept the values (the second above), the worksheet it is on and the dimension on that sheet that relates to the values ( here subcategory)

on the Option Tab select 'Update List when Parameter Changes' and the parameter that will drive the list (the 1st parameter - here Category Parameter)

Then create the viz and the first parameter will drive the drop down list in the second 

I hope this help explain the use of the Condition and the Top N tabs and has given you some ideas on how you can use them in your vizes 

The workbook used here can be found at   Link to Workbook

Enjoy

Jim

Parameters and Parameter Actions

My blog has moved to jimdehner.com 

Thank you for all the support over the past 2 years - I recently updated my blog to make it easier to read, improve the rendering of images and tie back to video presentations that are associated with the content - Check it out at  Jim's new blog  -   

Do you use parameters - I love them and use them a lot - Let's look at them in more detail

Parameters are one way users can directly input a value into Tableau.  

They are:

• Single valued –  have one value at a time 
• Static the value is constant until manually changed by the user 
• Global – they are available for use throughout the workbook. 

But they don’t do anything until they are used in a calculation, a set, or a filter.  

The same parameter can be used in any mix of calculations or filters on a single any combination of worksheets or dashboards.  Changing the value of the parameter will change the calculation or filter wherever it is used.  The change takes places as the sheet is opened.

There are 3 steps to follow when using parameter

• Create it
• Put it in the viz
• Use it in a calculation or filter

1-Create it

Parameters can be created directly from the data

Or from the Filter or Set Top N  tabs

Select Create Parameter - when the window opens  set up the parameter

• Add a meaningful name that tells the user what to do
• Select a data type that matches that of the dimension you will use
• Load values into the parameter either typing directly or from the dimension
• Click OK and you are done

2 Add the parameter to the viz

All you need to do is open the parameter pill and select Show Parameter Control

3-Use it in a calculation or a filter

After creating the parameter you need to use it - it is the calculation or the filter that affects the data in the viz - not the parameter by itself. 

Example 1 - Parameter driven T/F filter

The easiest way to use the parameter is in a True/False filter - the expression just says when the subcategory is equal to the one the user selected using the parameter return a True

Placed on the filter shelf and set to True will return this

Example 2 - Use it in a Set

The same parameter can be used to create a set - The Formula area on the Condition tab acts like an LOD -aggregate the dimension using Max or Min (sorry no Attr()) - set the Max subcategory to the parameter value to select the "In" value - all others are in the "Out"

a possible viz could compare the selected subcategory to all others

Example 3 - Change all views in a dashboard

Parameters are global - when the value is changed it changes all the calculations or filters where the parameter is used -

Bring the 2 worksheets together on a dashboard and changing the parameter value will affect both sheets without using filters or actions

Example 4 Top N filtering

Parameters can also take on numeric values like in the following Top N example 

Create a filter on Subcategory and open the Top tab 

Select Create From Parameter

When the window opens - select a starting value and a range

and create a viz 

The user can set the N from the control on the left - Note also the subcategory drop down will list all subcategories and has a reference to the Top N filter application 

Example 5 - Parameter Actions

With the 2019.2 release Parameter Actions were introduced given the user an easy visual way to change parameter values for the viz without opening a drop down - 

I will replace the parameter drop down from the earlier example with a value picker - a simple worksheet with the values (here subcategory names and a circle mark)   A horizontal will work well the the overall layout of the dashboard

Add the value picker to the dashboard and create a Parameter Action -

• From the Dashboard on the upper ribbon select Action
• Then Add - Change Parameter
• Give the action a name
• The source is - Value Picker 
• The target is  Select Subcategory parameter

I used Hover for the example you can select what works best in your analysis

The value of the parameter and the viz will change as the user  hovers order the Value Picker marks - see the video

Have some fun with Parameter Actions -They are major enhancement  for the user experience

Example 6 Dynamic Parameters

Now the game changer - Dynamic Parameters - introduced with the 2020.1 release the parameter value list can be updated when the date source is updated and the workbook is opened - see how they work in the simple example below

In this example sales data is updated weekly - the initial load includes 4 dates 4/13-18 - the user wants to use a parameter to filter by individual date  - the viz looks something like this:

The dynamic parameter set up adds only a couple fields to the one you have used so far

• the parameter that is to be updated and the field in the data set
• initial value of the parameter (default is Current) 

In the example I added an LOD for the fixed max date in the data set - the parameter value will update to the latest date in the data set

When you update the data and when you reopen the book the parameter updates

NOTE - UPDATE     the 2020.3 release now will update the parameter and from a data source refresh or edit  - If you are on an earlier version see below

Desktop users - Note if working from a live data source and manually refresh the source you will need to close and reopen the Tableau workbook before the parameter updates

I hope this helps you understand parameters, parameter actions and dynamic parameters better  - I love using them - with a little practice you can too

The workbook examples used in the first part of this post can be found on my Tableau Public site at  https://public.tableau.com/profile/jim.dehner#!/vizhome/parameterworkbook/ValuePickerdrivendb?publish=yes

Enjoy

Jim