help with margin formula

meadow_ryan

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

AS

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

AS

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

meadow_ryan

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'

AS

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

meadow_ryan

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

AS

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

meadow_ryan

that worked...thanks again