Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone.
I'm using aggr function for the first time, and I'm noticing that this function behaves weridly when used inside a table.
In particular I have a table with contract_id, service_description_1 (more services for each contract), service description _2, and an amount. In particular the service_description_1 is more general than the service_description_2, that is different service_description_2 may belong to the same class of service_description_1. Moreover the amount is related to the service_description_1, that is the value of the amount is the same for each service_description_2 belonging to the same class of service_description_1.
Now I want to compute the total amount for each contract, where for each contract the amount of each service_description_1 must be counted only once.
So I do something like: Sum(Aggr(Sum( Distinct Amount), Service_description_1)), and i want to put this in a table with the contract_ids. If I select a single contract this measure gives the correct result. If I select multiple contracts and these contracts have services belonging to a common service_description_1 then weird things happen. It looks the amounts are summed for all the contracts in the selection and the values are all grouped in a single contract. I cannot understandthe logic behind this.
I make here an example. Suppose we have:
contract_id | Service_id | Service_description_1 | Service_description 2 | amount |
A | 1 | de_1 | 1213 | 10 |
A | 2 | de_2 | 1214 | 15 |
A | 3 | de_2 | 1217 | 15 |
B | 7 | de_1 | 1219 | 20 |
B | 11 | de_2 | 1356 | 25 |
B | 8 | de_3 | 1444 | 5 |
I'm not sure about this, cause I'v not tested this simple table, but it looks like it does something like this:
contract_id | my_measure |
A | 70 |
B | 5 |
that is, all services with service_description_1 in (de_1, de_2) are summed and assigned to the first contract, the amount of the service with service_description_1 = de_3 is assigned to the second row.
It also looks like that when I write instead Sum(Aggr(Sum( Distinct Amount), Service_description_1, contract_id)), the the computations is right, that is:
contract_id | my_measure |
A | 25 |
B | 50 |
Does anyone knows what happens when selection are made, and according to which logic the measue is splitted over contract_id in this case?
It would be ok for me if, for the first measure I provided, the two resulting rows showed the same value, which would mean that when using the aggr inside a measure, the dimension of the table is somehow ignored, but that it's not what it looks like to happen. Moreover the fact I insert the contract_id inside the parameter list of the aggr function looks to affect how the measure splits over the contract_id dimension.
Any explanation would be highly appreciated. Thank you.
I think what you might really need is this
Sum(
Aggr(
Sum(DISTINCT Amount)
, contract_id, Service_description_1)
)