Change Filters From "AND" to "OR"

 

THE PROBLEM


At work a few weeks ago, my colleague, Ethan Hahn, and I were presented with a problem. He was putting together some data and building a dashboard for our Marketing department. It contained a list of customers and their corresponding metrics. He wanted to provide the marketing team with a tool that allowed them to build a customer list based on two options. He applied two filters and starting testing his dashboard. The problem was that when options in each of the two filters were selected, the filters worked together to provide the options in filter 1 AND the options in filter 2. However, he wanted to provide the end user with the ability to utilize OR versus AND. He had a number of possible solutions, but we ultimately landed on one that I thought was worth sharing with the community.



THE EXAMPLE AND DATA


I think the above paragraph requires some additional clarification. We will do that with a very hypothetical example.

The Flerlage name is somewhat rare, but in Cincinnati, Ohio, a group of Flerlages used to own the Flerlage Marine where they sold boats. They have since sold the business and it is now known as Sea Ray of Cincinnati. However, in this example, I am going to act as if I am a Tableau developer for Flerlage Marine and my marketing department wants to do an ad campaign for high-end boats. The details of which are not yet determined. My goal is to provide them with a tool that allows them to target the right audience.

For this, I've built a sample marketing data set, which looks like the following:







The sample data set contains just 200 names as a test (customers and non-customers) as well as Household Income and the Value of Their Home (both in thousands). It also contains information stating whether or not the person was a previous customer.

I've also created a sample viz. It contains filters and a simple table as well as a BAN to show how many customers are in that resulting list. It looks like the following:






Okay, remember that we are working toward a marketing campaign for high-end boats. So let's play with the filters to trim down the list to our best customers. Let's just get our best customers and choose the highest two values for both Household Income and Value of Home (let's ignore the third column for now).





When we do this, we get just 3 customers out of our list of 200. That would make a pretty terrible marketing campaign. But what do we see? Well, we see customers with household incomes greater than $250k AND home values of over $400k. To risk sounding repetitive, that's household incomes greater than $250k AND home values of over $400k. I'm not sure that we often think this deeply about our filters, but as we change the selections in multiple filters, all criteria must be met for the value to show in the resulting dataset. It means that it must meet the criteria for filter 1 AND for filter 2 AND for filter 3, etc.

So let's think logically about our example. If a person on our list has a $500k home, but we have no information about their income (Unknown), wouldn't we still be interested in including them in our campaign? Conversely, what if a person had a $300k annual income, yet lives in a smaller home? Again, I think we'd want to include that consumer. But how do we do it using normal filters?

Well, for the first example ($500k home / unknown income), you may say "let's just check the "Unknown" box for income.





Well, that certainly added a couple of people to the list...all people we want to include. Now, let's address the second part ($300k annual income, yet lives in a smaller home - we will define that as less than $200k). I' went ahead and checked the boxes and we get the result below:






Again, we see lots of consumers we want on the list, but there are a number of issues. Take a look at third on the list, Brendan Cremin. His income and home value are both unknown. He is included because we checked Unknown for Income and Unknown for Home Value. But I wouldn't want him on our list. And what about seventh on the list, Dorris Williamson? We know nothing about her income and she lives in a home that is less than $100k in value. Dorris is probably an amazing person, but she is unlikely to purchase a high-end boat based on the data we have about her. Again, she is included because she meets the criteria in the first filter AND the criteria in the second filter.



THE SOLUTION

So in the above example, we added values to each side to try to get our ideal customers, but it failed. So how do we do it? How do we include the person with a $500k home, but an unknown income as well as a person with a $300k annual income, yet lives in a smaller home? The answer all comes down to the AND that I capitalized throughout this entire blog post so far. We change that AND to an OR. This way, if they have a high income, it will be included regardless of their home value. And similarly, if they have a high home value, they will be included regardless of their income. And this is exactly what we want! (Note, we will give the user the ability to utilize AND or OR in their filters).

Okay, so how do we get the filters to do this? Well, you don't. Unless there is some crazy hack out there, filters don't work with OR...they are all the way AND. So we will utilize set controls instead. For more information on Set Controls, check out my set controls blog post.

Okay, first, let's create one set for Household Income and another set for Home Value. Just right-click on the Household Income pill, choose Create, then choose Set. In the pop-up window, name the set "Household Income (k) Set", select all values and click OK. Do the same for Home Value.

Now, let's create a string parameter and add two values to the list: AND and OR. This will allow the user to determine if they want the "filters" (actually set controls) to act with AND or OR.

Now, let's create a calculation to be placed on the filter shelf. This calculation will allow you to determine if the values are in each set and also evaluate if the user wants the result to fit both set controls (AND) or just one or the other (OR).


Set Filter
// Look at each set
// Look at the parameter (and / or)
// Then determine what is in the set and/or the other set

IF [AND / OR] = 'AND' THEN

IF [Household Income (k) Set] AND [Value of Home (k) Set]
THEN 'SHOW'
ELSE 'HIDE'
END

ELSE

IF [Household Income (k) Set] OR [Value of Home (k) Set]
THEN 'SHOW'
ELSE 'HIDE'
END

END

Okay, what does this calculation do? Well, it first evaluates the parameter. If that parameter is set to AND then the first IF/THEN is utilized. Check out the calc, it says "IF [Household Income (k) Set] AND [Value of Home (k) Set THEN 'SHOW'". So this is saying if it is in both sets, then show, else hide. If OR is selected in the parameter, then the calc is the same except the two sets are separated with an OR. So this is saying, if the value is in one set or the other, then show, else hide.

On your table sheet, add this filter calculation to the filter shelf and check the "SHOW" box only. Right click and apply to all worksheets using the data source.

Next, add both of the sets to the detail card. Right click on each (from the detail card) and choose "Show Set". This is a set control. And if you read my linked blog post about set controls, checking a box will add a value to a set.

All of this has already been completed on the "OR Filter" dashboard of my sample viz. On that page, I show the two set controls at the top (they look like filters) separated by my AND/OR parameter. Now, let's give it a try!

So let's try the first example where in our original setup, we yielded just three customers. This was accomplished by selected the highest two values in each filter:






Now let's do it using our OR filter set up. To confirm things are working correctly, let's set the parameter to AND and then check the highest two values for each set control. If we did it correctly, it should yield the same 3 customers. In the following images, I'll show the AND filters and the OR filters side by side.






Okay, now let's change the parameter to "OR".







Wow, there are nearly 10 times as many results! If you inspect the results data set, you'll see the 3 from the first list, but also see results where a consumer met one criteria OR the other, but not both. Let's try it with the highest three options in each filter, which yielded 16 consumers in our initial setup.







WRAP-UP

There is a lot that you can do with this concept. On top of "AND" and "OR", you could add an option in your parameter to utilize the options in one set control but ensure that it's not in the other one. For example, you may choose to have a home value of $300k+ but NOT include any homes of $150k or less. You could do this with the current set up as well, but with different data sets, providing an option to not be in the set could provide very useful.

Or, we could accomplish something similar to the above using three set controls instead of two. This would allow you to build out some pretty complicated logic. In my original data, I included a field called "Previous Customer" with values of YES and NO. We could add a second AND/OR parameter and build out a calculation to work with all three. That calculation would be a bit lengthy as you would need to account for all combinations (and you can't feed in the parameter values of AND/OR as operators, unfortunately). But you can see how valuable this could be.

Note: I had not seen it until this blog post was published, but wanted to give credit where credit was due.  Samuel Epley actually built this same exact logic for Workout Wednesday last year.  Check out the blog post and challenge here.  Really cool stuff, Samuel!

Okay, that's it for now. Again, if you want to see all of this in action, please check out my sample viz on Tableau Public. Thanks so much for reading and please reach out if you have any questions or comments. Thanks!

Kevin Flerlage, October 12, 2021

Twitter | LinkedIn | Tableau Public