turn on suggestions

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

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Running Count of Repeated Values JSL Script?

Topic Options

- 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

Mar 24, 2015 1:36 PM
(4969 views)

I would like to script a running count or cumulative sum of repeated values within a column.

For example, first appearance should equal 1, max value of count by value specified should equal the cumulative sum of the value within the column.

color | count_color |
---|---|

RED | 1 |

RED | 2 |

BLUE | 1 |

YELLOW | 1 |

RED | 3 |

Ideally, the output for :count_color would not need the data table to be sorted by :color

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions

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

Creating a new column and assigning it the formula below does what you're after, I think:

`Col Cumulative Sum( 1, :Color )`

6 REPLIES

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

Try this expression in a column formula (or in a For Each Row() loop);

Eval**(**Eval Expr**(**Col Sum**(**Row**()** <= Expr**(**Row**())**, :color**)))**;

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

Using the Eval command under formula generates an output, but all values = 0

Perhaps I have misinterpreted. My column formula is below.

// Running Count

New Column("count_color",

numeric,

continuous,

formula(

Eval(Eval Expr(Col Sum(Row() <= Expr(Row()), :color)))

)

);

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

Either paste the formula from **MS** into the formula editor or wrap the formula in ** NameExpr** (longer explanation here):

New Column( "count",

Numeric,

"Continuous",

Format( "Best", 12 ),

Formula( Name Expr( Eval( Eval Expr( Col Sum( Row() <= Expr( Row() ), :color ) ) ) ) )

),

There is a bug in JMP that you've helped uncover; the resulting table does not include the Name Expr( ) wrapper around the formula when the table's <<getscript command is used. Thanks!

Craige

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

Hi @Craige_Hales,

I am doing a similar count of all the numbers that are not zero, or else it will be changed to 1.

If( :Number != 0,

Name Expr( Eval( Eval Expr( Col Sum( Row() <= Expr( Row() ), :Number ) ) ) ),

1

);

This doesn't seem to work. I get black spaces for non zero numbers. Can you help me with this? Thank you.

I am doing a similar count of all the numbers that are not zero, or else it will be changed to 1.

If( :Number != 0,

Name Expr( Eval( Eval Expr( Col Sum( Row() <= Expr( Row() ), :Number ) ) ) ),

1

);

This doesn't seem to work. I get black spaces for non zero numbers. Can you help me with this? Thank you.

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

**EDIT:** May want to ignore this. This gives a total count of each item that can be used in a script later on, but doesn't actually address the OP's real question.

A somewhat more roundabout way to do this is to use the Summary platform and then read the resulting table into whatever script object you would like. I've used an associative array here.

```
// create temporary summary table
new = Data Table( "Untitled 8" ) << Summary( Group( :color ) );
// make sure "Untitled 8" is the correct title of the original data, containing a column named "color"
```

// transfer result to associative array
result = Associative Array(); // empty A.A.
For Each Row(new, result << Insert Item(:color, :N Rows)); // populate A.A.
Show(result); // display A.A. to log
Close(new, No Save); // close temporary summary table

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

Creating a new column and assigning it the formula below does what you're after, I think:

`Col Cumulative Sum( 1, :Color )`