Is there a way to make JMP evaluate column formulas starting at the bottom row and then working up (as opposed to the default top row first)?
The attached image is an example formula I made. For each row, it populates itself with the value in the "data" column, unless the "data" value is missing; in that case, it populates itself with the next value in the "formula" column (i.e. itself). Ideally, I would like the missing values currently shown in the column named "formula" to populate with "7", but because the formula evaluates starting at the top row, the next value in the "formula" column is missing, until it gets to row 7. If this formula evaluated starting at the bottom row and going up, then Lag(:"formula",-1) would never return any missing values. Thoughts?
NOTE: If I keep pressing the "Apply" button in the column formula GUI, eventually the "formula" column does fill in "7"s for rows 1-7 one at a time, but I can't actually do that in more complex applications where I don't know how many rows of empty data there are.
Here are two different ways to do what you want
Names Default To Here( 1 );
dt = New Table( "Example",
Add Rows( 12 ),
New Column( "data",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [., ., ., ., ., ., ., 7, 8, 9, 10, 11] )
)
);
// Method one
dt << New Column( "Row", formula( Row() ) );
dt:Row << delete formula;
dt << sort( by( :Row ), order( descending ), replace table( 1 ) );
dt << New Column( "formula1",
formula( If( Is Missing( :data ), Lag( :data ), :Data ) )
);
dt:formula1 << delete formula;
dt << sort( by( :Row ), order( ascending ), replace table( 1 ) );
dt << delete columns( {Row} );
// Method two
dt << New Column( "formula2",
formula(
If( Row() == 1,
dMat = :data << get as matrix;
fMat = Matrix( Index( 1, N Rows( dMat ) ) );
fMat[0] = .;
fMat = fMat`;
For( i = N Rows( fMat ), i >= 1, i--,
If( Is Missing( dMat[i] ),
fMat[i] = dMat[i + 1],
fMat[i] = dMat[i]
)
);
);
fMat[Row()];
)
);
