Not everyone is a Tableau guru, at least not yet. To help Tableau rookies, we’re starting from square one with the Tableau Essentials blog series. The series is intended to be an easy-to-read reference on the basics of using Tableau Software, particularly Tableau Desktop. Since there are so many cool features to cover in Tableau, the series will include several different posts.
Not everyone is a Tableau guru, at least not yet. To help Tableau rookies, we’re starting from square one with the Tableau Essentials blog series. The series is intended to be an easy-to-read reference on the basics of using Tableau Software, particularly Tableau Desktop 8.1 and 8.2. Since there are so many cool features to cover in Tableau, the series will include several different posts. This is the tenth post in the series. Check out the full list on our Tableau Essentials blog channel. This is also the second post in the Calculated Fields sub-series.
We’re jumping back into calculated fields. Be sure to check out our introduction before digging into this one. Today, we’re revisiting that long list of Tableau functions. We’ll start with Logical Functions, and then we’ll continue on to the other function groups.
Let’s go back to the Calculated Field window:
Go to the Functions table and select Logical from the drop-down menu:
This selection will filter the function list for only the logical functions:
We’re left with seven different functions.
CASE expression WHEN value1 THEN return1 WHEN value2 THEN return2 … ELSE default return END
The CASE function is used to perform a logical test and return values based on the result. CASE functions can be rewritten as an IF function, but the CASE function is often times simpler to write and shorter.
Here’s an example of a formula:
This CASE function creates a new field by looking through the country field. When the value “United States” is found, it uses “USA.” When “United Kingdom” is found, it uses “UK.” All other values receive the value “World.”
IF test THEN value END / IF test THEN value ELSE else END
The IF function creates a logical test. IF test is true THEN do y. The test part of the function must be Boolean, either by using a Boolean field in the data source or as the result of an expression using operators or logical comparisons (AND, OR, NOT).
Here’s an example:
IF test1 THEN value1 ELSEIF test2 THEN value2 ELSE else END
You can expand the functionality of the IF function with the ELSEIF by adding additional IF-THEN statements. For example, here we can recreate our CASE formula above using an IF-THEN-ELSEIF statement:
Same result as our CASE function, grouping all other values into World and abbreviated the USA and UK.
The IFNULL function basically runs a true/false test on whether the value in the tested field is NULL. The first value in the function is used if the value is not null, and the second is used if it is null.
If any country’s Total Population does not have a value, then it will be reset as zero and removing the null from the newly created field.
IIF(test, then, else, [unknown])
The IIF function is very similar to the IF function above. It essentially creates a shorthand function for an IF-THEN-ELSE statement with the added benefit of being to define a value with the final argument if the test yields an unknown result. Remember, just like the IF function, the test must be Boolean by data type or result in a Boolean value as a result of the test.
Let’s take a look at an example:
If the field % of Change is less than 5% then my formula above will return a value of Poor, otherwise it will return Good.
The ISDATE function tests whether the string argument can be converted to a valid date (TRUE) or if it cannot (FALSE). Here’s an example:
The above formula is approved by Tableau because the field that we used is indeed a string data type, but every result will obviously be FALSE. You might use this function if your date is formatted in a style you might not recognize, such as ISO 8601. Here’s an example:
The above value represents September 1, 2014 and indeed is a valid value for a date.
Another simple function, ISNULL, simply tests whether an expression is null (TRUE) or not (FALSE).
If you want to filter null values from your visualization, you can filter null values with the Filter card.
The last logical function is the ZN function. It is a variation on the ISNULL and IFNULL function. ZN tests to see if a function is null, and if it is, it will return a value of zero.
The first time you see a list of logical functions, there’s the inevitable hesitation on what all of this means. Now that we’ve gone through each one, hopefully you’ll find them useful in leveraging your data and your visualization even more than before. If you want to jump in and learn more about when to use these functions, here’s a great post.
Calculated fields can add a whole new layer of insight to your Tableau dashboards. The possibilities are practically endless, but we’ll be covering the fundamentals, especially functions, to help you build a foundational understanding of how and when to use them. Check back for more posts covering:
- Logical Functions
- Number Functions
- Date Functions
- String Functions
- Type Conversion
- Aggregate Functions
- User Functions
Another great resource for functions is Dan Murray’s best-selling guidebook, “Tableau Your Data!” It features a whole section devoted the functions we’ll be covering in this series and much, much more.
More Tableau Essentials
Want to learn more about Tableau? We have several posts outlining all of Tableau’s fantastic features. Check out the full list on our Tableau Essentials blog channel.
As always, let us know if you have any questions or comments about this post or Tableau in general. If you’re looking for personalized training or help with something bigger, contact us directly!