AWS Quicksight - get std dev for fields grouped by sum?

Question

New to QS. I have data like this:

timestamp	count
2022-01-01	5
2022-01-01	2
2022-01-02	0
2022-01-02	9
2022-01-02	2

I want to sum the counts for each timestamp, and then get the standard deviation for those sums.

My attempt was this, because a similar approach worked for getting the median:

stdev(sumOver({count}, [timestamp], PRE_AGG))

Resulting value is always a little lower than it should be(for example 13.53 instead of 15.03). What's wrong?

Answer 1

As suggested by @Register Sole it is probably the difference between the standard deviation of the sample, stdev in Quicksight which uses sqrt(N-1) and the standard deviation of the population, stdevp in Quicksight, which uses sqrt(N) as the denominator in the standard deviation formula.

Using your sample provided above I made the Quicksight Table visualisation below without any calculated fields, just adding the count field multiple times and changing the aggregation used for the column:

I confirmed the stdevs in both Excel and manually as accurate. Cheers (I would have added this as a comment but lack the rep)