Counting Formulas

Question

Looking to create 3 cols by col variable to could down with change, count up and count up/down from mid point.

Count Template.jmp

SDF1 · Answer

Hi @DELANDJ1985 ,

I had a slightly different take on how to do it than @jthi and @txnelson . See the attached jmp file where I defined separate column formulas for each counting scheme. I did each one slightly differently so you could take the concept and perhaps modify it to your specific issue at hand. There might even be more than one possible answer to your query!

You don't have to make a column formula, but could script it up and run it as a script so the column isn't locked. But, that detail depends on the needs of your specific scenario.

Hope this helps!,

DS

Count Template_mod.jmp

txnelson · Answer

Jarmo's response is the correct answer, if the Lots are always contiguous.   However, if the grouping column is not contiguous, such as

A
A
A
B
B
A
A
C
C
C

then the provided formulas will not be correct.  Here are alternative formulas that handle noncontiguous groupings

Down

As Constant( count = 0 );
If( :Lot != Lag( :Lot ),
	count
	++);
count;

Up

As Constant(
	count = 0;
	For Each Row( If( :Lot != Lag( :Lot ), count++ ) );
	count = count + 1;
);
If( :Lot != Lag( :Lot ),
	count
	--);
count;

Middle

As Constant(
	count = 0;
	For Each Row( If( :Lot != Lag( :Lot ), count++ ) );
	count = count + 1;
	count = -1 * Ceiling( count / 2 );
);
If( :Lot != Lag( :Lot ),
	count
	++);
count;

jthi · Answer

Is something like this what you are looking for?

"Down"

Col Cumulative Sum(Row() == Col Min(Row(), :Lot))

"Up"

Col Sum(Row() == Col Min(Row(), :Lot)) - Col Cumulative Sum(Row() == Col Min(Row(), :Lot)) + 1

Middle

Col Cumulative Sum(Row() == Col Min(Row(), :Lot)) - Col Sum(Row() == Col Min(Row(), :Lot)) + 1

Count Template.jmp