How to directly calculate the number of consecutive conditions?

lwx228

For example, take "height" as an example and count the consecutive number of "height"s in the row greater than the "height" in the previous row.

 

I added an auxiliary column h to determine whether the "height" of the row is greater than the "height" of the previous row, and then counted the consecutive "height" of the row greater than the "height" of the previous row in the other column.If the "height" is less than the "height" of the previous row  have to recalculate.

 

• How can do this calculation in one step?

• I tried this, but it didn't work:

• if(:height>Lag(:height,1),row()-Contains(:height[Index(1,Row())],:height<Lag(:height,1),-1))

• Thanks!

txnelson

The problem has a fairly simple solution:

If( Row() == 1,
	hCounter = 0,
	If( :height > Lag( :height, 1 ),
		hCounter++,
		hCounter = 0
	)
);
If( hCounter == 0,
	.,
	hCounter
);

This version will produce missing values when there are 0 lags that meet the condition, just like your 2 column solution.  If you want the resulting value to be a zero and not a missing value, just use:

If( Row() == 1,
	hCounter = 0,
	If( :height > Lag( :height, 1 ),
		hCounter++,
		hCounter = 0
	)
);

hCounter;

Here is a complete comparison of the 2 methods

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

dt << New Column( "h",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( If( Row() == 1, 0, :height > Lag( :height, 1 ) ) )
);
	
dt << New Column( "Two Columns Used",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( If( :h == 1, Row() - Contains( :h[Index( 1, Row() )], 0, -1 ) ) )
);
dt << New Column( "All in one",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula(
		If( Row() == 1,
			hCounter = 0,
			If( :height > Lag( :height, 1 ),
				hCounter++,
				hCounter = 0
			)
		);
		If( hCounter == 0,
			.,
			hCounter
		);
	)
);