Subscribe Bookmark RSS Feed

Dynamically create new variable based on differences between rows

terapin

Community Trekker

Joined:

Jun 23, 2011

I'm relatively new to complex JMP formulas that probably involve local variables and have a question about dynamically creating and using local variables in a column formula.

I have a data series that increases or decreases over time and has several resets in the data series. The data resets are when the instrument is reset to something near its' lower measurement range.

I want to be able to adjust the data so that: 1) all data prior to a reset are unadjusted and brought over into a new column variable, 2) the first reading after a reset is set to the last data value prior to the reset, 3) the readings after the reset are the result of the difference between two adjacent rows added to the adjusted data in the previous row. This needs to be done with multiple data resets and with data that has a positive and negative slope to it.

An example of the type of data for both a positive and negative sloped data series is show below.

Any suggestions on how to accomplish this using a JMP column formula would be greatly appreciated.

Date Existing + Slope Variable Existing - Slope Variable Adjusted + Slope Variable Adjusted - Slope Variable
1/1/2011 1 6 1 28
1/2/2011 2 5 2 27
1/3/2011 3 3 3 25
1/4/2011 3 2 3 24
1/5/2011 4 1 4 23
1/6/2011 6 1 6 23
1/7/2011 7 12 7 23
1/8/2011 8 11 8 22
1/9/2011 8 10 8 21
1/10/2011 10 7 10 18
1/11/2011 11 6 11 17
1/12/2011 0 5 11 16
1/13/2011 1 3 12 14
1/14/2011 1 2 12 13
1/15/2011 2 1 13 12
1/16/2011 3 1 14 12
1/17/2011 5 0 16 11
1/18/2011 6 11 17 11
1/19/2011 7 10 18 10
1/20/2011 10 8 21 8
1/21/2011 11 8 22 8
1/22/2011 12 7 23 7
1/23/2011 1 6 23 6
1/24/2011 1 4 23 4
1/25/2011 2 3 24 3
1/26/2011 3 3 25 3
1/27/2011 5 2 27 2
1/28/2011 6 1 28 1
1 REPLY
gh

Community Trekker

Joined:

Jun 23, 2011

try the lag function, or simply subscripts