Subscribe Bookmark RSS Feed

Formula Col Min() Returning Subsequent Value Assistance

patrickw

New Contributor

Joined:

Sep 25, 2017

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.

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

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

Jim
4 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

Solution

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

Jim
patrickw

New Contributor

Joined:

Sep 25, 2017

Thank you for your prompt response. When I apply the suggested formula it appears to terminate after the first ID and does not repeat for subsequent IDs (i.e., I am only getting a result returned on row 17).
patrickw

New Contributor

Joined:

Sep 25, 2017

I think maybe the issue is arising because the IDs are not sequential numbers?
txnelson

Super User

Joined:

Jun 22, 2012

Did you look at the data table I attached.  It had values in row 17 and in row 38.  Have you looked into the log to see if errors are occurring?

 

The IDs do not have to be sequential.  Take a look at the formula, and from it you should be able to see how it is working.

Jim