community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Standard Deviation on last 3 rows

Asteroid

Hello. I am trying to calculate StnDev for the Team's Velocity, which is StnDv from Avg of last 3 Itertions.

Iteration NameTotal Points Accepted This SprintVelocity (avg of last 3 Iter)Velocity Variance (   STDEV.S(of last 3 iterations)/AVERAGE(of last 3 iterations)   )
PI4 IT522  
PI4 IT610  
PI1 IT120  
PI1 IT227  
PI1 IT328  
PI1 IT427  
PI1 IT527  
PI1 IT613  
PI2 IT122  
PI2 IT232  

 

I found the Velocity which I can do with Multi Row Formula:

AVERAGE([Row-1:Total Points Accepted This Sprint],[Row-2:Total Points Accepted This Sprint],[Row-3:Total Points Accepted This Sprint])

 

But how to do the Velocity Variancefor the last 3 iterations?

 

Highlighted
ACE Emeritus
ACE Emeritus

Hi @dlesny,

 

The Variance of that Velocity would be another multi-row formula, and the StdDev would be the square root of the variance; I've attached an example using your data, which hopefully looks kind of like what you're after.

 

Hope that helps!

John

Asteroid

Thank you for your help. I am not getting quite the same results like Excel. STDEV.S uses the following formula:

Formulaso =STDEV.S(B10:D10) (32,22,13) returns 9.504384953 and with this workflow 7.79601

 

 

 

 

ACE Emeritus
ACE Emeritus

I think it's due to me focusing on the row-1/row-2/row-3 for "previous 3"... if I change it to currentRow/row-1/row-2, then I can get 7.79601 to appear in the output. (Please see attached).  So, it really depends on what you want to compare to: deviation from previous (my first approach), or deviation when including your in result in calculation of the previous (the new approach).

 

Asteroid

Hi John

Thank you for your help again.

I have spoted that and altered before, that's not the issue however.

For (32,22,13) Excel returns 9.504384953 with STDEV.S

I need Alteryx to return the same value with the same data and atm it returns 7.79601. Any idea?

 

 

ACE Emeritus
ACE Emeritus

I think I was overthinking things with multiple Multi-Row formulas.  To reproduce that, we can just plug it into a single such formula:

SQRT(
  (POW([Points]       - AVERAGE([Points],[Row-1:Points],[Row-2:Points]),2) +
   POW([Row-1:Points] - AVERAGE([Points],[Row-1:Points],[Row-2:Points]),2) +
   POW([Row-2:Points] - AVERAGE([Points],[Row-1:Points],[Row-2:Points]),2)
  )/2
)

Hope that helps!

John 

Asteroid

That is perfect, Than kyou Sir.

Labels