How do I calculate these in Beast Mode?

lawiti

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)

GrantSmith

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.

GrantSmith

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?

lawiti

@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

@GrantSmith do you have further guidance on this?

GrantSmith

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.