Calculating aggregated standard deviation

[#1]

Hi

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

[swuehl · Accepted solution]

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

[#3]

Thanks Stefan

All calculations working ok now,

[#4]

Hi

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