cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Discussions

Solve problems, and share tips and tricks with other JMP users.
Choose Language Hide Translation Bar
hogi
Level XIII

score Unique & count unique

Add dense ranking to Ranking Tie and Col Rank functions 

Col N Categories (💚) 

 

new in JMP19 : New in Graph Builder for JMP 19 

 

very helpful : )

hogi_0-1759813931381.png

 



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;

hogi_0-1759508880772.png

 

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] 

4 REPLIES 4
hogi
Level XIII

Re: score Unique & count unique




@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
hogi
Level XIII

Re: score Unique

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.

XanGregg
Staff

Re: score Unique & count unique

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.

 

hogi
Level XIII

Re: score Unique & count unique

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

 

hogi_0-1759921000864.png

 

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.


 


 

Recommended Articles