Beast mode to kill div0 errors

Hi, can anyone tell me why If my "A" code works, and my "B" CASE WHEN... THEN 0 ELSE 2 END code works, why can't I substitute code "A" for "2" in code B? I'm trying to kill divide by zero errors in a beast mode.

--A

SUM(IFNULL(`qty sold instock`,0))/SUM(IFNULL(`qty found`,0))

--B

(CASE WHEN (IFNULL(`qty found`,0)) = 0 THEN 0 ELSE 2 END)

--B ELSE A

(CASE WHEN (IFNULL(`qty found`,0)) = 0 THEN 0 ELSE (SUM(IFNULL(`qty sold instock`,0))/SUM(IFNULL(`qty found`,0))) END)

Thanks

Tagged:

Best Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🔵
    Accepted Answer

    You might need to provide some sample rows of your data. I took your final beast mode:

    (CASE WHEN (IFNULL(`qty found`,0)) = 0 THEN 0 ELSE (SUM(IFNULL(`qty sold instock`,0))/SUM(IFNULL(`qty found`,0))) END)

    And put together some sample rows with the same columns that look like this and beast mode calc worked.


  • MaryAl
    MaryAl ⚪️
    Accepted Answer

    I thought it should work! There must be some NULL or ZERO scenario I failed to address. This code considering all NULL and ZERO possibilities does work.

    CASE

    WHEN (SUM(IFNULL(`qty sold instock`,0)) + SUM(IFNULL(`qty found`,0))) = 0 THEN 0

    WHEN (SUM(IFNULL(`qty found`,0))) = 0 THEN 0

       WHEN (SUM(IFNULL(`qty sold instock`,0))) = 0 THEN 0

       ELSE (SUM(`qty sold instock`)*1) / SUM(`qty found`)

    END

    Thanks!

Answers

  • MaryAl
    MaryAl ⚪️

    I found another way to accomplish this with more WHEN statements. Just curious why the B ELSE A does not work.

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!