BEAST MODE CALCULATION ISSUE
Using the Beast Mode below I am being forced to use the ABS function in order for the Beast mode to give me a calculation. If I remove the ABS Function I get the error "An issue has occurred during processing. We are unable to complete the request at this time". The issue I have is I need to remove the ABS (Absolute Value Function) from the Beast Mode because in some cases I have have a Negative number that is required to be calculated to show a negative profit margin.
*********************************************************************
CASE WHEN SUM(IFNULL(CASE WHEN JT158_WTParent
= 'Y' THEN (SO Invoice QuantityShipped
* SO Unit Price
) ELSE 0 END ,0)) = 0
THEN 0
ELSE
(((SUM(IFNULL(ABS(CASE WHEN JT158_WTParent
= 'Y' THEN (SO Invoice QuantityShipped
* SO Unit Price
) ELSE 0 END ),0))) - (SUM(IFNULL(Actual Material Cost
,0)) + (SUM(IFNULL(Actual Labor Cost
,0))))))
/
SUM(IFNULL(CASE WHEN JT158_WTParent
= 'Y' THEN (SO Invoice QuantityShipped
* SO Unit Price
) ELSE 0 END ,0))
END
***************************************************************************
Best Answer
-
I find that Beast Modes often fail if the top-most level of the calculation is not an aggregation, but aggregation functions are used elsewhere in the Beast Mode. What I mean is, in your calculation, you are using SUM several times, but the top-level of the Beast Mode is a CASE statement and a divide operation. This may seem unrelated to your ABS problem, but I find that sometimes Beast Modes function unexpectedly like that when the real root cause is something like this. I would try remaking your Beast Mode so that it is all encased in a SUM. You can either achieve this by finding a way to remove all aggregate functions underneath that SUM, because Beast Modes don't support double aggregations in that way, or explore using FIXED functions to achieve double aggregations, still wrapping the Beast Mode in a SUM (or other appropriate aggregation).
Here is some great documentation of FIXED functions in Beast Modes if you aren't familiar:
Was this comment helpful? Click Agree or Like below.
Did this comment solve your problem? Accept it as the solution!2
Answers
-
I find that Beast Modes often fail if the top-most level of the calculation is not an aggregation, but aggregation functions are used elsewhere in the Beast Mode. What I mean is, in your calculation, you are using SUM several times, but the top-level of the Beast Mode is a CASE statement and a divide operation. This may seem unrelated to your ABS problem, but I find that sometimes Beast Modes function unexpectedly like that when the real root cause is something like this. I would try remaking your Beast Mode so that it is all encased in a SUM. You can either achieve this by finding a way to remove all aggregate functions underneath that SUM, because Beast Modes don't support double aggregations in that way, or explore using FIXED functions to achieve double aggregations, still wrapping the Beast Mode in a SUM (or other appropriate aggregation).
Here is some great documentation of FIXED functions in Beast Modes if you aren't familiar:
Was this comment helpful? Click Agree or Like below.
Did this comment solve your problem? Accept it as the solution!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