topic Re: How do you write a column formula which can group by rows defined by another column? in Discussions

How do you write a column formula which can group by rows defined by another column?

LauraW — Mon, 08 Sep 2025 15:43:24 GMT

Hi,

I would like to be able to write column formulae which can perform various column formula functions within groups defined by rows in, for example, a column called 'group'. There are various mentions of the 'group by' button when using gemini, but I cannot find this.

For example, I would like to calculate the first cycle in the below table when the retention first passes 60 % (cycle where retention <= 60 %) for each group, A, B, and C.

How can I go about doing this? Thanks for the help :)

My JMP version is JMP18 .

Re: How do you write a column formula which can group by rows defined by another column?

mmarchandFSLR — Mon, 08 Sep 2025 16:08:54 GMT

Here's what I came up with.

g = :Group[Row()];
Min( :Cycle[Where( :Group == g & :Retention <= 60 )] );

Re: How do you write a column formula which can group by rows defined by another column?

jthi — Mon, 08 Sep 2025 16:17:59 GMT

Here are few examples using my example table

Column 4:

:C[Col Min(If(:R <= 60, Row(), .), :G)]

If your data is always in order (by Cycle and Retention) and Retention always reaches 60, using :C[] could be unnecessary and you could calculate the row in "reverse" by calculating how many items are above 60, Column 5:

Col Sum(:R > 60, :G) + 1

Edit: You could also combine Col Min and Col Number

m = Col Sum(:R <= 60, :G);
If(m > 0,
	Col Number(:G, :G) - m + 1;
,
	.
);