topic Col score & Col N unique in Discussions

Col score & Col N unique

hogi — Wed, 03 Dec 2025 22:24:46 GMT

new in JMP19 :

very helpful : )

Details you should be aware of:

1) excluded rows are not included in the calculation
[ very useful, along the idea of ,

nevertheless: somewhat inconsistent, in view of the behavior of other Col... aggregations!]

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

dt << Clear Select << Select Rows( Index( 3, 8 ) ) << Exclude;

dt<< New Formula Column(
	Operation( Category( "Aggregate" ), "Score Unique" ),
	Columns( :name ),
	Group By( :sex )
) << Run Formulas;

1b) unfortunately, the results don't get update when row states are changed:

[TS-00231503]

wait(2);
dt<< Clear Select << Select Rows( Index( 3, 8 ) )  << Exclude;

[to trigger an update, change a cell in the input column and change it back]

2) Just work with "columns", don't work with expressions.
this column has missing entries:

New Column( "Score Unique[1st character of name][sex]",
	Formula( Col Score( Substr( :name, 1, 1 ), :sex ) ))

[This appears to be somewhat inconsistent, in view of the behavior of other Col... aggregations]

[TS- 00215787]

Re: score Unique & count unique

hogi — Wed, 08 Oct 2025 11:09:32 GMT

@hogi wrote:
1b) unfortunately, the results don't get update when row states are changed:

[TS-00231503]

same behavior in Graph Builder - in combination with Scoping:
When row states are changed after creating the transform column, the values in the plot don't get updated.

I guess this behavior will be fixed in a future version of JMP.
For now there are multiple workarounds to correct the plot:

change one of the many inputs which trigger an update of the scoped column formula:
change data filter, overlay, wrap, X axis ... and change back
save the code and run it again.
create a new transform column and drag it onto the Y axis
edit: #3 #works for this case - but not for others [see below

(view in My Videos)

Some explanation:

Bar graph with default setting:
counts - by age and student [use it as a reference]
use Count Unique[name] on the Y axis:
count of [all] unique names: 39 (2x Robert)
The magic:
scope = "X"
-> count of unique names - split by "X" [values on the first x axis = age]
age=14: 12 students (like in 1.)
remove some students from the analysis (via exclude & hide)
-> doesn't change the counting in the plot
this is very unexpected!
remove the transform column from the Y axis
-> switch back to #1: standard counting
proves: there are just 4 students with age=14

Re: score Unique

hogi — Mon, 06 Oct 2025 17:42:15 GMT

I just wanted to create a video with Col Number() - instead of Col N Unique() - to show the difference.
Unfortunately, Col Number behaves the same :
according to the setting it should respond to the row states, the local data filter ....
Indeed, it reacts on changes of the local data filter [not shown] - but it doesn't react on changes of the row states.

(view in My Videos)

Re: score Unique & count unique

XanGregg — Tue, 07 Oct 2025 13:11:56 GMT

I guess this behavior will be fixed in a future version of JMP.

Yes, it's already a part of the upcoming 19.1. Col Score and Col Unique behave like other Col functions with respect to exclude.

Re: score Unique & count unique

hogi — Wed, 08 Oct 2025 11:08:07 GMT

@hogi wrote:
For now there are multiple workarounds to correct the plot:
- create a new transform column and drag it onto the Y axis

Another level of confusion:
this workaround just works for the new col aggregations - like Col N unique() [red]

For other col aggregations like Col Median(), Col Number() ... it doesn't help to create a new transform column [blue].
They seem to recycle the row state settings which were defined when the first Col aggregation was calculated - or even the results of the first calculation.

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
gb =Graph Builder(
	Transform Column( "count Unique1", Formula( Col N unique( :weight, :"@Exclude"n ) ) ),
	Transform Column( "Count1", Formula( Col Number( :height, :"@Exclude"n ) ) ),
	Variables( Y( :Count1 ), Y( :count Unique1, Position( 1 ) ) ),
	Elements( Bar( Y( 1 ), Y( 2 ) ) )
);
//gb =Graph Builder();



dt << Select Rows( Index( 3, 9 ) ) << Exclude << Clear select;
wait(2);

gb << 	Transform Column(
		"Count2",
		Formula( Col Number( :weight, :"@Exclude"n ) )
	);
gb << 	Transform Column(
		"count Unique2",
		Formula( Col N unique( :weight, :"@Exclude"n ) )
	);
	
gb << Add Variable( {:Count2, Role( "Y" ),Position(1)} );
gb << Add Variable( {:count Unique 2, Role( "Y" ),Position(1)} );

the other 2 workarounds are OK:

@hogi wrote:

- change one of the many inputs which trigger an update of the scoped column formula:
change data filter, overlay, wrap, X axis ... and change back

- save the code and run it again.