Is there any ways to calculate moving standard deviation?
I want to calculate standard deviation in each time period based on moving period.
*not fixed period, period is determined by user's input.

In Anaplan there is no function to calculate standard deviation like Excel's "=STEDEV.P()",
so I try to calculate it using multiple line items.

Line items for
A : Moving Average calculation of Figure
B : Figure - Average
C : B^2
D : Moving Average calculation of C
E : Square root of D

To calculate one period of standard deviation is OK with above Line items,
however to calculate other time periods' standard deviation, these line item are not enough,
because only one "Average" can be used for one standard deviation calculation.

Is there any ideas to calculate standard deviation in each time period?

Use MOVINGSUM function to calculate the moving average across a stated time range where the calculation is carried out in a module contain time. 

Use TIMESUM when the calculation is not carried out in a module containing time. 

Both functions will allow you to calculate the mean average. 

Hi  

You are right, there is no direct function for Standard Deviation in Anaplan. If you are using a timescale and have a current period setup in time settings, use TIMESUM with aggregation method set to AVERAGE. This should help you get a rolling average value. 

Check out the TIMESUM Aggregation here 

Thank you for your comment.

I still don't have image for solution...

Please see attached image.

I think even using TIMESUM or MOVINGSUM, I can use only "an Average figure".

In my idea, to calculate W12's standard deviation, average figure as of W12 is used for each time period.

However to calculate W11's standard deviation, average figure as of W11 has to be used.

I have to use different "Average figure" in each time period.

Do you have any ideas for this?

Please don't use TimeSum() in a module that is dimensionalized by time as it has performance issues:  https://community.anaplan.com/t5/Anaplan-Live-December-2020/Function-vs-Function-MOVINGSUM-vs-TIMESUM/td-p/93488

Thanks,

Rob

Use MOVINGSUM within the calculation module dimensioned by time.

Set up a time settings module dimensioned by week. 

Add a new line item called, 'Count'. Format as number. Use the following formula =1. Set summary settings to SUM.

Add a new line item called, 'Cumulative'. Format as number. Use the following formula =IF 1+PREVIOUS(Cumulative)<=YEARVALUE(Count) THEN 1+PREVIOUS(Cumulative) ELSE 0

In your MOVINGSUM line item use the following formula =MOVINGSUM(Data to Average,-TimeSettings.Cumulative,0,AVERAGE)

This will calculate the YTD mean average of the 'Data to Average' line item in your calculation module. 

This can then be used in your stand deviation calculation.

It is a very common mistake and often overlooked, but can be very costly.  And that is the point, to learn something new every day?

Rob

I try to make standard deviation calculation  based on user's input period setting.

example1 user input=3weeks

Week 5's standard deviation is calculated based on figure on Week 3, Week 4 and Week 5

Week 4's standard deviation is calculated based on figure on Week 2, Week 3 and Week 4

…

example2 user input=4weeks

Week 5's standard deviation is calculated based on figure on Week 2, Week 3, Week 4 and Week 5

Week 4's standard deviation is calculated based on figure on Week 1, Week 2, Week 3 and Week 4

…

if user's input count of weeks is limited, I think I can write formula with many IF-THEN-ELSE.

However I want try to set calculation with no limit of count of weeks.

Do you have any ideas for this?

But I want function Do you think it is possible to c

Try replacing the start period in the MOVINGSUM with-the user input.

Example; user selects 3.

=MOVINGSUM( Value, -UserInput, 0, AVERAGE )

Thank you.

Moving average calculation is OK, but the goal is to calculate moving standard deviation.

I continue to consider how to calculate.