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:

9.1 discrete agg on filters

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:

9.2 discrete agg on filters

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:

9.2 discrete agg quick filter

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

Screen Shot 2015-11-22 at 9.30.31 AM

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:

9.2 no attr from primary on filters

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:

9.2 attr from secondary on filters

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).

 

14 thoughts on “Feature Geek: Filtering by Discrete Regular Aggregate Pills in v9.2

  1. 3danim8 (aka Ken Black)

    Your technique of using short descriptions coupled with the animated GIFs is a very nice technique for explaining these quick-hitting topics. I’m glad I’m done blogging because I definitely would have stolen from you. This issue has been a long time frustration for me, so I’ll be glad to see it vanish.

    Thanks and keep up the great work,
    Ken

    Reply
  2. Pingback: Feature Geek: How to Have Sets with Your Secondary (9.2 Style) via Aggregated Booleans | Drawing with Numbers

  3. Dave Weaver

    Thanks for the clear explanation.

    It appears that it is still not possible to use Actions to alter filters based on Discrete Aggregates. It is rather friction-inducing that I can display a quick filter on my Aggregate calculation which works great, but I can’t define an action do the same. Users say, why can’t I just click on that bar chart and have the other window filter based on my selection.

    Surely, this is a feature that is coming soon.

    Dave

    Reply
    1. Jonathan Drummey Post author

      Hi Dave,

      Filter Actions *can* use discrete aggregates as an origin but not a target, I’m guessing you mean the target. Level of Detail (LOD) Expressions that were introduced in v9 offer a potential workaround, if you use a FIXED LOD expression to compute the aggregate result that can be used as a dimension and therefore be the target of a Filter Action. I set up a quick example at https://public.tableau.com/views/lodexpressionforfilteraction/Dashboard?:embed=y&:display_count=yes&:showTabs=y. The histogram (grey bars) uses an aggregate measure that is computed as an LOD expression.

      Jonathan

      Reply
      1. Dave Weaver

        Interesting. Thanks for putting the example together so quickly. I may be able to rewrite my aggregate function as a FIXED LOD expression

        This may be getting into the weeds a bit here, but I have been toiling on this for a while and maybe my problem is relevant to others…

        I have built a waterfall chart, which compares two portfolios of investments (expenditures) under different ‘scenarios’ (A and B). The difference between the scenarios is broken down into a variety of categories, some investments may have gone up, some gone down, some cancelled, some new, etc. This is what I am calling category.

        My current structure has three(!) levels of LOD expression, Overkill no doubt, but it works and I’m scared to tweak it. It is further complicated because users can adjust date parameters that can change which categories an investment falls into, otherwise a lot of this could be pushed down into the data. This is further challenging because each bar of the waterfall chart is not actually based on a dimension, but uses a fancy INDEX() method to applya different calculation for each bar.

        /////////////////////////////////////
        // [Category]
        // Highest level. This is the field I want to be destination for an action filter
        //
        // FY = Exclude Fiscal Year, category is based on Aggregate across all FYs

        {EXCLUDE [FY] :
        // if these fields are non-zero, and only one of them can be non-zero, then that is the category
        if(MAX([2d. Cancelled Forecast]) > 0) THEN “Cancelled” ELSEIF
        (MAX([3d. Deferred Forecast]) > 0) THEN “Deferred” ELSEIF
        (MAX([4d. Lower Forecast]) > 0) THEN “Lower Forecast” ELSEIF
        (MAX([5d. Higher Forecast]) > 0) THEN “Higher Forecast” ELSEIF
        (MAX([6d. Accelerated Forecast]) > 0) THEN “Accelerated” ELSEIF
        (MAX([7d. New Forecast]) > 0) THEN “New” ELSE
        “No Change”
        END
        }

        each of the fields in the formula above look something the following example, lets look at Cancelled

        //////////////////////////////
        // [Cancelled Forecast]
        // the include clause is needed, because the condition always needs to be evaluated at the investment/expenditure level
        {INCLUDE [PortfolioExpenditureID]:
        IF
        (ISNULL(SUM([Forecast B total]))) // if Forecast B is zero in all years then…
        then
        SUM([Forecast A, within range])
        else
        NULL
        end
        }

        /////////////////////////////////
        //the field [Forecast B total] is also a LOD expression.
        {EXCLUDE [FY]: SUM([Forecast B])}

        So to convert that first formula to FIXED may be possible. I will think about this…

        Dave

        Reply
      2. Dave Weaver

        I changed that first formula to FIXED, and I am getting correct results. But it is still not avilable as a destination filter for Actions

        {FIXED [Portfolio], [Expenditure]:

        if(MAX([2d. Cancelled Forecast]) > 0) THEN “Cancelled” ELSEIF
        (MAX([3d. Deferred Forecast]) > 0) THEN “Deferred” ELSEIF
        (MAX([4d. Lower Forecast]) > 0) THEN “Lower Forecast” ELSEIF
        (MAX([5d. Higher Forecast]) > 0) THEN “Higher Forecast” ELSEIF
        (MAX([6d. Accelerated Forecast]) > 0) THEN “Accelerated” ELSEIF
        (MAX([7d. New Forecast]) > 0) THEN “New” ELSE
        “No Change”
        END

        }

        Reply
      3. Dave Weaver

        Got it working. Thank you so much for the tip to look at FIXED. I eventually tracked down every formula and found one it didn’t like. Once I FIXED it, it all worked. It’s such a good feeling when it finally works!

        Reply

Leave a Reply to Matt Lutton Cancel reply