Division By Zero - Be Aware - Failed IFERROR, IFNULL, and GROUPBY

joe

When you're performing calculations over 100s of millions or records sometimes you come across division by zeros in which you're hoping the software you're using would manage accordingly, I ran into a scenario in an ETL scenario where it lead to having more than a 10% revenue shortfall in my numbers.

Generally in Domo if there is no data or if there is an error it will return a NULL in the output variable; however, if it is a division by zero, several functions fail to work with the output.

Figure 1: Input and Output

• Ratio = Num / Denom
• Ratio IFERROR = IFERROR(Num / Denom, 0 )
• Ratio IFNULL = IFULL( Num/Denom , 0)
• Ratio Backfill IFERROR = IFERROR(IFERROR(Num / Denom, 0 ),0)

In Figure 1 you can see in row 4 that the Ratio of Num and Denom yielded a blank calculation, however, both the IFERROR and IFNULL failed to backfill with the assigned zero.

Compared to Row 6 where the Ratio is also blank, but the IFNULL was able to backfill with zero. Not that a blank / blank for Domo is not necessarily an error, hence it did not meet the criteria for Ratio IF ERROR result of 0.

The real problem came when it was time to Group BY.

Figure 2: GroupBY Node

When I grouped by Products and sum Ratio and Ratio IFERROR, Product Y failed to sum in Figure 2 Row 3 because of the blank in row 4 (Division by Zero) from Figure 1, as opposed to Product Z which also had a blank cell in row 6, but was able to sum as seen in row 1 in Figure 2.

As a workaround, I used a case statement to check the denominator before computing the ratio.

case

when Denom = 0 then 0

else Num / Denom

end

Domo is aware of the issue, but be aware that IFERROR, IFNULL, and GROUP BY fails at managing division by zero errors.

Think about it this way, if Product Y was 1 million records and just 1 of those 1 million records had a division by zero, the sum of all 1 million records would result with a BLANK.

HowDoIDomo

My hero!! Thank you!!