Tableau provides various logical functions, such as CASE
, IF
, and IIF
, to create calculated fields based on some conditions. In this recipe, we will create and see the use of some of these logical functions.
Let's use the sample file Sample – Coffee Chain (Access)
. Open a new worksheet and select Sample – Coffee Chain (Access) as the data source.
Once the data is loaded on the worksheet, perform the following steps to create calculated fields based on conditions:
Coffee or Tea
.CASE [Product Type] WHEN 'Coffee' THEN 'Coffee' WHEN 'Espresso' THEN 'Coffee' WHEN 'Herbal Tea' THEN 'Tea' ELSE 'Tea' END
formula and hit OK.0
instead of missing values of Sales, right-click on Sales from Measures and click on Create Calculated Field.Non-missing Sales
.ZN([Sales])
and hit OK.Expensive Type
.IF [Total Expenses] <= 49.99 THEN 'Cheap' ELSEIF [Total Expenses] >= 50 and [Total Expenses] < 100 THEN 'Somewhat Expensive' ELSEIF [Total Expenses] >= 100 and [Total Expenses] < 150 THEN 'Slightly Expensive' ELSE 'Very Expensive' END
formula.The CASE
and IF
functions are similar in that they both allow testing of an expression and returning values on various conditions. The CASE
function is usually easier to read and is usually the preferred way of testing expressions. The IF
function allows us to test on numeric conditions whereas the CASE
function doesn't allow that; for example, we cannot write a CASE [Profit] < 100
condition, but we can write IF [Profit] < 100
. The IFNULL
function is very useful when we want to return any value (numbers in case of numeric expressions and a string in case of string expressions) if the expression is null, and the ZN
function is useful when we want to return 0
if the expression is null.