KNOWLEDGE BASE

Unable to Create a Calculated Field that Does Not Contain Specific Strings


Published: 15 Jun 2017
Last Modified Date: 15 Jun 2017

Issue

You are unable to create a calculated field that does not contain specific letters or numbers in a specifc Strings. 

For example, using the Superstore sample data, you would like to look at the Sales & Profit data per Manufacturer, but you wish to filter some Manufacturers that contain the letter O. 

Environment

Tableau Desktop

Resolution

Step 1: Create a parameter

  1. Click the drop down arrow located next to the Dimensions panel. 
  2. Select Create Parameter...
  3. Name the parameter, in this example "Does not Contain".
  4. In Allowable Values, select List, then add the list of values that you wish to exclude.
    • In this example, we've added the letters A, C, O, X etc. (based on the Manufacturers names). 
  5. Click OK
  6. Right click the parameter and select Show Parameter Control

Step 2: Create a calculated field

  1. Select Analysis > Create Calculated Field...
  2. Name the calculated field, in this example "Does NOT Contain"
  3. Enter a formula similar to the following: 
    IIF (contains [STRING] [PARAMETER],FALSE,TRUE)
  4. In this example, we've used: 
    IIF(CONTAINS([Manufacturer],[Does not contain]),FALSE,TRUE)

Step 3: Use the Calculated field

  1. Drag the calculated field to the filter shelf and tick:
    1. True to exclude the strings that does not contain the value you specified in your parameter. 
    2. False to show only the strings that contain the value you specified in your parameter. 
  2. For example, in the attached workbook. Selecting the letter O (in the parameter) and True in the filter will exclude the following Manufacturers because they all contain the letter "O": 
    • Other
    • SAFCO
    • Office Star
    • O'Sullivan
    • Okidata
    • Office Impressions
    • ClearOne
    • OtterBox
    • TOPS
    • REDIFORM
    • iOttie
    • ACCOHIDE
    • OIC

Additional Information

The parameter is case sensitive. 
Did this article resolve the issue?