1 Reply Latest reply on Aug 31, 2016 12:29 AM by Vasil Petkov

# How do you handle viewing certain dates when using a calculated field that expresses Standard Deviation?

Hello everyone,

I've recently been charged with dealing with a calculated field that is getting the standard deviation of certain data points. I have created a sample workbook to mimic the behavior of my project's workbook. I have a two calculated fields:

• Moving Average - takes the window average of the summation of the rows starting from the current row going back 7 rows
• WINDOW_AVG(SUM([Profit]), -7, 0)
• Standard Deviation - takes the sum of profit, divides by the moving average calculated field minus 1 then multiples the result by 100
• (SUM([Profit])/[Moving Average]-1)*100

The question: Is there a way to use all the data from the population when the calculations are done then just focus on a particular date range without the calculation being affect? (I know this sounds unclear, the concept is still trying to be cemented in my brain.)

Here is an example using the Coffee Chain data source:

Now, the user changes the startTime and endTime parameters to start to view the data from February 2012 to May 2012:

The values have changed. Now to clarify, I did expect this to happen since I am using startTime and endTime parameters to control the date range, thus affecting the amount of data points used in the calculation. Unfortunately, this is not what was requested. I should be able to change the date range and not have it affect the underlying calculation and take into consideration the last 7 days (if possible but I doubt, I'm thinking I would have to use the entire population instead of sample.) I'm open to hear any and all suggestions in handling this. Thank you all for your time and effort in helping me with my issue!

• ###### 1. Re: How do you handle viewing certain dates when using a calculated field that expresses Standard Deviation?

Hi Vincent,

You can use a window calculation to filter your dates, something like WINDOW_MAX(max([timeUnit])) computed along cell. You can then use this as a filter. You users will be able to select specific months without compromising the calculation.

Check the attached for a sample solution.

Best,

-V