JSL- Formula for calculating column average for last x rows per ID

AvgLlama27 — Wed, 25 Sep 2024 14:06:49 GMT

I am trying to create a column formula to calculate the average for the last 30 sets of data per cycle run (shown in the example below):

I have been able to achieve this, however I would like to be able to calculate the average per "ID" rather than the entire data set per test cycle.

The formula I have used is shown below:

If( Row() == Col Max( Row(), :Cycle ),
	Mean( As Column( "Output, mV" )[Index( Row() - 29, Col Max( Row(), :Cycle ) )] )
)

...but I am unsure of where to go next.

Thanks in advance.

Re: JSL- Formula for calculating column average for last x rows per ID

jthi — Wed, 25 Sep 2024 15:00:58 GMT

Have you checked out if Col Moving Average could do what you are looking for?

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

dt << new column("avg", Numeric, Continuous, Formula(
	If(Row() == Col Max(Row(), :sex),
		Col Moving Average(:height, 1, 9, 0, 1, :sex);
	);
));

topic JSL- Formula for calculating column average for last x rows per ID in Discussions

JSL- Formula for calculating column average for last x rows per ID

Re: JSL- Formula for calculating column average for last x rows per ID