Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Fisitron
Level I

Evalute column formulas bottom up?

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.

1 REPLY 1
Highlighted
txnelson
Super User

Re: Evalute column formulas bottom up?

Here are two different ways to do what you want

formula12.PNG

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