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
- :
- Creating a running total of 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

Feb 13, 2014 10:38 AM
(7826 views)

I am wondering how to create a column in a JMP table that is the running total of another column (which I may need to reference via the column name as a string). That is, if I have a column with the following data:

1

2

3

4

5

I would want to create a column with a formula whose contents would work out to

1

3

6

10

15

Currently I have this loop, working from a list of names of columns to take the running total of:

For(i = 2, i <= numCols, i++,

colName = colNames*;*

col = New Column("Cumulative " || colName,

Formula(Summation(j = 1, Row(), As Column(ColName)

);

);

This appears to work, but is very slow and if I create multiple columns, they all end up with the same data somehow. What is the best way to do this?

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

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

I feel like if there is a way to get JSL to do what you want by parsing a string, then there should be away to do it without using the Parse function, with plain JSL code (which, after all, is just a string). This is what I got to work using Substitute:

For(i = 2, i <= numCols, i++,

colName = colNames*;*

newName = "Cumulative " || colName;

col = New Column(newName, Numeric, Continuous,

Formula(Eval(Substitute(

Expr(Summation(j = 1, Row(), As Column(xxx)

Expr(xxx), colName

)))

);

);

4 REPLIES 4

Highlighted
##

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

Re: Creating a running total of a column

Formula() does not evaluate its argument, so all column formulas will contain the variables ColName and J and update as the loop proceeds.

Here is one way to get around this:

For**(** i = **2**, i <= numCols, i++,

colName = colNames**[**i**]**;

col = New Column**(** "Cumulative " || colName, Numeric **)**;

Eval**(**

Parse**(**

Eval Insert**(**

"col<< Set Formula(Summation(j = 1, row(), ^(ColName)^

**)**

**)**

**)**;

**)**;

Highlighted

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

I feel like if there is a way to get JSL to do what you want by parsing a string, then there should be away to do it without using the Parse function, with plain JSL code (which, after all, is just a string). This is what I got to work using Substitute:

For(i = 2, i <= numCols, i++,

colName = colNames*;*

newName = "Cumulative " || colName;

col = New Column(newName, Numeric, Continuous,

Formula(Eval(Substitute(

Expr(Summation(j = 1, Row(), As Column(xxx)

Expr(xxx), colName

)))

);

);

Highlighted
##

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

Re: Creating a running total of a column

Yes, that should work as long as the columns never are renamed.

This code illustrates the difference between the two approaches. The ugly string method hard codes the formula to a specific column. The second method looks for a column with a specific name (which could be useful)

// Example table

dt = New Table**(** "Test",

Add Rows**(** **4** **)**,

New Column**(** "Col1", Numeric, Set Values**(** **[****1**, **2**, **3**, **4****]** **)** **)**

**)**;

// Add formula by parsed string

colNames = dt << **get column names****(** string **)**;

numCols = N Items**(** colNames **)**;

For**(** i = **1**, i <= numCols, i++,

colName = colNames**[**i**]**;

col = New Column**(** "Cumulative " || colName, Numeric **)**;

Eval**(**

Parse**(**

Eval Insert**(**

"col<< Set Formula(Summation(j = 1, row(), ^(ColName)^

**)**

**)**

**)**;

**)**;

// Add formula by substituting expression

For**(** i = **1**, i <= numCols, i++,

colName = colNames**[**i**]**;

newName = "Cumulative " || colName;

col = New Column**(** newName,

Numeric,

Continuous,

Formula**(**

Eval**(**

Substitute**(**

Expr**(**

Summation**(** j = **1**, Row**()**, As Column**(** xxx **)[**j**]** **)**

**)**,

Expr**(** xxx **)**, colName

**)**

**)**

**)**

**)**;

**)**;

Wait**(** **1** **)**;

// Change column name and a value and look how formulas work

Column**(** **1** **)** << **set name****(** "New Name" **)**;

Column**(** **1** **)[****1****]** = **100**;

Wait**(** **2** **)**;

// Add new column with same name as the original column.

dt << **New Column****(** "Col1", numeric, values**(** **[****1**, **1**, **1**, **1****]** **)** **)**;

dt << **rerun formulas**;

Highlighted
##

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

Re: Creating a running total of a column

Btw, if the list of colNames contain names instead of strings, this approach seems to give the same result as the parsed-string:

colNames = dt << **get column names****(** **)**; // Without the "string" option!

numCols = N Items**(** colNames **)**;

For**(**i = **1**, i <= numCols, i++,

colName = colNames**[**i**]**;

newName = "Cumulative " || **(**colName<<**get name****)**;

col = New Column**(**newName, Numeric, Continuous,

Formula**(**Eval**(**Substitute**(**

Expr**(**Summation**(**j = **1**, Row**()**, xxx**[**j**]))**,

Expr**(**xxx**)**, colNames**[**i**]**

**)))**

**)**;

**)**;