Col score & Col N unique

[#1]

Add dense ranking to Ranking Tie and Col Rank functions 

Col N Categories (:green_heart:) 

new in JMP19 : New in Graph Builder for JMP 19 

very helpful : )

Details you should be aware of:

1) excluded rows are not included in the calculation
[ very useful, along the idea of Formula Editor: meaningful results with excluded rows ,

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] 

[#2]

---

---

---

@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:

1. 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
   
   
2. save the code and run it again.
   
   
3. create a new transform column and drag it onto the Y axis
   edit: #3 #works for this case - but not for others [see below

Some explanation:

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

[#3]

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.

[#4]

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.

[#5]

@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.

---