Try this expression.
Have a look at the attachment.
test.qvw 138.2 K
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)
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. ☺)