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?

Standard Deviation on last 3 rows

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?

Workflow

Datasets

Developer

Reporting

Best Practices

Data Investigation

Accepted answers

JohnJPS

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

All comments

JohnJPS

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

StdDevLastThree.yxmd

dlesny

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

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

JohnJPS

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

StdDevLastThreeCurr.yxmd

dlesny

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?

JohnJPS

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280