IIF - Conditional Logic

BQL expressions can include if-then-else logic using IIF.

A typical use case is to divide two base measures and to guard against division by zero. For example, to calculate a debt/asset ratio and return zero when the denominator is zero:

IIF([asset_measure]=0,0,[debt_measure]/[asset_measure])

The expression is read as "IF [Asset] equals 0 THEN 0 ELSE [Debt]/[Asset]".

If-then-else expressions can also be nested. For example:

IIF([x]=0,0,IIF([x]<0,[y]/[x],[z]/[x]))

An expression can look for instances where an aggregated number returns a value that is not a number (for example, NULL or some other value that is not a legal number) and replace it with zero, using the following syntax:

IIF(isNaN([y]/[x]),0,[y]/[x])

If the output includes single quotes, escape them using a backslash ( \ ).