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:
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.
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:
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:
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”:
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.
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.
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.
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.
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.
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)
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.
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.
Jonathan, really appreciate this blog and your contributions to the Tableau community. Do you think the approach you described above might be a way for me to generate a message for users when quick filters make a view disappear? More info at this recent post on the Tableau Forums site: http://community.tableausoftware.com/thread/130223.
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.
Thanks, Jonathan. I really appreciate your feedback, and your hack suggestion. I may need to see if it will work.
Pingback: Tableau Tips,Tricks,Best Practices - Sort/Filter - Jenny (Xiao) Zhang
Can I use the value which appears in the flag in another calculated field??
Hi Jonathan, I have a similar issue with this one. I want to show the reference line when all buildings are selected for the selected region. But building and region are not in the view but a quick filter. I did the same thing as you advised in this post, but doesn’t work to me. Any insights would be appreciated.
Hi, without knowing more about the data and your view I can’t answer your question, I’m sorry, Please post a link to a packaged workbook with sample data (for example a Tableau Public link) and I can take a look, thanks!