trying to compute mean based on previous rows' values (need to retrieve historic values)

[#1]

Hi - I am trying to figure out how to get jsl to retrieve prior rows' values (values from previous 4 rows) to use in a formula to compute the mean of those rows. For example, let's say my column A has the following values 3,3,2,8,0,2,2.    I'd like the formula in column B to compute the mean of column A using values from its prior 4 rows.   The mean function is straightforward but having I haven't figured out how to get it to read and use only a subset of past data.

Ex:   

colA   colB   

3

3

2

8              

0         4

2         4

2         3         

Thanks in advance for any help on this!

[#2 · Accepted solution]

Hi,

Have you looked into the Lag () function? You can indicate the offset of the rows as shown below. 

If( Row() < 4,
	.,
	Mean( Lag( :Column 1, 3 ), Lag( :Column 1, 2 ), Lag( :Column 1, 1 ), :Column 1 )
)

There might be more elegant ways to do this, but this will work.

Best,

TS

[#3]

Nice and simple.  It does not seem to be working when I use the new column function.  That is, nothing is being populated in my new column (the column holding the mean formula).  Here is my script:   

New Column( "hrly turb current",
numeric,
continuous,
Mean( Lag( :pearson_turb_fnu, 4 ), Lag( :pearson_turb_fnu, 3 ),
Lag( :pearson_turb_fnu, 2 ), Lag(:pearson_turb_fnu, 1) )
);

Am I applying it correctly?

[#4]

I will suggest that you look into the Col Moving Average() function

Names Default To Here( 1 );
Open( "$SAMPLE_DATA/Big Class.jmp" );
new column("mov avg",formula(
Col Moving Average( :height, 1, 4, 0)));

[#5]

Thanks so much Jim.  I'll check it out.  I also got the Mean (lag) formula to work.   : ) 

[#6]

Seems I just needed to add the word "formula" to the equation for it to work properly.  Thanks very much TS!