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

code_wizard

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.

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)