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.

在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

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

UseTIMESUMwhen 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 Aggregationhere

[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-TIMESU...

Thanks,

Rob

[ankit_cheeni]

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

[rob_marshall]

@ankit_cheeni

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

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.总结设置设置为和。

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.

[ChrisAHeathcote]

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

[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

星期4的标准偏差的计算方法是基于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

星期4的标准偏差的计算方法是基于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