45105_en-US

liliconcom

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.

 

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.

Re: Formula Col Min() Returning Subsequent Value AssistanceI think maybe the issue is arising because the IDs are not sequential numbers?Re: Formula Col Min() Returning Subsequent Value AssistanceThank 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).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