# Feature Geek: Filtering by Discrete Regular Aggregate Pills in v9.2

Tableau version 9.2 is getting close to release and thanks to a couple of little birdies — moles? informants? people whose initials are [redacted] and [redacted])? — I’ve learned about some features that weren’t announced during the keynote at the 2015 Tableau Conference. I asked for permission to talk about them and over the next few weeks I’ll blog about a few of my favorites.

NOTICE: This is a discussion of beta software and as such the described features and functionality are subject to change or complete removal prior to actual release. (Francois told me I’d get pie charts for Christmas if I didn’t put this warning here.)

## The Problem

Up through v9.1 Tableau will happily put any continuous (green) pill on the Filters Shelf, but when it comes to discrete (blue) pills Tableau’s behavior has been inconsistent.

• Discrete pills based on dimensions and record-level calculations like [Sales] > 10000 are supported.
• Discrete pills based on regular aggregates such as SUM([Sales]) > 10000 have not been supported.
• Discrete pills based on table calculations such as LOOKUP(SUM([Sales]) >10000, 0) are supported.

Here’s an example where I’m looking at Sales & Profit in my pirate-themed shops in New England. I’ve set up a KPI with the following formula:

```IF SUM([Profit]) > 5000 THEN
"Thar be profit!"
ELSEIF SUM([Profit]) > 0 THEN
"Need more booty"
ELSE
"Arrr!"
END```

In version 9.1 and earlier if I try to filter to see only the Arrr! circles to find the money-losing stores, I can’t drop the KPI pill onto the Filters Shelf:

There have been a few workarounds for this by:

• Brushing/highlighting via the Color Legend.
• Using a LOOKUP() calculation as described in the table calc section above.
• Converting the discrete KPI into numbers that can be used as a continuous filter like IF SUM([Sales]) > 10000 THEN 1 ELSE 0 END, then filtering for 1.
• Using FIXED Level of Detail expressions to convert the aggregate measure into a dimension.

This seemingly arbitrary distinction between dimensions, regular aggregate measures, and table calculations confuses new users and the solutions can cause further complications. It’s a point of friction that to us as users doesn’t seem like it needs to be there at all, no matter the underlying complexity of the problem.

## Engage Friction Reducers!

In version 9.2 (with a little Mapbox Pirates theme goodness) this issue is gone, *poof* like it never existed:

So we users don’t have to keep in mind the distinction between calculation levels and pill types, we can filter away.

And the discrete aggregate pill is also completely usable as a quick filter:

…and can even be used as a scoped filter like continuous regular aggregate measures:

## Complications of using ATTR()

One additional note that I discovered after this was posted, this text is copied from How to Have Sets with Your Secondary (9.2 Style) via Aggregated Booleans.

There is still one limitation here, we can’t drop ATTR(Xerox Flag) from a primary data source onto the Filters Shelf:

I suspect the reason why has to do with ATTR() being a special aggregation computed in Tableau whereas MIN(), MAX(), COUNT(), and COUNTD() are all computed in the data source.

However, when using data blending we can put ATTR(secondary dimension) on the Filters Shelf because of how data blending works – many aggregate filters on secondary sources are generally computed in Tableau, not in the data source, so Tableau already has that ability to filter on ATTR(secondary dimension). Here’s an example using Xerox Flag from a duplicated secondary source:

Note that Tableau is adding an extra Null value to the list of filter values because that is Tableau’s default behavior for secondary dimensions used as filters. Vote for Option to eliminate null value form Quick Filter on secondary data source field if you’d like to get rid of this.

## Conclusion

For me as a calculation writer this is a nice new feature because it lets me express KPIs and other measures in their natural forms and work with them more easily. As a trainer my students are less confused and can focus more. Thank you, Tableau devs!

Tableau Public is running v9.2 already and you can see the filter by discrete regular aggregates workbook and download it to your v9.2 beta. (If you’re not running the beta, you can get it by contacting your Tableau sales rep).

# Keeping a Value in Totals Whilst Excluding from Quick Filter List

Over at Peter Gilk’s Paint by Numbers blog there was a question on this post on filtering while retaining results. Here’s the what Jeremy asked:

May I ask if it would be possible to get a detailed explanation of applying this principle to a different type of data?

For example, I would like to see the US Sales totals, and have the ability to filter it to a US state without the ability to select a US territory (Guam, Puerto Rico, etc), but to have the US territory sales remain in the US national totals. How could I do this?

In this short post I cover two different techniques how to do this using a self-data blend and LOD expressions, respectively.

# Filtering and Hiding

### Joe Mako’s Order of filtering post:

http://www.tableausoftware.com/support/forum/topic/question-how-create-product-velocity-calculation#comment-39603

A long post on global filters, action filters, etc.

Global filter across data sources (parameter with calc field to test the parameter)
http://vizwiz.blogspot.com/2012/06/create-global-filter-in-tableau-across.html

Complex sorts in table calcs

use as filter is faster than quick filters

Use as filter can work on any clickable element in the view

• Edit actions to change what they use
• see the TCC11 – JediTricks video for some funky global filtering

(use in nursing dashboard for per-unit stuff?)

Type-in on a quick filter is used as a wildcard, to change this use a type-in parameter and then a calc field with:

`[ID] == [ID Parameter]`

That will return true for the one row of interest and false for everything else. Just put that on the filter shelf and select just “true” (you’ll need to set the parameter to a value that exists to have the “true” option available).

### Can’t drag a filter

If a pill can’t be dragged onto the filter shelf, try converting it into a continuous.

Lookup0 to make filters work locally after all db calculations are done. Handy to filter in %ofTotal calc. #tableaujedi #TCC11
by mohanganeesh at 10/18/11 2:38 PM

Hide happens after the data calculations. Filter happens before. Very handy to use hide in % of total calc. #tableaujedi #TCC11

Here’s a hiding link using the LOOKUP() trick:
http://community.tableausoftware.com/message/172343
File is Hide via Filter on Table Calc.twbx.zip.

Create a field called Date Filter w/value LOOKUP(ATTR([date]),0). Tableau will treat it as a date for the filter functions, but still have all data available to the calculations.

Hide (filter) rows if first column is empty

Filtering first year out of YoY calculations:
http://kb.tableausoftware.com/articles/knowledgebase/filter-first-year

Filtering YoY to same month/day as current date:
http://www.freakalytics.com/2012/06/05/balancing-analysis-of-multiple-years-by-filtering-through-the-same-monthday-as-today/

### Grabbing certain prior dates out of the data with user-selectable “filter”:

one way to filter for data within specific dates:

http://community.tableausoftware.com/message/175953#175953

`IF [scanDate] >=[StartDate] AND [scanDate] <= [EndDate] THEN "Keep" ELSE "Remove" END`

### Restrict filter options in a Quick Filter based on another Filter

Mark Jackson demonstrates a couple of options in http://community.tableausoftware.com/message/210455#210455

Can use Action Filter

### Find first month in a set of data

My response to http://community.tableausoftware.com/message/177996

which is based on: http://community.tableausoftware.com/message/168597#168597
workbook is running sum of count distinct.twbx

`IF TOTAL(MIN([Month of Install]))=ATTR([Month of Install]) THEN 1 ELSE 0 END`

Calculation uses TOTAL which doesn’t care about sorting/addressing, so it’s a lot more bulletproof.

Another one for last date:

As an alternative to LAST()==0 from Ross Bunker in http://community.tableausoftware.com/message/178251#178251

`ATTR([Order Date]) == WINDOW_MAX(ATTR([Order Date]))`

### Hiding certain items/dimensions in a % of total

For hiding certain items/dimensions in a % of total – e.g. PrimeCare/Non-PrimeCare/Null, just show % of total that is PrimeCare

1.     Create a calculated field called “Hide [Dimension]” with calc:

LOOKUP(MIN([dimension]),0)

results in “find my value” when the [dimension] is in the level of detail 2. Add that calc field to the level of detail 3. Create a Quick Filter on the calc field

Enjoy!

from TCC11 – JediTricks video

Using a set:
and attached workbook (upgraded so it will work with current data)

Using a simple IF statement:

A way to “filter” dates by using LOOKUP()
full_running.twbx workbook, simpler solution worksheet

Create a calculated field that is LOOKUP([Date],0) and make it continuous. Put it on the filter shelf for Range of Dates and leave it at that. When the user changes the slider, the range will change.

### Hide NULL values

http://onlinehelp.tableausoftware.com/v5.2/online/Output/wwhelp/wwhimpl/common/html/wwhelp.htm?context=Tableau_Online_Help&file=Tableau%2520Online%2520Help-29-24.html

### Dashboards and filters

http://vizwiz.blogspot.com/2011/08/tableau-tip-local-vs-global-filters-on.html

http://reports4u.co.uk/2011/06/10/tableau-dashboard-level-filter/

Robert Morton with a bit on when filters might not be able to properly compute the domain: http://community.tableausoftware.com/message/215056#215056

Data source filters are not applied directly to the data that is extracted, instead are added to each query. Post from Robert Morton: http://community.tableausoftware.com/message/227815

[loop category=”wikicontent” tag=”filtering,filters,filter”]
[field title] – Added [field date]
[content]
[/loop]

### Related posts:

[loop tag=”filter,filtering,filters,hide,hiding,LOOKUP()” exclude=”this” relation=”and” compare=”not” taxonomy=”category” value=”wikicontent”]