In today's blog I will go through the syntax for logical calculated fields in Tableau.
Basic if (IIF)
Starting first with the immediate if function (IIF) which is useful for a simple logical case.
We can simplify:
To
Standard If and Extension with Elseif
Also includes explanation of AND, OR, NOT
The standard if is useful because it can be expanded to be more complex - we can use AND to set two or more test conditions where all must be satisfied, OR to test on two or more conditions but that satisfying 1 is enough to return true. Not can be used to reverse a test.
We can expand a standard "if" clause by using "elseif" to introduce a second test statement (and we can keep adding elseifs) that occurs after the original. In other words any instance that does not satisfy the first case is tested against the second statement. Like a waterfall each instance is sequentially tested against each statement.
Case and In
Case is an alternative approach to logical questions - it is less flexible than an if function because we are limited to an = operator. In If functions we can use >,<, >=. Case is most useful for renaming fields.
This is a good opportunity to mention that IN can be used to streamline syntax as seen in the example below:
First the case syntax is slightly different we would read this as read the State/Province column; when it is x then return y. IN here allows us to list multiple options and saves writing:
Returning to the case example: for column "State/Province" when the value is either "Ontario", "Alberta" or "Quebec" return "Selected Canada".
Ending with some important pointers:
- These statements need to be ended clearly with END.
- Case is most useful for instances of renaming.