Survey banner
The Dataiku Community is moving to a new home! We are temporary in read only mode: LEARN MORE

Handling 0 in Denominator Column for Month-over-Month Change

codewizard
Level 1
Handling 0 in Denominator Column for Month-over-Month Change

 

I'm trying to calculate the month-over-month percentage change in Dataiku using the following formula

((sales/ sales_lag) - 1) * 100

Where:

  • sales is the numerator column
  • sales_lag is the denominator column containing the lagged (previous month's) values

However, the sales_lag column can have null or zero values, which causes the division to result in infinity or null values in the derived column.

Could someone please help me with the correct formula or approach to handle null and zero values in the denominator column when calculating month-over-month percentage change in Dataiku?

Any assistance or guidance on how to handle this issue within Dataiku's formula syntax and limitations.

0 Kudos
1 Reply
Turribeach

Hi, there are several ways to handle this depending on what resulting value you want to use when your sales_lag is zero. Here are few functions you can use:

https://doc.dataiku.com/dss/12/formula/index.html#tests

You could use isNull() / isBlank() in combination with the if() fuction to check whether sales_lag is empty. Personally I would use isError() on your whole formula to have a guaranted result if any of the columns have values which produce an error. So something like this will do:

if(isError(((sales/ sales_lag) - 1) * 100), "", ((sales/ sales_lag) - 1) * 100)

 

 

0 Kudos