topic Re: Counting Formulas in Discussions

Counting Formulas

DELANDJ1985 — Thu, 10 Oct 2024 15:24:16 GMT

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

Re: Counting Formulas

jthi — Thu, 10 Oct 2024 15:37:17 GMT

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

Re: Counting Formulas

txnelson — Thu, 10 Oct 2024 16:47:42 GMT

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;

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;

Re: Counting Formulas

SDF1 — Thu, 10 Oct 2024 17:31:31 GMT

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!,