Creating a running total of a column<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />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?
Re: Creating a running total of a columnBtw, 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]
)))
);
);
Re: Creating a running total of a columnYes, 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 columnI 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 columnFormula() 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)^))"
)
)
);
);
