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:
- Right-click on Product Type from Dimensions and click on Create Calculated Field.
- In the Name box, enter
Coffee or Tea. - In the Formula box, enter the
CASE [Product Type] WHEN 'Coffee' THEN 'Coffee' WHEN 'Espresso' THEN 'Coffee' WHEN 'Herbal Tea' THEN 'Tea' ELSE 'Tea' ENDformula and hit OK. - To use
0instead of missing values of Sales, right-click on Sales from Measures and click on Create Calculated Field. - In the Name box, enter
Non-missing Sales. - In the Formula box, enter
ZN([Sales])and hit OK. - To see Total Expenses in some categories, right-click on Total Expenses from Measures and click on Create Calculated Field.
- In the Name box, enter
Expensive Type. - In the Formula box, enter the
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' ENDformula.
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.