Can I get a "rolling standard deviation" as a formula a table?

[#1]

I have a time series data set, and what I'd like to do is see if the value of one column (a key performance indicator, KPI) is related to the variability in another column (a process variable, PV).  I think what I'd like to do is to calculate the standard deviation across the n previous and m future data points to get an estimate of how much the PV is moving about and then plot that as x vs. the KPI as y...  I'm hoping to show that the KPI goes up as the variability increases.

I can do this easily in Excel and then import into JMP, but I'm sure there's a way to do it with a JMP column formula... I'm just too dumb to work out how

Thanks in advance for any help

Charles

[#2]

This should work for what you want

Std Dev( :height[Index( 1, Row() )] )

or to do a rolling 6 row STD DEV

Std Dev( :height[Index( Row() - 5, Row() )] )

[#3]

Thanks Jim

I'm embarrassed to say that I don't quite understand your answer, though... 

I don't seem to be able to copy this into my column formula and I can't seem to find the :height function, so I'm guessing that this is JSL? 

If so, is there a way to get the values a few rows back and a few rows forward?  If I understand the Index function, that gives me a series of integers, so I think that INDEX(1,Row() ) would give me a series of numbers from 1 to my current row?

Thanks again 

Charles

[#4 · Accepted solution]

This is a formula.  My error in assuming that you would understand that :Height is a reference to the column to perform the standard deviations on.  The ":" in front of the column name is a scoping element, telling JMP that Height is a column in the data table, not a memory variable.  The Height column is found in the Big Class sample data table.  It is probably the most familiar data table to the JMP Community.  It is used a lot to illustrate how to do different things in JMP.  It is available in the Sample Data Library under the Help pull down menu.

To copy the formula into the Formula Editor, open the editor( create a new column, go to the column header and right click and select "Formula")

move your cursor to the "no formula" box,

and right click and "Paste" the text into the box

The Formula Editor creates an enhanced display of the formula.  To edit the formula further, double click on any blank space within the formula, and an editor window will pop up

The Formula Editor is a complete editor which can be used in an interactive form to build column formulas.  What I am illustrating above is how to paste an already defined formula into the editor.

[#5]

Thank you!

[#6]

I was looking for a means to calculate a rolling standard deviation and came across your solution.  Thanks.  I set up :Winsize as a table variable (which I set equal to 10).

If( :Sample ID >= :WinSize,
	Std Dev( :Lvalue[Index( (Row() + 1) - :WinSize, Row() )] )

However, I have noticed that Excluded rows are used in the StdDev calculation.  Is there a way to leave out the Excluded rows?  assuming there are N_Excl rows in the current rolling data window, two different implementations occur to me:

1. report the standard deviation based on these :WinSize-N_Excl data points
2. extend the window so that there are :WinSize "good" data points in the window

I'm unsure how to implement either.

Thanks for considering!

[#7]

Can you add a by group option?

[#8]

@AdamChoen Here is an example with a by, using the Big Class sample data table.  The data needs to be sorted by the "By" column, so, open the table, sort the data by the Sex variable.  Then create an new column, and set the new column's formula to

As Constant(
	rollingSize = 5;
	theStart = 1;
);
If( Lag( :sex ) != :sex,
	theStart = Row()
);
If( (Row() + 1) - RollingSize >= theStart,
	Std Dev( :weight[Index( (Row() + 1) - rollingSize, Row() )] );
,
	.
);