Subscribe Bookmark RSS Feed

Creating a running total of a column

david_t_pitchfo

Community Trekker

Joined:

Feb 10, 2014

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
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 REPLIES
ms

Super User

Joined:

Jun 23, 2011

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

  )

  )

  );

);

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

ms

Super User

Joined:

Jun 23, 2011

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;

ms

Super User

Joined:

Jun 23, 2011

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]

        )))

);

);