Beast mode divide by zero error
Hi,
I am stumped on a divide by zero error. I am calculating the weekly index by comparing weekly sales to the avg qtrly sales. Q3 and Q4 don't have results yet, so their value in the dataset is zero. How would I expand the case statement to correct for this error?
Thanks!!
Debbie
case when `acct_wk_i` <= 13 then (`nonclr_sls`/ `avg_nonclr_sls_qtr1`)*100
when `acct_wk_i` > 13 and `acct_wk_i` <= 26 then (`nonclr_sls`/ `avg_nonclr_sls_qtr2`)*100
when `acct_wk_i` > 27 and `acct_wk_i` <= 39 then (`nonclr_sls`/ `avg_nonclr_sls_qtr3`)*100
when `acct_wk_i` > 40 and `acct_wk_i` <= 52 then (`nonclr_sls`/ `avg_nonclr_sls_qtr4`)*100
else 0 end
Best Answer

What I do sometimes is adding 0.000000000001 to the denominator. That way you always get at a least 0% as a result every time.
Not he cleanest solution and obviously the result will always have a ninor error but gives the output I want most of the times.
2
Answers

would an isnull help out the code at the end
when (`nonclr_sls`/ `avg_nonclr_sls_qtr4`)*100 is null then '0' perhaps?
0 
Hi,
This doesn't work, since I already have "when" in the calcs to determine acct_wk_i ranges.
thanks for the idea.
0 
Thanks!! This worked perfectly.
0 
Hi @debbie_a, you can also use case statements for denominators. While adding 0.000000001 to a denominator works, another way that I teach people in my compnay is to use a case statement anytime there is a denominator. You can use case statements within case statements.
CASE WHEN Denominator = 0 THEN 0 ELSE
Numerator / Denominator END
Thanks,
Josh
2 
How would that apply in this equation? i'm getting errors when adding in additional case statements for each qtrs cal.
case when `acct_wk_i` <= 13 then (`nonclr_sls`/ `avg_nonclr_sls_qtr1`)*100
when `acct_wk_i` > 13 and `acct_wk_i` <= 26 then (`nonclr_sls`/ `avg_nonclr_sls_qtr2`)*100
when `acct_wk_i` > 27 and `acct_wk_i` <= 39 then (`nonclr_sls`/ `avg_nonclr_sls_qtr3`)*100
when `acct_wk_i` > 40 and `acct_wk_i` <= 52 then (`nonclr_sls`/ `avg_nonclr_sls_qtr4`)*100else 0 end
0 
Hi @debbie_a, this is probably not the best example because you have four different denominators, but if you had only one denominator, you could put the (case when denominator = 0 then 0 end) up front, then run through them all.
Here is what your beast mode would have to look like to use case statements.
case when `acct_wk_i` <= 13 then
CASE WHEN `avg_nonclr_sls_qtr1` = 0 THEN 0 ELSE (`nonclr_sls`/ `avg_nonclr_sls_qtr1`)*100 END
when `acct_wk_i` > 13 and `acct_wk_i` <= 26 then
CASE WHEN `avg_nonclr_sls_qtr2` = 0 THEN 0 ELSE (`nonclr_sls`/ `avg_nonclr_sls_qtr2`)*100 END
when `acct_wk_i` > 27 and `acct_wk_i` <= 39 then
CASE WHEN `avg_nonclr_sls_qtr3` = 0 THEN 0 ELSE (`nonclr_sls`/ `avg_nonclr_sls_qtr3`)*100 END
when `acct_wk_i` > 40 and `acct_wk_i` <= 52 then
CASE WHEN `avg_nonclr_sls_qtr4` = 0 THEN 0 ELSE (`nonclr_sls`/ `avg_nonclr_sls_qtr4`)*100 END
else 0 end0 
The problem with "when Denominator = 0" is that is does not work if the Denominator is null.
Then you would need to add IFNULL and so on.
There are circunstances when 0% is not what you want (I would need NULL instead sometimes) if the Denominator is 0 or Null, so +0.0001 is useless. At that point I start trowing IFNULLs and NULLIFs left, right and centre.
1 
Valid point as I have run into many issues with Null values in the past.
0
Categories
 10.6K All Categories
 1 APAC User Group
 12 Welcome
 36 Domo News
 9.6K Using Domo
 1.9K Dataflows
 2.4K Card Building
 2.2K Ideas Exchange
 1.2K Connectors
 339 Workbench
 252 Domo Best Practices
 11 Domo Certification
 461 Domo Developer
 47 Domo Everywhere
 100 Apps
 703 New to Domo
 84 Dojo
 Domopalooza
 1.1K 日本支部
 4 道場日本支部へようこそ
 22 お知らせ
 63 Kowaza
 296 仲間に相談
 649 ひらめき共有