nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Creating a running total of a column

david_t_pitchfo

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?

Accepted answers

david_t_pitchfo

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

All comments

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

)

);

david_t_pitchfo

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;

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]

)))

);