Creating a running total of a column

[#1]

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?

[#2]

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

  )

  )

  );

);

[#3 · Accepted solution]

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]

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;

[#5]

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]

        )))

);

);