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.
Answers
-
Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,981 Neuron
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)