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
-
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))
ENDAaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
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(
CASEWHEN 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
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
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'
0 -
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
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
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))
END0 -
that worked...thanks again
2
Categories
- 11K All Categories
- 5 Private Company Board
- 2 APAC User Group
- 12 Welcome
- 42 Domo News
- 9.9K Using Domo
- 2K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 349 Workbench
- 267 Domo Best Practices
- 12 Domo Certification
- 474 Domo Developer
- 51 Domo Everywhere
- 110 Apps
- 726 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 29 お知らせ
- 65 Kowaza
- 302 仲間に相談
- 664 ひらめき共有