Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Try this
=Sum(Aggr(Sum({<contract_term_type = {'>0'}>}contract_term*fi_acquisition), contract_no))/Sum(fi_acquisition)
How about this
Sum({<contract_term_type = {'>0'}> }Aggr(Sum(contract_term/Sum(TOTAL contract_term)*contract_term), contract_no)))
Or
Instead of what Sunny says:
sum({<contract_term_type=-{0}>}contract_term)
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?
Whoes solution are you talking about?
Also, what are you weighting on? Contract_term_type?
I was talking about your solution. It should be weighted on contract term
That means you just need a simple average then, right? Try this
=Avg(Aggr(Sum({<contract_term_type = {'>0'}>}contract_term), contract_no))
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))
Give me a final number you look to get based on the above data and the logic used