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.
