Looker Community

How to Avoid Dividing by Zero

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) 
1 Like