Community Trekker

Joined:

Sep 25, 2017

Formula Col Min() Returning Subsequent Value Assistance

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.

1 ACCEPTED SOLUTION

Accepted Solutions

Super User

Joined:

Jun 22, 2012

Solution

Re: Formula Col Min() Returning Subsequent Value Assistance

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

Super User

Joined:

Jun 22, 2012

Solution

Re: Formula Col Min() Returning Subsequent Value Assistance

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

Community Trekker

Joined:

Sep 25, 2017

Re: Formula Col Min() Returning Subsequent Value Assistance

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

Community Trekker

Joined:

Sep 25, 2017

Re: Formula Col Min() Returning Subsequent Value Assistance

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

Super User

Joined:

Jun 22, 2012

Re: Formula Col Min() Returning Subsequent Value Assistance

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