IF() vs. IIF()
The IF() function is a simple version of the IIF() function. The key difference between the two is the IIF() function provides users an option to return a specific value in the event the test expression doesn?t return either TRUE or FALSE, i.e. is null or unknown.
The IF function takes the following form (with optional additional ELSEIF statements): IF <test> THEN <return something> ELSEIF <return something else> ELSE <return something> END
Consider the following calculation: IF A THEN B ELSE C END
What happens if A is unknown or NULL? The calculation will return C because it didn?t evaluate to A.
The IIF() function can take two forms:
- IIF(test, then, else)
- IIF(test, then, else, unknown)
If you don?t specify the return argument, a ?Null? would be returned for any NULLs or Unknown records. IIF() gives users the flexibility to decide how to handle NULLs, i.e. IIF(A,B, C, D) would return D if A is unknown or NULL.
In order to properly handle NULLs in the IF() function you?d have to wrap your test in an ISNULL() function and modify the rest of the IF statement accordingly, which results in a more abstruse calculation for users. The IIF statement has a more simple syntax, but it could also be less readable, in particular if you need more complex logic and need to nest additional IF or IIF statements into your calculation.
CASE()
The CASE() function is similar to the IF() statement and is very easy for a user to understand. However, it has an important difference from the IF() function: you can?t evaluate boolean expressions in the case statement tests. For example, the following calculation won?t work:
CASE [Profit] WHEN > 0 then ?Profitable? ELSE ?Unprofitable?END
Final verdict
There is old information on the Tableau forums that CASE() is faster than IF(), but that distinction has virtually gone away with new releases of the product. The key differentiators between these methods are boolean tests, null values, and readable. Here?s my reference.
First choice ? use CASE() if you don?t need to perform boolean comparisons in your test argument and your logic is simple
Second choice ? use IF() for more complex logic or don?t need to handle NULLs or are comfortable accurately including the ISNULL logic in your calculation.
Third choice ?use IIF() for simple logic/calculations and need to handle NULL values.
