How to perform double agreggation (In multiple levels)

Question & Answer
, ,
1

Hiii! I need to sum all my costs (shown on the table), for the orders is only a sum but for the clients I have the costs repeated for each order so I need to do a MAX or an AVG first and then sum the costs for each seller. In my example is (5 + 2 + 9).
image
In SQL is something like this

select
AVG(AvgQuantity) “AvgofAverages”
from (
select
Client,
AVG(Quantity) “AvgQuantity”
group by Client
) X

I’m trying to do something like this but it doesn’t work:
sum({order_costs}) +
sum(avgOver({clients_costs}, [{clients}], PRE_AGG))
I also tried double agreggation but I get the Nested aggregation error.
Please please Help!!! :cold_sweat:

2

Hello @ramoshe !

I was able to get that syntax to save as a calculated field on my end. Are {order_costs} and {client_costs} calculated fields as well?

And if so, could you share what they look like? This will help me understand what the nested aggregation error might be coming from.

3

Hi Duncan, no, they are not calculated fields.

1 Like
4

Hi @ramoshe,

Can you try sum(max({client_costs}, [{clients}]))?

2 Likes
5

Hello @ramoshe !

Were you able to try @David_Wong 's calculated field? To me that looks like it should work.

6

Thank you that was the answer!!! :smiling_face_with_three_hearts: