Evalute column formulas bottom up?

Fisitron

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.

bottum up column formula.PNG

txnelson

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