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 ( \ ).