sum(aggr(x), y) with if(z)

[thanhphongle]

Hello Community

these is my table

contract_no	contract_term	contract_term_type
1	36	0
1	6	1
1	6	2
2	48	0
2	12	1
2	6	2
3	36	0
3	6	1
3	6	2

contract_no	fi_acquisition
1	30000
2	9000
3	2000

I want to calculate the weighted average of the contract term where contract_term_type > 0. The weighting should refere to fi_acquisiton

What I tried so far is

if(contract_term_type>0, Sum(Aggr(sum(cost_asquisition/Sum(TOTAL cost_acquisition)*contract_term),contract_no)))

Unfortunetly is does not work

Hope someone can help me.

And is there a way to solve it with Set Analysis Function?

[thanhphongle]

This is what the expression should return

1. sum(TOTAL fi_acquisition) = 41.000

2. sum(fi_acquisition)/sum(TOTAL fi_acquisition) per contract_no where contract_term_type >0

• contract_no = 1 AND contract_term_type = 1 AND contract_term = 6 ->  30.000/41.000 = 0,7317
• contract_no = 1 AND contract_term_type = 2 AND contract_term = 6 ->  30.000/41.000 = 0,7317
• contract_no = 2 AND contract_term_type = 1 AND contract_term = 6 ->  9.000/41.000 = 0,2195
• contract_no = 2 AND contract_term_type = 2 AND contract_term = 6 ->  9.000/41.000 = 0,2195
• contract_no = 3 AND contract_term_type = 1 AND contract_term = 6 ->  2.000/41.000 = 0,0487
• contract_no = 3 AND contract_term_type = 2 AND contract_term = 6 ->  2.000/41.000 = 0,0487

3. now take the result and multiply it with the contract_term -> Result* contract_term

• 0,7317 * 6 = 4,39
• 0,7317 * 6 = 4,39
• 0,2195 * 12 = 2,63
• 0,2195 * 6 = 1,31
• 0,0487 * 6 = 0,29
• 0,0487 * 6 = 0,29

4. Take the sum
4,39 + 4,39 + 2,63 + 1,31 +0,29 + 0,29 = 13,31

[sunny_talwar · Accepted solution]

Try this

=Sum(Aggr(Sum({<contract_term_type = {'>0'}>}contract_term*fi_acquisition), contract_no))/Sum(fi_acquisition)

[thanhphongle]

IT WORKS !!! THANKS VERY MUCH !!