Tableau Logical Functions: IF, IIF, IFNULL

Have you ever had the situations when you would like to add some conditions as a text or as a number in one separate column? And you got stuck on it. The solution is “IF”, “IIF”, and “IFNULL” functions.

IF

With the function IF you can add three or more conditions to your calculation:
IF test1 THEN value1 ELSEIF test2 THEN value2 ELSE else END

EXAMPLE

IF SUM([Profit])>5000 then "Profit"
ELSEIF SUM([Profit])<4999 and SUM([Profit])>2000 then "Breakeven"
ELSE "Loss"
END

Note!
If you create this formula, use exact the aggregation in the text field. In the example above the aggregation is SUM([Profit]), thus I used this aggregation in the formula and NOT just [Profit].

IIF

If you want to run a calculation with TWO conditions, e.g. :
If I have “XY”, then give me “Z”, otherwise give me “AB” (two conditions!), then use the function IIF.

EXAMPLE

Assume, we have this data set:

I would like to calculate the Conversion Rate (CR) for Downloads, e.g. mum of all download activities divided by sum of all sessions on the Page1, Page2 and Page3.

In order to do this I created this calculation field:

sum(IIF([Activities]="Downloads", [# Activities], NULL))
/
(
sum(IIF([Activities]="Page1", [# Activities], NULL))+
sum(IIF([Activities]="Page2", [# Activities], NULL))+
sum(IIF([Activities]="Page3", [# Activities], NULL))
)

With this operation I said Tableau: 

"Filter all numbers of activities if the "Activities" = "Download" and divide this sum by the total sum of Page1, Page2 and Page3." 

IFNULL

If you have data set with empty fields and you would like to fill this empty fields with other values, then use the IFNULL function.

EXAMPLE

I have this data set:

I would like to have “2” in each empty row. So, I created this calculation field:

IFNULL([Email Permissions],2)

And this is the result

Hope it helped...

Check out this link for more information.

Cheers!