topic How do I get weighted sum? in QlikView

How do I get weighted sum?

Mon, 01 Jul 2013 18:47:41 GMT

I am trying to calculate the weighted sum. However

Aging Cost Weight by Cost Weighted Aging

1 10 10/80 1*(10/80)

2 30 30/80 2*(30/80)

3 40 40/80 3*(40/80)

Total 80 1 2.375

I'd like to get the result of 2.375. However, such number doesn't show but a symble "-" Coul d somebody help me on having the weighted sum calculated? Thanks!

Re: How do I get weighted sum?

Mon, 01 Jul 2013 18:53:28 GMT

What I am using to get the Weighted Aging was

Sum

(Cost)/Sum (total <CURRENT_AREA> (Cost))*Aging

Should I do extra steps to get the total? Anyone knows how to do that?

Re: How do I get weighted sum?

hic — Mon, 01 Jul 2013 20:29:03 GMT

Normally, weighted averages are calculated through

Sum(Weight*Cost)/Sum(Weight)

And this formula would give a correct total also. But you want to us Aging also, which perhaps complicates it. But try

Sum(Aging*Weight*Cost)/Sum(Aging*Weight)

HIC

Re: How do I get weighted sum?

hic — Mon, 01 Jul 2013 20:32:51 GMT

Sorry, I didn't notice that your cost and your weight is the same thing - just try

Sum(Aging*Cost)/Sum(total Cost)

which probably is closer to what you want to do.

HIC

Re: How do I get weighted sum?

Mon, 01 Jul 2013 20:36:37 GMT

Oh my god!

It took me hours of time trying to figure out how to use Aggr() and you solved in such a simple way!!! Big thanks!!