help with margin formula

Im running the following formula to produce my margin %, and want when 'override_payee_id' = 'MEADBIMT' to have the % always be 15% but currently the way its written its taking .15/SUM(IFNULL(`total_charge`,0))*100 which isn't producing the correct amount, how can I write the following to have when the payee id is MEADBIMT to always be 15% but else run through the rest of my case statement

 

thanks

 

SUM(CASE

when `override_payee_id` = 'MEADBIMT' then .15
when IFNULL(`NewAmount`,0) = 0 then `total_charge` - `override_pay_amt`
ELSE `total_charge` - `override_pay_amt` - `NewAmount`

End

) / SUM(IFNULL(`total_charge`,0))*100

Best Answer

  • AS
    AS 🔵
    Accepted Answer

    I think one of the parentheses is out of order.  And we can eliminate the *100 since the card can be formatted to improve readability.

     

    CASE
    WHEN `override_payee_id` = 'MEADBIMT'
    THEN AVG(.15)
    ELSE
    SUM(`total_charge` - `override_pay_amt` - IFNULL(`NewAmount`,0))
    /
    SUM(IFNULL(`total_charge`,0))
    END

Answers

  • Try taking that case statement out of the division.  This might not work exactly as scripted, but use this for concept:

     

    CASE
    WHEN `override_payee_id` = 'MEADBIMT'

    THEN .15

    ELSE

    SUM(
    CASE 

    WHEN IFNULL(`NewAmount`,0) = 0

    THEN `total_charge` - `override_pay_amt`
    ELSE `total_charge` - `override_pay_amt` - `NewAmount`
    END
    )

    /

    SUM(IFNULL(`total_charge`,0))*100

    END

  • that didn't seem to change anything...I understand what you're saying about breaking apart the case statement but its like its still taking into account the case with the division even when the 'override_payee_id' = 'MEADBIMT'

  • There may be an issue with aggregated outputs versus nonaggregated outputs.  Aggregations in case statements can be tricky.

    We could simplify this a little bit also.

     

    CASE
    WHEN `override_payee_id` = 'MEADBIMT'

    THEN AVG(.15)

    ELSE

    (SUM( `total_charge` - `override_pay_amt` - IFNULL(`NewAmount`,0)
    /

    SUM(IFNULL(`total_charge`,0))*100)

    END

  • so I wrote as below, added an ending parenthesis as it was giving a syntax error if not, but when adding to the card is stating "An Issue occurred during processing, we are unable to complete the request at this time"

     

    CASE
    WHEN `override_payee_id` = 'MEADBIMT'

    THEN AVG(.15)

    ELSE

    (SUM( `total_charge` - `override_pay_amt` - IFNULL(`NewAmount`,0)
    /

    SUM(IFNULL(`total_charge`,0))*100))

    END

  • that worked...thanks again