Formula Col Min() Returning Subsequent Value Assistance

patrickw

Hello,

 

I am quite new to JMP. I have a data set similar to the one shown here for 4000 ID's. Each ID has a Count value for several different dates, and I have the set sorted by ID first then by Date.

 

I am interested in generating a formula that will result a 1 next to the first Count value >=500 that occurs after the 0's within each ID (only when there are 3 consecutive Count values >=500). With this logic, the formula should return a 1 at row 17 and 38 (NOT 35 because there are not 3 consecutive values >=500) in the example provided. I have attempted a stacked If( row()==Col Min(row(), ID)) strategy but have been unsuccessul. Any and all help is greatly appreciated. Thank you.

 

IfColMinExample.jmp

txnelson

Here is my version of your requested formula

If( Row() == 1,
	zero = 0;
	found = 0;
);
If( Lag( :ID ) != :ID,
	zero = 0;
	found = 0;
);
If( :Count == 0, zero = 1 );
If( zero == 1 & :Count >= 500 & :Count[Row() + 1] >= 500 & :Count[Row() + 2] >= 500 & found == 0,
	found = 1;
	1;
,
	.
);

And attached is your sample data table with the formula applied

IfColMinExample.jmp