Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have data with the amount; each amount is linked with a contract number and id.
I want to take the sum of the amount field for each contract with max id and aggregate based on the contract number
Contract number | Amount | Id |
1001 | 4500 | 2 |
1001 | 4500 | 4 |
1001 | 4900 | 5 |
1002 | 2122 | 1 |
1002 | 2500 | 2 |
1003 | 1220 | 4 |
1003 | 1000 | 3 |
So, for contract 1001 , amount should be - 4900
for contract 1002 , amount should be - 2500
for contract 1003, amount should be - 1220
and the required sum should be (4900+2500+1220)=8620
Hi @PedroNetto thank you for suggestion, could you please tell me what is [number Amount], we need to take max of ID against the contract number
@User0 the correct formula is: Sum( Aggr( max([Amount], Contract))
The amount need not be max, we need to take the amount with max id, for example:
Contract number | Amount | Id |
1001 | 4500 | 2 |
1001 | 4500 | 4 |
1001 | 4000 | 5 |
1002 | 2122 | 1 |
1002 | 2000 | 2 |
1003 | 1000 | 4 |
1003 | 1300 | 3 |
So, for contract 1001 , amount should be - 4000
for contract 1002 , amount should be - 2000
for contract 1003, amount should be - 1000
total amount = 7000.
@PedroNetto Thank you so much 🙂 It worked!!!