Beast mode divide by zero error

debbie_a
debbie_a Member
in Magic ETL

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

  • rado98
    rado98 Contributor
    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.

     

Answers

  • KVincent
    KVincent Member

    would an isnull help out the code at the end

    when (`nonclr_sls`/ `avg_nonclr_sls_qtr4`)*100 is null then '0' perhaps?

  • debbie_a
    debbie_a Member

    Hi,

    This doesn't work, since I already have "when" in the calcs to determine acct_wk_i ranges.

    thanks for the idea.

  • rado98
    rado98 Contributor
    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.

     

  • debbie_a
    debbie_a Member

    Thanks!! This worked perfectly.

  • jstan
    jstan Contributor

    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

  • debbie_a
    debbie_a Member

    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`)*100

    else 0 end

  • jstan
    jstan Contributor

    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 end

  • rado98
    rado98 Contributor

    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. 

  • jstan
    jstan Contributor

    Valid point as I have run into many issues with Null values in the past.

Categories