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 -
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 -
that worked...thanks again
2
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive