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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Standard Deviation on last 3 rows

Meteor

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

Meteor

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).

 

Meteor

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 

Meteor

That is perfect, Than kyou Sir.

Labels