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

More than dots on a map - The renewable energy story

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  -   

Renewable Energy – How big is it-

A dashboard should tell a story beyond just presenting data.  It needs to be interesting and when possible encourage the viewer to interact and investigate to make the story their own. This is the end product

But that's several iterations later -

It  started like this - as a result of a trip from Nashville to Chicago. In northern Indiana there was a 10 mile stretch  where as far as I could see to the east or the west all I saw were wind turbines. Wow – didn’t expect to see that – thought that wind power was just a west coast thing.  

I found a data set that includes the location of every wind turbine in the US -  They were concentrated in the mid-west and south  Not what I expected at all - I had a lot to learn!

While interesting there wasn't much of have a story from that data  – just raised a lot of questions – How much energy do wind turbines produce? Is it a significant portion of overall energy production? What about other renewable sources? How does it vary by state?

What would you do - something like this might work but how would you make that

To start you need data. Lots of data! – Energy generated in Kwh , by generation source at the state level – you could use a search engine and hope for the best – there are also several portals that you could consider

• ·         DATA.GOV  - US Government open data – ties to over 250,000 databases https://www.data.gov/
• ·         American Fact Finder  – guided search portal to US Census data bases https://factfinder.census.gov/
• ·         Data.World  collaborative access to the open data community https://data.world/
• ·         Stat.Can  is the national statistic office portal in Canada   https://www.statcan.gc.ca/

The energy data used in here was found at www.eia.gov the US Energy Information Agency site but each energy source had to be extracted separately – (the a future post will go through the Tableau Prep flow used to combine and clean the separate energy source files). (Note: data is for electric energy generation in 2017) 

But what's the story? – Does energy generated from renewable source really represent 10% of the total?  That’s what I thought before looking at the data but that’s just not right – nationally it is closer to 15% and it some states renewable energy sources can account to 85% of the total. 

• the portion of energy generated from each of the renewable source in each state
• the state rank nationally 
• and the trend since the start of the century

That would be a much more interesting story that just the location of  wind turbines that got my initial interest.   It would also be good if the dashboard was dynamic so individual users could look at their state or region.

The upper portion of the viz is where you can create the  most impact – Make the title a statement that takes the user away from any preconceived notions and use BAN blocks present both present the facts and set the color code for the rest of the viz

The block color – teal for Hydro, Gold for Solar, Red for Wind and Black to total all sources will be used throughout the remaining charts

Now build on the story including a Trend chart to show growth since the start of the century and  a state level ranking chart to see a comparison throughout the area.  They are relatively straight forward area and bar charts using the percent of total calculation

Finally, allow the user to select a state or area and change all the charts accordingly –

That’s the fun part and you may have noticed references to sets in the previous charts -

Sets and set actions driven off a state map make the dashboard dynamic. 
To create the State set, Open the STATE pill and Select CREATE then SET

When the box opens Select any STATE – we are going to change it with a set action, so the initial value is not important

Next create a simple Map at the State level

Drag State to the canvas to create the map and be sure to place the State Set on the marks card

Bring it all together on a dashboard using containers to fix the location of the individual sheets

Make your point in the title and always include attributions for data sources, functions or material that are not your own.  Also, if you want the user to do something you need to tell them what to do and why

One last thing – need to make the dashboard dynamic by adding a set action

And then set the action to go from the map on the selection form the viz

on the individual worksheets set action filter has been added

The filter is set as IN meaning that the States selected from the Map will be applied as the filter – you can select a single state, or several using a Cntl Select or use the lasso to select a area

  

Congratulations – you’re done!

Download the actual workbook, complete with  “How To” instructions, from my Tableau Public site at https://public.tableau.com/profile/jim.dehner#!/vizhome/RenewableEnergySources/Dashboard1  - 


Jim