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
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 753 Beast Mode
- 61 App Studio
- 41 Variables
- 692 Automate
- 177 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive