Tableau Essentials: Calculated Fields – Logical Functions

Data

Tableau Essentials: Calculated Fields – Logical Functions

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:

Calculated Field window 

Go to the Functions table and select Logical from the drop-down menu:

Functions table 

This selection will filter the function list for only the logical functions:

Logical functions

We’re left with seven different functions.

CASE Function

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:

Example 1

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 Function

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:

Example 2

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:

Example 3

Same result as our CASE function, grouping all other values into World and abbreviated the USA and UK.

IFNULL Function

IFNULL(expression1, expression2)

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.

Example 4

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 Function

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:

Example 5

If the field % of Change is less than 5% then my formula above will return a value of Poor, otherwise it will return Good.

ISDATE Function

ISDATE(string)

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:

Example 6

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:

Example 7

The above value represents September 1, 2014 and indeed is a valid value for a date.

ISNULL Function

ISNULL(expression)

Another simple function, ISNULL, simply tests whether an expression is null (TRUE) or not (FALSE).

Example 8

If you want to filter null values from your visualization, you can filter null values with the Filter card.

ZN Function

ZN(expression)

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.

Example 9

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

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:

Introduction

  1. Logical Functions
  2. Number Functions
  3. Date Functions
  4. String Functions
  5. Type Conversion
  6. Aggregate Functions
  7. 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!

 

Want More Tableau Essentials

  1. Tableau Essentials: Chart Types – Introduction
  2. Tableau Essentials: Chart Types – The Text Table
  3. Tableau Essentials: Chart Types – Heat Map
  4. Tableau Essentials: Chart Types – Highlight Table
  5. Tableau Essentials: Chart Types – Symbol Map
  6. Tableau Essentials: Chart Types – Filled Map
  7. Tableau Essentials: Chart Types – Pie Chart
  8. Tableau Essentials: Chart Types – Horizontal Bar Chart
  9. Tableau Essentials: Chart Types – Stacked Bar Chart
  10. Tableau Essentials: Chart Types – Side-by-Side Bar Chart
  11. Tableau Essentials: Chart Types – Treemap
  12. Tableau Essentials: Chart Types – Circle View
  13. Tableau Essentials: Chart Types – Side-by-Side Circle View
  14. Tableau Essentials: Chart Types – Line Charts (Continuous & Discrete)
  15. Tableau Essentials: Chart Types – Dual-Line Chart (Non-Synchronized)
  16. Tableau Essentials: Chart Types – Area Charts (Continuous & Discrete)
  17. Tableau Essentials: Chart Types – Scatter Plot
  18. Tableau Essentials: Chart Types – Histogram
  19. Tableau Essentials: Chart Types – Box-and-Whisker Plot
  20. Tableau Essentials: Chart Types – Gantt Chart
  21. Tableau Essentials: Chart Types – Bullet Graph
  22. Tableau Essentials: Chart Types – Packed Bubbles
  23. Tableau Essentials: Formatting Tips – Introduction
  24. Tableau Essentials: Formatting Tips – Custom Shapes
  25. Tableau Essentials: Formatting Tips – Labels
  26. Tableau Essentials: Formatting Tips – Color
  27. Tableau Essentials: Formatting Tips – Tooltips
  28. Tableau Essentials: Formatting Tips – Maps
  29. Tableau Essentials: Calculated Fields – Introduction
  30. Tableau Essentials: Calculated Fields – Logical Functions
  31. Tableau Essentials: Calculated Fields – Number Functions
  32. Tableau Essentials: Calculated Fields – Date Functions
  33. Tableau Essentials: Calculated Fields – String Functions
  34. Tableau Essentials: Calculated Fields – Type Conversion
  35. Tableau Essentials: Calculated Fields – Aggregate Functions
  36. Tableau Essentials: Calculated Fields – User Functions

More About the Author

Carly Capitula

Principal / Enablement Practice Lead
A Quick Recap of the 2017 DRIVE/ Conference Last week, some of the InterWorks team dropped by Seattle, WA for DRIVE/ 2017 – a conference for professionals in fundraising and ...
Tableau Viz Showcase by Industry: K-12 Education “Data-driving decision making” is a phrase heard in several industries, perhaps none more so than in education. In such a scrutinized ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK