topic Creating a running total of a column in Discussions

Creating a running total of a column

david_t_pitchfo — Thu, 13 Feb 2014 18:38:55 GMT

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:

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

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?

Re: Creating a running total of a column

ms — Thu, 13 Feb 2014 19:13:42 GMT

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

)

);

Re: Creating a running total of a column

david_t_pitchfo — Fri, 14 Feb 2014 15:40:44 GMT

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

Re: Creating a running total of a column

ms — Fri, 14 Feb 2014 16:30:46 GMT

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;

Re: Creating a running total of a column

ms — Fri, 14 Feb 2014 16:36:55 GMT

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]

)))

);