cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

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 Name Total Points Accepted This Sprint Velocity (avg of last 3 Iter) Velocity Variance (   STDEV.S(of last 3 iterations)/AVERAGE(of last 3 iterations)   ) PI4 IT5 22 PI4 IT6 10 PI1 IT1 20 PI1 IT2 27 PI1 IT3 28 PI1 IT4 27 PI1 IT5 27 PI1 IT6 13 PI2 IT1 22 PI2 IT2 32

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

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:

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

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

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