How to Avoid Dividing by Zero

[lindsey1]

When creating percentage measures, it is often useful to make sure you are not dividing by zero in the percentage calculation. This can be done through the SQL function NULLIF(expression_1, expression_2).

NULLIF takes two inputs. If the two expressions are equal, NULLIF returns a NULL. If the two expressions are not equal, NULLIF returns the value of the first expression.

So, in order to avoid dividing by zero in a dimension, we might use NULLIF(${field_name}, 0). This means "if ${field_name} is 0, give me a NULL, otherwise give me ${field_name}".

Here is an example of a percentage measure using NULLIF:

- measure: percent_sold 
  type: number                                 
  format: "%.2f%"                               
  sql: 100.0 * ${sold_count} / NULLIF(${count}, 0)