Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Formula Col Min() Returning Subsequent Value Assis...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 25, 2017 6:13 PM
(444 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 25, 2017 6:49 PM
(856 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 25, 2017 6:49 PM
(857 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 25, 2017 7:38 PM
(431 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 25, 2017 7:50 PM
(427 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 25, 2017 7:56 PM
(425 views)

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