How do I calculate these in Beast Mode?

lawiti
lawiti Member
in Beast Mode

I created the nested beast mode below and got this error message
'An issue has occurred during processing. We are unable to complete the request at this time.' , raised a support ticket and was told that beta feature(nested beast mode) has been closed/ no longer available.

How else can I do these calculations:

  1. AVG( (COALESCE(SUM(Ferric_cost), 0) + COALESCE(SUM(FEP_cost), 0) + COALESCE(SUM(HP_cost), 0) + COALESCE(SUM(PAC_cost), 0) + COALESCE(SUM(SES_Poly_cost), 0) + COALESCE(SUM(Innospec_cost), 0)) / COALESCE(AVG(BBLs), 1)
    ) OVER (ORDER BY date)
  2. COALESCE(AVG(BBLs), 0) OVER (ORDER BY date)
  3. SUM(chemcost/day)/ SUM(AVG BBLs)

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    The issue is you're attempting to aggregate an aggregate, which you can't do within a Beast Mode; you'd need to pre-aggregate your data in an ETL before attempting to aggregate it in a Beast Mode.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach

    Which fields are nested beast modes? What definitions go to which field?

    Have you tried using a table card and breaking each part of the beast mode into separate sections to determine which part is causing issues?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • lawiti
    lawiti Member

    @GrantSmith, thanks for your response. I will give you an example with the 3. SUM(chemcost/day)/ SUM(AVG BBLs)…the chemcost/day = SUM(COALESCE(Ferric_cost, 0)) + SUM(COALESCE(FEP_cost, 0)) + SUM(COALESCE(HP_cost, 0)) + SUM(COALESCE(SES_Poly_cost, 0)) + SUM(COALESCE(Innospec_cost, 0)) + SUM(COALESCE(PAC_cost, 0)).

    AVG BBLs = COALESCE(AVG(BBLs), 0) OVER (ORDER BY date)

    I have tried to inspect AVG BBLs = COALESCE(AVG(BBLs), 0) OVER (ORDER BY date), the component fields are showing just fine.

  • lawiti
    lawiti Member

    @GrantSmith do you have further guidance on this?

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    The issue is you're attempting to aggregate an aggregate, which you can't do within a Beast Mode; you'd need to pre-aggregate your data in an ETL before attempting to aggregate it in a Beast Mode.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories