
Re: Standard deviation and weighted average
John Witherspoon Mar 8, 2012 12:02 PM (in response to Andrew Quickfall)The simplest approach might be to unaggregate the scores again. For instance:
Raw:
LOAD Score as Score2
RESIDENT Aggregated
WHILE iterno()<=Respondents
;Then you can just use avg(Score2) and stdev(Score2). Even if you have a huge sample size, it's only a single small integer getting duplicated (Edit: well, plus a key field to connect it to your data), so the memory requirements might not be bad. If that is impractical for you, here's a way to calculated the weighted average and the standard deviation without unaggregating:
weighted average = sum(Score*Respondents)/sum(Respondents)
standard deviation = sqrt(sum(Respondents*sqr(Scoresum(total Score*Respondents)/sum(total Respondents)))/(sum(Respondents)1))You may or may not want the final 1 in the standard deviation. It's required to duplicate what QlikView does, and Wikipedia indicates that this is used when you have a sample from a population. If you have ALL responses, then it may be appropriate to remove it.
See attached.
testStdevOnAggregation.qvw 149.8 K
