334847_en-US

liliconcom

How Do I create a Column formula for N Categories?

I would like to create a column formula that provides a count of the number of distinct values in a column.  I can use the Column viewer, Show summary function in JMP 15 but I want to have that value in a column for use in other calculations.

 

For example in the Big Class Table - N Items in column "Sex" should show 2 as 2 distinct values.

Re: How Do I create a Column formula for N Categories?

Thank you, @txnelson & @PatrickGiuliano .

 

Fairly new to JMP, still learning. Good to know about the Col Number() operator.

 

I have attached a sample of my data set. I am looking for the unique number of tools per week per year. Each tool could have multiple rows per week. I put an ideal outcome column in the set.

 

Thanks again for your help!

Re: How Do I create a Column formula for N Categories?

Thank you Jim! 

@CTrahon0526 If you're new to JMP, here's a bit more elaboration on what Jim shared.

You can put the Col Number() function in a column formula and compute it directly in the data table. 

I'm attaching a mock example jmp data table. 

 

First, I created a "Year Week" column by using an Instant Column Formula in JMP. Right Click on the time column, New Formula Column > Date Time > Year Week. 

 

 

Then I created a "Count by Year Week" column with the following column formula in it: 

Col Number(:time, :Year Week)

 

Re: How Do I create a Column formula for N Categories?

The function is

Col Number(<:column Name>)

See the Scripting Index for details

Re: How Do I create a Column formula for N Categories?

 

@PatrickGiuliano ,

 

Is there a way to get the N Items by Variable?

 

I am trying to find the unique N values per Year per Work Week. Thanks.

 

Re: How Do I create a Column formula for N Categories?

Thanks @txnelson  you're the man!  

@patricia_roth  and I also came up with the following ode to the gracious support from some of the other JMP team members.

Summarize( x = by( :sex ) );
N Items( x );

very much the same but a few less lines.

 

Re: How Do I create a Column formula for N Categories?

Here is the formula that I would use:

If( Row() == 1,
 Summarize( Current Data Table(), bygp = by( :sex ) );
 n = N Items( bygp );
);
n;