Magic ETL

Magic ETL

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

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Best Answer

  • 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

  • would an isnull help out the code at the end

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

  • Hi,

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

    thanks for the idea.

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

     

  • Thanks!! This worked perfectly.

  • 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

  • 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

  • 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

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

  • Contributor

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

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In