3 Replies Latest reply on May 18, 2016 2:02 AM by Jessica Fan

# Standard deviation of a year on year calculation

I would like to know how to calculate the standard deviation of a year on year increase in client revenues.

Each client is broker down by type. Corporate etc.

I would like to take the peer average which is the average increase in Revenues between last year YTD compared with current YTD by type.

so steps would be:

Calculate the peer average increase YoY % for Corporates: 15% for e.g.

I would then like to be able to calculate the Standard deviation for all corporate clients so that I could see all clients that are within 1 standard deviation etc and what are the outliers and plot them in a graph, showing average, standard deviation above and below bands and plot the clients.

Whenever I try to do this I always get the aggregation error that other posts have mentioned previously.

Could anyone help with an approach to do this? Much appreciated.

• ###### 1. Re: Standard deviation of a year on year calculation

Hi Ben,

Please see the attached workbook for a visualisation on standard deviation (this workbook uses a parameter to allow you to define the number of s.d. you want to see)

You should be able to create fields for the upper and lower bounds of your s.d and plot them as reference bands.

e.g. something like this:

WINDOW_AVG(SUM([Profit])) - WINDOW_STDEV(sum([Profit]))*[Standard Deviation Param]

Hope this helps

Jessica

1 of 1 people found this helpful
• ###### 2. Re: Standard deviation of a year on year calculation

Thanks Jessica this is awesome. One thing I am still struggling with a little bit is that my calculation is based on year on year growth. so its affectively a percentage difference from the percentage mean.

When I try to inject a YoY calculation where you have sum(profit) in the stddev calculation I get the aggregation error.

Not sure if I am explaining this clearly but its the year on year percentage that I am trying to calculate the stddev on.

• ###### 3. Re: Standard deviation of a year on year calculation

Hi Ben,

Can you please share the calculation and the error you see? Or attach the workbook (in twbx format)

Jessica