0

Good morning Math Experts, I am having difficulty figuring out how to calculate the standard deviation for a set of data.

Here is the request from my user: Going back three years from today, calculate the yearly average of contacts an officer has with a citizen and the standard deviation of the yearly average across a department.

So I have counted all the contacts in the three year period and counted all the officers that made those contacts averaged that and divided by three to get a single year average:

enter image description here

How can I calculate the standard deviation of this data at the ONE year level? Am I even calculating the one year average correctly?

I feel like maybe I am making this too complicated but don't think the answer the SQL STDEV() function is giving is correct. I am using SQL server query but can use R if that makes it easier.

Leslie
  • 101

1 Answers1

0

Separate the query per year first. So for example, call $e_1$ the average number of encounters in the last year (TotalContants/NumberOfOfficers), $e_2$ the average number of encounters from one year ago to two years ago, ...

You will then have a list of values $e_1, e_2, \cdots$, the calculate the standard deviation of that

$$ \sigma^2 = \frac{1}{n}\sum_{k=1}^n (e_k - \overline{e})^2 $$

in your case $n = 3$. The issue here is that three points are usually not enough to actually describe what's happening. My suggestion is to define the quantity

$$ e_\delta(n) = \frac{\text{Total contancts in the period }(n\delta - 1{\rm yr}, n\delta)}{\text{Number of officers in the period }(n\delta - 1{\rm yr},n\delta)} $$

So for example you can set $\delta = 1~{\rm month} = 1/12~{\rm year}$, and $e_{1/12}(0)$ is the average number of encounters in the last years, $e_{1/12}(-1)$ is the number of encounters in one year measured from 1 month ago, $e_{1/12}(-2)$ ... Doing it this way you get a sample of 24 points

$$ \sigma^2 = \frac{1}{n}\sum_{k=0}^{n}(e_{1/12}(-k) - \overline{e}) $$

with $n=24$

caverac
  • 19,345