How to get moving standard deviation

Options
Naoto
Naoto Member, ALL USERS, Partner, Certified Model Builder Posts: 4 Occasional Contributor
in Planning & Modeling

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?

Tagged:

Comments

  • Naoto
    Naoto Member, ALL USERS, Partner, Certified Model Builder Posts: 4 Occasional Contributor

    @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?

  • Naoto
    Naoto Member, ALL USERS, Partner, Certified Model Builder Posts: 4 Occasional Contributor

    @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

     

  • Naoto
    Naoto Member, ALL USERS, Partner, Certified Model Builder Posts: 4 Occasional Contributor

    Thank you.

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

    I continue to consider how to calculate.

Categories