How to get moving standard deviation

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?

Accepted answers

All comments

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?

Image_124349.PNG

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.

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…