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

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

Feb 13, 2014 10:38 AM
(3919 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

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

Feb 14, 2014 7:40 AM
(6178 views)
| Posted in reply to message from david_t_pitchfo 02/13/2014 01:38 PM

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

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

Feb 13, 2014 11:13 AM
(3545 views)
| Posted in reply to message from david_t_pitchfo 02/13/2014 01:38 PM

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)^

**)**

**)**

**)**;

**)**;

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

Feb 14, 2014 7:40 AM
(6179 views)
| Posted in reply to message from david_t_pitchfo 02/13/2014 01:38 PM

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

)))

);

);

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

Feb 14, 2014 8:30 AM
(3545 views)
| Posted in reply to message from david_t_pitchfo 02/14/2014 10:40 AM

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**;

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

Feb 14, 2014 8:36 AM
(3545 views)
| Posted in reply to message from david_t_pitchfo 02/14/2014 10:40 AM

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**]**

**)))**

**)**;

**)**;