Calculate adjusted mean by excluding extreme values
I have a set of response data in this format:
Student ID School Q01 Q02 Q03 .......
10000001 A 5 4 3
10000002 A 3 3 1
10000003 B 4 5 5
10000004 B 4 3 4
10000005 C 3 2 5
10000006 C 5 3 4
The response data set are data collected from over 50000 students from different schools. They were asked to give 1 to 5 points to a number of questions (i.e. Q1, Q2, etc). By using Qlikview for analysis, we want to compile a table like this
School Mean of Q1 Mean of Q2 Mean of Q3......
A XX XX XX
B XX XX XX
C XX XX XX
Instead of calculating the normal mean using Avg(), we would like to exclude some extreme cases before calculation. The design is for each question, we exclude the top 5 and bottom 5 responses, and then we calculate the mean of the remaining responses. Thus for a question with the following responses:
The bottom 5 (i.e. 1,1,1,1,2 ) and top 5 (5,5,5,5,5) will be excluded and the rest are used for calculating the mean.
I tried to implement this in QV using rank() and firstsortedvalue() but neither of them work. I can't find a way to sort the responses (which most likely contain duplicate values) and then get rid of / exclude those top and bottom 5 responses. Can anyone shed some light on that?
