# Calculate Standard Deviation for rolling average

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:

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.

## 1 Answer

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$$