topic Weighted Average not rolling up properly in QlikView

Weighted Average not rolling up properly

avastani — Thu, 01 Apr 2010 21:27:00 GMT

I have weighted averages in my app and when I start to roll them up in dimensions the weighted averages are not working. Has anyone faced this issue? I compute the weighted average in script as x= SUM(A * B)/SUM(B).

The chart expression is Avg(x) and it is not giving me the right answer at higher dimensions. At the lowest level of detail and 2-3 dimensions up the numbers look fine but start skewing beyond that point.

Weighted Average not rolling up properly

johnw — Thu, 01 Apr 2010 22:21:41 GMT

Avg() is a simple average. So your chart is taking the simple average of some precalculated weighted averages. As you've found out, that doesn't work.

You need to do the weighting in the chart itself, not in the script. Your chart expression needs to be sum(A*B)/sum(B) in order for the weighted averages to roll up properly.

Weighted Average not rolling up properly

avastani — Fri, 02 Apr 2010 15:07:34 GMT

that is correct, so I modified that and do sum(A*B) and sum(B) separately in the script and the sum(A*B)/sum(B) as a separate expression in the chart itself.

however, qlikview is still not reporting the right answers when rolled up for both of them.

in case 1 where I am do the sum/s in the script, it works fine at low levels but starts giving problems at higher levels of roll up say roll up 4 onwards

in case 2 it gives incorrect results when the rolled up values are broken at lower levels.

i know it is something really simple that i am missing but can't get what it is.

Weighted Average not rolling up properly

johnw — Tue, 06 Apr 2010 01:00:17 GMT

Not sure what to say. Sounds like it should work. Do you have an example of the problem that you could post?

Weighted Average not rolling up properly

Oleg_Troyansky — Tue, 06 Apr 2010 05:35:18 GMT

why aggregate anything in the script? How about simply using the formula in the chart expression?

If you could post a sample, it would be easier to find out what else might be wrong...