topic Re: Calculating aggregated standard deviation in QlikView

Calculating aggregated standard deviation

Wed, 08 Feb 2012 09:09:51 GMT

I'm trying to calculate a aggregated standard deviation.

Attached is a example Qlikview doc with 10 days worth of data for 6 trading books.

I can calculate the average and standard deviation for each trading book.

Next step is to aggregate the trading books into desks.

I have a formula to calculate the count and the average, however when I try and replicate formula for standard deviation I get different answers to what I am expecting.

Any ideas how to calculate the standard deviation by desk (aggregation of the trading books)

Thanks

Andy

Re: Calculating aggregated standard deviation

swuehl — Wed, 08 Feb 2012 10:59:42 GMT

Andy,

I needed to add a closing bracket ] in your last inline load, that's also the reason why your total count was 21, not 20.

Then I changed your count to

=count(aggr(sum(Revenue), Desk,Day))

your average to

=avg(aggr(sum(Revenue), Desk,Day))

and your stdev

=stdev(aggr(sum(Revenue),Desk, Day))

which seems to give plausible data to me.

Hope this helps,

Stefan

Calculating aggregated standard deviation

Wed, 08 Feb 2012 11:14:41 GMT

Thanks Stefan

All calculations working ok now,

Re: Calculating aggregated standard deviation

Thu, 28 Feb 2013 10:19:19 GMT

I tried drilling down on the function stdev and I found out that it is equal to stdev.s of excel.
Excel has 2 ways of calculating standard deviation 1)Stdev.P 2)Stdev.S.

I would like to show how these works. Let us suppose we have 2 nos. 85 and 90.

so if i use Stdev.P ans is 2.5 and that has to be the ans

if i use Stdev.S ans is 3.34.

and the same ans 3.34 is given by qlik view Stdev formula. Isnt it wrong? please guide me