Nesting of aggregate functions...is not allowed

Question & Answer
,
1

I have a few calculated fields created in my dashboard,
monthly Cost per Unit(CPU) = coalesce(sum(totalcost)/sum(quantity), 0)
baseline Cost per unit(CPU) (Trailing 12 month average cost per unit) = windowSum(sum(totalcost),[{month_period} ASC],12,0, [{item_id])/windowSum(sum(quantity),[{month_period} ASC],12,0, [{item_id}])
cost reduction/increase = coalesce(({monthly CPU}- {baseline CPU})*{quantity}, 0)
I have the above created successfully,
now I want to quantify the reduction and increase scenarios respectively, but i got the error message that 'nesting of aggregate function is…not allowed.

  1. when monthly CPU>=baseline CPU, then it is a potential reduction in cost, so the calculation is sumif({cost reduction/increase}, monthly CPU>=baseline CPU)
  2. when monthly CPU<baseline CPU, then it is a potential increase in cost, so the calculation is sumif({cost reduction/increase}, monthly CPU<baseline CPU)

Any ideas how can I bypass this error?

2

See if this helps

3

Hi @yuchen1,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

4

Hi @yuchen1,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information if needed.