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

- JMP User Community
- :
- Discussions
- :
- Is there any way to quickly get the number of negative values in a column?

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

Highlighted

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

Oct 10, 2018 1:00 AM
(4576 views)

Hello, everyone!

I need to count the number or percentage of negative Numbers on the "Arrival Delay" column.

This functionality does not appear to be found in the JMP's menu operations and formulas.

There is a =countif(,"<0") in excel that can do quickly.

JMP wants to use a loop?

dt = Open( "$SAMPLE_DATA/Airline Delays.jmp" );

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

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

Use a Transformation variable with a specified formula of

If(:Arrival Delay < 0, 1, 0)

Then use Sum as the statistic in the Summary Platform

Jim

Highlighted

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

Jim's answer is great; here are two more that avoid creating a column but have different side effects:

```
dt = Open( "$sample_data/big class.jmp" );
// using table subscripts, suitable for use in a column formula.
// As Constant calculates the result once, not for every row.
As Constant( N Rows( Loc( dt[0, "age"] < 15 ) ) ); // 27
// using row selection, suitable for JSL programs. Avoids a temporary
// matrix but does change the selection state of the table.
dt << selectwhere( age < 15 );
N Rows( dt << getselectedrows ); // 27
```

What you choose depends a bit on whether you'll use it in a column formula or in a JSL program; JMP added the As Constant function (mostly) to support column formulas that need to make an expensive calculation. Without it, JMP would count the number of rows for every row, leading to N^2 performance issues.

Explaining the first example: dt[0,"age"] returns a matrix from the table (post). 0 means all rows. Comparing that matrix with < 15 returns a matrix of 0 and 1 results. Loc([ 1 0 1 0]) returns a smaller matrix of the locations of the non zero (true) results. NRows or NItems reports the number of elements. As Constant isn't needed in a JSL program if you capture the answer in a variable.

Craige

5 REPLIES 5

Highlighted

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

Use a Transformation variable with a specified formula of

If(:Arrival Delay < 0, 1, 0)

Then use Sum as the statistic in the Summary Platform

Jim

Highlighted
##

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

Re: Is there any way to quickly get the number of negative values in a column?

Thank Jim!

That seems to be the only way.

That seems to be the only way.

Highlighted

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

Jim's answer is great; here are two more that avoid creating a column but have different side effects:

```
dt = Open( "$sample_data/big class.jmp" );
// using table subscripts, suitable for use in a column formula.
// As Constant calculates the result once, not for every row.
As Constant( N Rows( Loc( dt[0, "age"] < 15 ) ) ); // 27
// using row selection, suitable for JSL programs. Avoids a temporary
// matrix but does change the selection state of the table.
dt << selectwhere( age < 15 );
N Rows( dt << getselectedrows ); // 27
```

What you choose depends a bit on whether you'll use it in a column formula or in a JSL program; JMP added the As Constant function (mostly) to support column formulas that need to make an expensive calculation. Without it, JMP would count the number of rows for every row, leading to N^2 performance issues.

Explaining the first example: dt[0,"age"] returns a matrix from the table (post). 0 means all rows. Comparing that matrix with < 15 returns a matrix of 0 and 1 results. Loc([ 1 0 1 0]) returns a smaller matrix of the locations of the non zero (true) results. NRows or NItems reports the number of elements. As Constant isn't needed in a JSL program if you capture the answer in a variable.

Craige

Highlighted
##

I use it in JSL.

Please give me specific instructions.

Thank Craige!

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

Re: Is there any way to quickly get the number of negative values in a column?

I use it in JSL.

Please give me specific instructions.

Thank Craige!

Highlighted
##

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

Re: Is there any way to quickly get the number of negative values in a column?

dt = Open( "$sample_data/Airline Delays.jmp" );

s=As Constant( N Rows( Loc( dt[0, "Arrival Delay"] < 0 ) ) );Wait(0);

good method!!

s=As Constant( N Rows( Loc( dt[0, "Arrival Delay"] < 0 ) ) );Wait(0);

good method!!

Article Labels

There are no labels assigned to this post.