How to get moving standard deviation

Naoto

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?

ChrisAHeathcote

@Naoto 

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. 

 

ankit_cheeni

Hi @Naoto 

 

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 

Naoto

@ChrisAHeathcote @ankit_cheeni 

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?

rob_marshall

@ankit_cheeni @Naoto 

 

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

ChrisAHeathcote

@Naoto 

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.

 

ankit_cheeni

Hi Rob
Thanks so much! I see how recklessly inefficient TIMESUM could've been. One learns something everyday eh! Thanks again

ChrisAHeathcote

I realised there was an error so I updated the suggest formula.

rob_marshall

@ankit_cheeni 

 

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

Naoto

@ChrisAHeathcote 

Thank you very much! I understand moving average calculation.

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

 

ChrisAHeathcote

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

Example; user selects 3.

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

Naoto

Thank you.

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

I continue to consider how to calculate.

ChrisAHeathcote

You need to calculate over a series of line items as suggested in your original post.