trying to compute mean based on previous rows' values (need to retrieve historic values)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!
ScriptingRe: trying to compute mean based on previous rows' values (need to retrieve historic values)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
Re: trying to compute mean based on previous rows' values (need to retrieve historic values)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)));Re: trying to compute mean based on previous rows' values (need to retrieve historic values)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?
Re: trying to compute mean based on previous rows' values (need to retrieve historic values)Thanks so much Jim. I'll check it out. I also got the Mean (lag) formula to work. : )
Re: trying to compute mean based on previous rows' values (need to retrieve historic values)Seems I just needed to add the word "formula" to the equation for it to work properly. Thanks very much TS!
