25 May 2015
One of the many great aspects of being a Tableau Consultant is that we can help clients answer challenging questions. We recently received a question: is searching for multiple strings in a single dimension while choosing if we wanted an 'AND' or 'OR' relationship between those items as well as adding a key phrase to exclude from the search. Practically think of this exercise as a multiple and customised wildcard match. While Tableau has excellent and flexible filtering capabilities, there is no canned solution for this requirement. What are we talking about exactly?- Searching on up to 3 key phrases- Full user control on whether the dimension contains 'all key phrases', 'any of them' or 'two at the same time or the third one', via logical operators- Optional exclusion of one key phrase
The wildcard match options support a single key phrase. There is a discussion in the Tableau community about applying regular expressions in a wildcard match value but as of now we have to find a different way.
To recap, entering this way a multiple value wildcard search phrase looks like the following:contains 'Phrase 1' AND/OR contains 'Phrase 2' AND/OR contains 'Phrase 3' AND exclude 'Phrase 4'
Don't worry now about why I channeled to formula's result to -50 or 100. I could have chosen any number, for our purposes I wanted the 'False' value of this formula (that is actually the 'True' value of the exclusion - so the Exclude phrase is NOT contained in the dimension member!) to be 'visibly' higher than the 'True' value. Nothing special so far, we are using the CONTAINS function to determine whether the Product Name dimension contains the Exclude phrase string. As usual, the example is based on the Superstore Sales data set, shipped with Tableau Desktop.Now we can move on to each of the four formulae reflecting the logical combinations between the search phrases.
You may have noticed that I also check if the key phrase is empty or not. In case of 'OR' conditions the lack of this check would return all the dimension members (all contain ''). Also, if all the 'contains' search phrases are left empty, that side of the search should return all the Product Names.It is time to combine these items to arrive to the final field we will rely on to determine whether a Product Name is displayed or not. Remember, in the boolean operator parameters 0 = AND, 1 = OR. The 1st boolean operator parameter connects the 1st key phrase and the 2nd key phrase. The 2nd boolean operator connects the 2nd key phrase with the 3rd key phrase (actually, the result of the 1st and 2nd with the 3rd). There is no 3rd boolean operator parameter required as the 'exclusion' is always 'added on top'.
It comes handy now that the four calculated fields (AND-AND-AND, etc.) are not boolean fields as those could not be used in a logical IF-THEN-END clause. Attaching numbers to the partial outcomes (the contain / exclude parts) and deriving a final True or False outcome supported his.
Finally, all the parameter controls have to be displayed in a sequential layout on a dashboard together with the view with Product Names. To test the solution, please click on the image below. I welcome any comments.
Background to the challenge
Before jumping into discussing the execution, we have to be aware that there are already similar solutions around in the community. I have found one from Ben Neville who built a custom wildcard match filter with up to three key phrases. The search logic between them is 'either-or'. What I worked on extends its scope.We at The Information Lab are also committed advocates of Tableau and grab every opportunity to pass on our understanding of the tool. As the solution in this post is built on wildcard match logic, let's check what is already available in Tableau!The wildcard match options support a single key phrase. There is a discussion in the Tableau community about applying regular expressions in a wildcard match value but as of now we have to find a different way.Alternative paths to the solution
What do we know in advance? We are dealing with Tableau, so:- everything is possible- there are multiple ways to achieve the objective- speed and user experience are two key aspects of the software so we keep this in mindTableau's wildcard match value entry can be mimicked by a string parameter and the CONTAINS(string, substring) function is just what we need during the search. The difference between two available alternatives is how the search phrases are entered.In method #1 the user is asked to enter all the key phrases as one parameter value. Search phrases could be separated by e.g. comma and special characters can indicate the logical relations between them. For example ''= OR, '&'=AND, '-'=Exclude. A search expression like: return every dimension member that contains 'Item' AND '32' OR 'System' excluding 'blue' would be entered like: Item, &32, System, -blue. Then this expression has to be parsed, also checking the first character of words, 'registering' their meaning and if '& / -' are present, trimming them. This process requires very complex string calculations that may hinder performance. Moreover, asking the users to remember to a list of special symbols and apply them to compile their search phrase is not exactly user friendly. So I rejected this route and chose method #2. There we create separate parameters for every key phrase as well for the logical operators to link them together. It speeds up development and easier to overview from the user's perspective.Detailed steps - building blocks
What are the 'ingredients' to this exercise? Take 4 string parameters (3 for search phrases to include, 1 to exclude a search phrase) and another 3 parameters to describe the logical operators (connections) between them. Actually, these last 3 parameters are just 2 as I defined the exclusion as always an 'AND' on top of the included key phrases. Logical operators are AND / OR though XOR (=exclusive OR) could also be included. That would be an interesting addition to this solution but for now I kept this simpler. Examples of the parameters: To recap, entering this way a multiple value wildcard search phrase looks like the following:contains 'Phrase 1' AND/OR contains 'Phrase 2' AND/OR contains 'Phrase 3' AND exclude 'Phrase 4'Detailed steps - calculations
This exercise is perfect to demonstrate the thinking process behind writing more complex Tableau calculations. The end result we strive for is a single calculated field that accomodates the combination of the above elements and filters for those dimension members where the overall calculation value is true. What are the combinations of logical relations among the 4 items of the total search phrase?1. AND + AND + AND2. AND + OR + AND3. OR + OR + AND4. OR + AND + ANDThe last AND is for the optional exclusion of the 4th search phrase. (To keep this simple, I omitted the XOR logical connection everywhere and the OR link between the 'contains' and 'excludes' parts of the multiple value search.)Other points to consider: how to deal with empty search phrases? How do we want the expression to be evaluated? We use brackets to make sure the OR/AND logical operations are evaluated from left to right.I hope you are still with me, I know it is high time to actually seeing some formulae... So let's start with the 'Exclude' formula linked to the 4th search phrase parameter I called 'Exclude phrase'.Don't worry now about why I channeled to formula's result to -50 or 100. I could have chosen any number, for our purposes I wanted the 'False' value of this formula (that is actually the 'True' value of the exclusion - so the Exclude phrase is NOT contained in the dimension member!) to be 'visibly' higher than the 'True' value. Nothing special so far, we are using the CONTAINS function to determine whether the Product Name dimension contains the Exclude phrase string. As usual, the example is based on the Superstore Sales data set, shipped with Tableau Desktop.Now we can move on to each of the four formulae reflecting the logical combinations between the search phrases.You may have noticed that I also check if the key phrase is empty or not. In case of 'OR' conditions the lack of this check would return all the dimension members (all contain ''). Also, if all the 'contains' search phrases are left empty, that side of the search should return all the Product Names.It is time to combine these items to arrive to the final field we will rely on to determine whether a Product Name is displayed or not. Remember, in the boolean operator parameters 0 = AND, 1 = OR. The 1st boolean operator parameter connects the 1st key phrase and the 2nd key phrase. The 2nd boolean operator connects the 2nd key phrase with the 3rd key phrase (actually, the result of the 1st and 2nd with the 3rd). There is no 3rd boolean operator parameter required as the 'exclusion' is always 'added on top'.It comes handy now that the four calculated fields (AND-AND-AND, etc.) are not boolean fields as those could not be used in a logical IF-THEN-END clause. Attaching numbers to the partial outcomes (the contain / exclude parts) and deriving a final True or False outcome supported his.Finishing steps for the complete, user-facing solution
Our work has not finished yet but we are almost there. In a view with the 'Product Name' field, the 'Search is True' field has to be placed on the Filters shelf with filtering for only the 'True' outcomes.Finally, all the parameter controls have to be displayed in a sequential layout on a dashboard together with the view with Product Names. To test the solution, please click on the image below. I welcome any comments.