topic Re: Weighted Standard Deviation in QlikView

Weighted Standard Deviation

Mon, 31 Aug 2015 12:56:17 GMT

I have a straight table that is calculating weighted average and weighted standard deviation (weighted by visits). When a selection is made in the straight table, the standard deviation calculates correctly. However, if no selection is made then it aggregates across all rows and shows that aggregated number in the standard deviation cell for each row.

For weighted average (which is calculating correctly), I used:

sum([VISITS]*[VALUE])/sum([VISITS]) Note that this did include TOTAL in the calculation initially. Removing this resulted in the correct calculation.

For weighted standard deviation, I am using:

sqrt(sum([VISITS]*sqr([VALUE]-sum( total [VALUE]*[VISITS])/sum(total [VISITS])))

/sum([VISITS])) I tried removing TOTAL from this calculation but it did not have the same effect as the weighted average - It resulted in no calculation.

Any ideas how to modify this weighted standard deviation formula so that it doesn't aggregate across all rows?

Re: Weighted Standard Deviation

Anonymous — Tue, 01 Sep 2015 08:18:48 GMT

Hi,

can you attach the qvw with your straight table and the desired result for a particular example?

Re: Weighted Standard Deviation

Tue, 01 Sep 2015 12:41:01 GMT

Hi Elena,

Please find the qvw attached.

If no ID is selected, then the weighted standard deviation is way off base for each id. However, selecting an ID shows the correct standard deviation. For example, ID 100A, with no selections the weighted average is 14.25% and the weighted standard deviation is 54.68% (incorrect). When 0100A is selected, the weighted average shows 3.61% (correct). I am trying to modify the expression so that the weighted standard deviation is correct regardless of whether a selection is made (like the weighted average).

Thanks.