topic Re: trying to compute mean based on previous rows' values (need to retrieve historic values) in Discussions

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

learning_JSL — Fri, 09 Jun 2023 18:02:02 GMT

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

0 4

2 4

2 3

Thanks in advance for any help on this!

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

Thierry_S — Mon, 11 Oct 2021 18:59:28 GMT

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,

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

learning_JSL — Mon, 11 Oct 2021 19:16:18 GMT

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)

txnelson — Mon, 11 Oct 2021 19:32:34 GMT

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)

learning_JSL — Mon, 11 Oct 2021 20:45:59 GMT

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

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

learning_JSL — Mon, 11 Oct 2021 20:47:20 GMT

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