Beast Mode Help: Two layers of division
I've got a calculation I am trying to perform with a beast mode formula, that I can't get to work.
The calculation is (Number of Lines / Hours Active) / Standard
I'm trying to protect against divide by zero in the two layers of the formula. The first item in the order of operation is the (Number of Lines / Hours Active), then the result of that calculation is / Standard
Here is the beast mode I attempted, but is not working. I know this case is checking for NULL, do I need to check for zero as well?
CASE
WHEN IFNULL(SUM(`RCV Standard  Dynamic`),0)=0
THEN 0
ELSE
(CASE
WHEN IFNULL(SUM(`RCV Time Active`),0)=0
THEN 0
ELSE
(SUM(`RCV Number of Lines`)/SUM(`RCV Time Active`))/`RCV Standard  Dynamic`
END)
END
Best Answer

Another option is to do a nested CASE statement to account for the divide by zero in both divisors:
CASE
WHEN IFNULL(`Standard`,0) = 0 THEN 0
ELSE
(CASE
WHEN IFNULL(`Hours Active`,0) = 0 THEN 0
ELSE (IFNULL(Number of Lines,0) / IFNULL(Hours Active,0))
END) / IFNULL(`Standard`,0)END
1
Answers

Hi.
Please try using nullif and ifnull.
Original(Number of Lines / Hours Active) / Standard
Standard = 0 > null
(Number of Lines / Hours Active) / nullif(Standard,0)
0 or null > 0
ifnull((Number of Lines / Hours Active) / nullif(Standard,0),0)
3 
I've tried a few times without success to change my existing Beast Mode to include what you've suggested and I cannot get it to work. Can you show me the completed Beast Mode instead of just those suggested lines please?
0 
Hi @swagner,
If I correctly understood your problem... Have you tried using just nullif? Divisions by 0 do raise an error but division by null equals null (This is what I systematically use), so in your case, I would go with :
(Number of Lines / NULLIF(Hours Active,0)) / NULLIF(Standard,0)
This way no divisor will ever be zero.
In the end, you can always revert to zero if the result is null (I do not agree with this approach as a division by zero tends to an infinite number, not a zero! But some people follow this)
IFNULL((Number of Lines / NULLIF(Hours Active,0)) / NULLIF(Standard,0),0)
Hope this helps.
Ricardo Granada
**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.3 
I forgot to nullif to "Hours Active".
Original
(Number of Lines / Hours Active) / StandardStandard = 0 > null
(Number of Lines / nullif(Hours Active,0)) / nullif(Standard,0)
or
(sum(Number of Lines) / nullif(sum(Hours Active),0)) / nullif(sum(Standard),0)0 or null > 0
ifnull((Number of Lines / nullif(Hours Active,0)) / nullif(Standard,0),0)
or
ifnull((sum(Number of Lines) / nullif(sum(Hours Active),0)) / nullif(sum(Standard),0),0)1
Categories
 11.3K All Categories
 6 Private Company Board
 2 APAC User Group
 12 Welcome
 45 Domo News
 10.2K Using Domo
 2K Dataflows
 2.6K Card Building
 2.3K Ideas Exchange
 1.3K Connectors
 354 Workbench
 271 Domo Best Practices
 14 Domo Certification
 483 Domo Developer
 58 Domo Everywhere
 113 Apps
 734 New to Domo
 86 Dojo
 Domopalooza
 1.1K 日本支部
 4 道場日本支部へようこそ
 31 お知らせ
 65 Kowaza
 305 仲間に相談
 667 ひらめき共有