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?

[sunny_talwar]

How about this

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

[isorinrusu]

Or

Instead of what Sunny says:

sum({<contract_term_type=-{0}>}contract_term)

[thanhphongle]

I just tried ur solutions.

QV returns me about 32 as average.

But if I look at the table

contract_no = 1 -> 6 + 6 = 12

contract_no = 2 -> 12 +6 = 18

contract_no = 3 -> 6 +6 = 12
So the weighted average should be at least lower than 18

does maybe the part
Aggr(Sum(contract_term/Sum(TOTAL contract_term)*contract_term), contract_no))

should consider the conditions where contract_type>0?

[sunny_talwar]

Whoes solution are you talking about?

[sunny_talwar]

Also, what are you weighting on? Contract_term_type?

[thanhphongle]

I was talking about your solution. It should be weighted on contract term

[sunny_talwar]

That means you just need a simple average then, right? Try this

=Avg(Aggr(Sum({<contract_term_type = {'>0'}>}contract_term), contract_no))

[thanhphongle]

oh sorry it should be weighted on the fi_acquisiton. I forgot to mentioned in the table. I reedit the post and added a second table

my weighted function is this:

Sum(Aggr(sum(fi_acquisition/Sum(TOTAL fi_acquisition)*contract_term),contract_no))

[sunny_talwar]

Give me a final number you look to get based on the above data and the logic used