
Re: Weighted average for multiple users and test values where one user appears more than once
Kaushik Solanki Apr 13, 2013 2:59 AM (in response to Steve Lord)Hi,
Try this expression.
Sum(aggr(avg(TestValue),UserId))/count(DISTINCT UserId)
Have a look at the attachment.
Regards,
Kaushik Solanki

test.qvw 138.2 K

Re: Weighted average for multiple users and test values where one user appears more than once
Steve Lord Apr 15, 2013 9:53 AM (in response to Kaushik Solanki )Hi Miguel, that is one that got me close, but it won’t work right if a particular user had, say, two tests with a testvalue of 2 and one with a value of 1. I think that was it, it could be multiple occurrences of some other value threw it off though. This one here gets me the right answer no matter who has what values or how often a particular value appears anywhere. It gets the weighted average of testvalues within each user, then the weighted average of those testvalues within the group. The AVG function is actually risky since it won’t give extra weight to values that occur more than once. Better to sum the values and divide by count one way or the other.
(SUM(aggr(SUM(TestValue)/Count(UserId), TestName, UserId, Year)))/Count(DISTINCT UserId)
Steve
PS> It actually turned out to be the same or similar to another discussion solution, and I had just neglected to include all of the field values I needed to include in the aggr() function to line things up right. I read an article somewhere else around here where someone explained the aggr() function and showed its use which took me from a vague to a clear understanding of the aggr() function. After that, I revisited my SUM/COUNT components with it. In their example they found the max values of a set, then the minimum value of the max values to show how the aggr() function slices and dices set analysis work. (I read that at home and don’t have the link I’m afraid, but it was top 5 on aggr() subject discussions in QlikCommunity. I’ll do a better job citing people in the future. ☺)


Re: Weighted average for multiple users and test values where one user appears more than once
Gysbert Wassenaar Apr 13, 2013 3:35 AM (in response to Steve Lord)Try: avg(aggr(avg(TestValue),UserId,Year,TestName))
see attached example

comm79602.qvw 149.5 K
