801763_en-US

liliconcom

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

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.

 

Automation and ScriptingBasic Data Analysis and ModelingRe: JSL- Formula for calculating column average for last x rows per ID

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);
	);
));