Screen Shot 2013-05-30 at 11.16.16 PM

Identifying (and Using) Quick Filter Selection Status

This post on the Tableau forums led me to figure out a new trick with Quick Filters. The goal is to know whether a Quick Filter has (All) Customers selected or some subset of Customers, then return a different measure based on that flag. Just to make it a little more fun, we want this calculation to work when there are other filters present, or not, to look something like the following view based on the Superstore Sales data:

Screen Shot 2013-05-30 at 11.36.17 PM

Tableau does not give us visibility into the Quick Filter status, however for discrete dimensions we can deduce that via other calculations. The method I came up with looks at how many members of the dimension are remaining, i.e. how many Customers have been selected, and compares that to the total number of customers. If the two numbers are the same then (All) customers must be selected, and with that information then we can choose which measure to present.

This particular solution requires a data source that supports COUNTD(), if you’re using Excel, Access, or a text file then you’ll need to create a Tableau data extract from your data.

For this example, the Quick Filter is on Customer, a discrete dimension. Tableau processes discrete dimension filters in the query to the data source, so after that data is returned to Tableau we have no way of knowing how many distinct customers there are in total. One way around this would be to separately calculate the total number of customers and hard-code it via a calculated field or parameter, but that would be static and need to change every time a new customer was added. An alternative would be to compute that total in a subquery that would be part of the data source, but it would be really hard to make that work are other filters in the view. Plus, if I solved it outside of Tableau then I wouldn’t have had much reason for this blog post!

So, where can we get a source of data that has the total number of customers before the Customer filter is applied, but does include other filters, like a filter on Customer Segment? The answer is to use a self-join via a Tableau data blend. The reason why this can work is that we can control the relationships in the data blend, and therefore where filters are applied.

When the dimension being filtered is in the view (on Rows, Columns, Pages, or Marks Card) and has a an active relationship (in v7, having a defined relationship, in v8 having a defined relationship and the linking icon turned on) Tableau will filter both the primary and secondary. In all other cases, Tableau will only filter the primary and not the secondary. Here’s a handy matrix:

Screen Shot 2013-05-30 at 11.39.16 PM

One bit of caution, in v8 through version 8.0.1, the linking field status is not respected for Date fields, I covered that in this post on blending.

By turning off the relationship on Customer between our primary and secondary data sources, when we filter on Customer only the primary gets filtered, leaving the total set of Customers we can count for our test. To remove that relationship, in version 7 go to Data->Edit Relationships…, then in the Relationships dialog click on the Custom radio button to delete the Customer relationship:

Screen Shot 2013-05-30 at 10.49.15 PM

And by leaving the relationship in place on Customer Segment, then both the primary and secondary are filtered on Customer Segment, as in the following view:

Now we have the working totals and need to build the calculation to test whether (All) is selected or not. Since we want to have the calculation work across all the dimensions in the view, we resort to table calculations. Here’s the calculation formula, called “All Customer Flag”:

TOTAL(COUNTD([Customer]))==TOTAL(COUNTD([2].[Customer]))

This has an Advanced Compute using of every dimension in the view in order to address on every dimension and therefore have only one partition (all data in the view). For the following view, that will be Customer Segment, Order Date, and Region. In this case, since we’re using TOTAL() the order doesn’t matter.

Screen Shot 2013-05-30 at 10.52.26 PM
Screen Shot 2013-05-30 at 10.53.33 PM

In the worksheet below, you can filter Customer Segment and the status of the All Customer Flag won’t change, but when you filter out any Customers the flag will change:

On to the measure… For this example, what if we don’t want to aggregate the final view by Customer Segment, and instead just want it to be a filter? In other words, the view is at a finer level of granularity than we need. To deal with this situation, we again turn to a table calculation, called Adjusted Sales:

IF FIRST()==0 THEN 
    IF [All Customer Flag] THEN
        TOTAL(SUM([Sales])) 
    ELSE 
        TOTAL(SUM([Sales])) * 1.2
    END
END

This is a nested table calculation. The IF FIRST()==0 part reduces the number of records returned. Then the IF [All Customer Flag] returns the total sales when all Customers are selected, and the total sales plus 20% when only certain customers are selected. The Compute Using for the nested Adjusted Sales is set to Customer Segment, so it partitions on Region and Order Date.

Screen Shot 2013-05-30 at 11.26.01 PM

Now you can see this in the view, I added a couple of calculations and subtotals to show how the Adjusted Sales matches the Sales from Primary when all Customers are selected, and matches the Sales from Primary +20% when fewer customers are selected.

After that, we can turn that into the final view by moving Customer Segment onto the Level of Detail, getting rid of the validation calcs, and putting a copy of the Adjusted Sales calc on the Filters Shelf to filter for non-Null values. This can speed up the view when there are a lot of marks, and is useful in version 8 to avoid unwanted mark stacking.

In the Tableau Public workbook above, click over to the Final View tab to see it all in action.

These instructions and this layout will work in both Tableau version 7 and version 8. However, in Tableau version 8, this solution will fail if you are using linking dimensions that are not in the view – you have to stick to this technique that uses version 7 and earlier style blending (called “Data Blending 1”). I’ll cover the reasons why in a blog post next week.

11 thoughts on “Identifying (and Using) Quick Filter Selection Status

  1. Matt Lutton

    I’m attempting to learn all I can from your posts. I am having difficulty grasping all that is going on here, but in the “Setting Up Customer Flag” portion, if any customer segment is not checked the all customer flag is always set to TRUE, even if you uncheck one or more customer name. Is this appropriate? I may need to study this a bit more to truly understand it, but I appreciate all the help you are providing the Tableau community.

    Reply
  2. Matt Lutton

    Hmm… forget my last comment. I could’ve sworn this was occurring a moment ago, but now it is working the way you described. Sorry for the confusion.

    Reply
  3. Matt Lutton

    OK, what I originally described does happen for me with SOME customers only. For instance, if I uncheck the “corporate” segment, and then uncheck “Adam Hart”, the flag stays TRUE. Sorry for multiple posts (feel free to delete them later)

    Reply
  4. Jonathan Drummey Post author

    Hi Matt,

    This is because the Quick Filter on Customer is set up to Show All Values. Adam Hart only has sales in the Corporate Customer Segment, so when we filter out Corporate, he has no sales but is still showing up in the Quick Filter. Based on some quick testing, it seems like you can turn on Show Only Relevant Values for the Customer Quick Filter and everything would still work as designed, and then there wouldn’t be the case where a customer like Adam Hart could be “filtered out” with no apparent effect, because he wouldn’t even show up in the Quick Filter to be filtered out.

    Reply
    1. Matt Lutton

      Ah, of course. That makes sense.

      You may see me ask a lot of seemingly simple questions, as I am a novice and have been tasked with learning as much as possible, as quickly as possible! Appreciate all your help and insight.

      Reply
  5. Jonathan Drummey Post author

    Hi Mike, there’s no way I know of at this time to get your desired result. When the quick filter selection results in no rows returned from the data source, then Tableau doesn’t perform any additional computations, so this technique for identifying the quick filter status wouldn’t work at all.

    I can imagine a hack in an embedded viz where the dashboard would be in an iframe that was slightly smaller than the actual dashboard, and collapsing worksheets in a vertical layout container would cause a normally out of sight (outside the boundaries of the iframe) message to be displayed, and that requires special circumstances to work.

    Reply
    1. Mike C.

      Thanks, Jonathan. I really appreciate your feedback, and your hack suggestion. I may need to see if it will work.

      Reply
  6. Pingback: Tableau Tips,Tricks,Best Practices - Sort/Filter - Jenny (Xiao) Zhang

Please add your thoughts and perspectives