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 -
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 -
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
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive