BEAST MODE CALCULATION ISSUE

jtrollinger
jtrollinger Member
in Magic ETL

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

***************************************************************************

image.png
image.png

Best Answer

  • brycec
    brycec Contributor
    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:

    https://domo-support.domo.com/s/article/4408174643607?language=en_US

    Was this comment helpful? Click Agree or Like below.
    Did this comment solve your problem? Accept it as the solution!

Answers

  • brycec
    brycec Contributor
    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:

    https://domo-support.domo.com/s/article/4408174643607?language=en_US

    Was this comment helpful? Click Agree or Like below.
    Did this comment solve your problem? Accept it as the solution!

Categories