Weighted average for multiple users and test values where one user appears more than once

[stevelord]

Hi, I have been puzzling over this for hours and need help computing an average TestValue for a group of Users.  Some Users appear more than once, but the Users should be equally weighted.  For each User with multiple TestValues, their TestValues need to be averaged to make one TestValue, then all of the User's average Test Values are to be averaged out to a group average TestValue.

Here is Dummy Data I used

TestName     Year     UserId     TestValue

Atest          2012     aaa          1

Atest          2012     aaa          1.5

Atest          2012     bbb          1

Atest          2012     ccc          2

Btest          2012     aaa          3

Btest          2012     ddd          9

Atest          2013     aaa          1000

I have this formula which averages the test values, but it is giving double weight to UserId aaa rather than averaging aaa's TestValue to 1.25 first.

SUM( aggr( SUM(TestValue)/COUNT(UserId), TestValue, UserId))/(COUNT(UserId))

For Atest, it gives 1.375 when it should give 1.416667 - I played with Distinct and All, and figure I need to switch something around or nest another computation somewhere in there.

For Btest, it gives 6 which would be fine in that case (I made Btest to check and ensure it kept the different kinds of tests apart,)

For Atest 2013 it gives 1000 - I made that record to ensure different years were kept apart.

If you can get 1.416667 for average TestValue for Atest 2012 without disrupting the other correct averages, you've got a correct answer I'd like to see.

I feel like the answer is right at my fingertips, but my brain is cooked now.  Please help!  Thanks!

-Steve

[kaushiknsolanki]

Hi,

     Try this expression.

     Sum(aggr(avg(TestValue),UserId))/count(DISTINCT UserId)

     Have a look at the attachment.

Regards,

Kaushik Solanki

[Gysbert_Wassenaar]

Try: avg(aggr(avg(TestValue),UserId,Year,TestName))

see attached example

[stevelord]

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. ☺)