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
Brett
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"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
 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 ひらめき共有