How Do I create a Column formula for N Categories?

[#1]

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.

[#2 · Accepted solution]

Here is the formula that I would use:

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

[#3 · Accepted solution]

Thanks @txnelson  you're the man!  

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

[#4]

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

[#5]

The function is

Col Number(<:column Name>)

See the Scripting Index for details

[#6]

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)

[#7]

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!

[#8]

I would not do what you are asking for in a formula.  Rather, I would:

1. Click on  Tables=>Summary  and create a summary data table, grouping by Year, WW and ToolID
2. Using the newly created summary data table, once again, click on   Tables=>Summary  and create a new summary data table by grouping on just Year and WW.  The new table's column called # Rows will give you the number of unique toolIDs per year/WW.
3. Next, click on   the original data table, and then using the Tables pull down menu once again, select  Update.  This will copy the data from the second summary table into the original table.  In the dialog box for the Update, 
   1. Select the table to update from(the second summary table)
   2. Select to Match Columns
   3. Select Year from each table and click on Match
   4. Select WW from each table and click on Match
   5. Click on OK

The data will now be in your original data table

[#9]

@CTrahon0526, Jim's solution is beautiful! And it teaches you a lot about Table operations in JMP.  I agree that a Column formula in this case is very tricky.  Tables Summary 2X is a very elegant solution.  Then "updating back" the results to the original data table using Tables > Update.  That last step can kind be done interactively (by literally dragging and dropping the column from the second summary table back to the first, and finally to the main table) instead of using Tables > Update, but the solution using Update is more robust (reproducible) if you are going to be doing this calculation on many tables.  But this kind of gives you a sense of how interactive JMP is as GUI-based tool.

[#10]

The drag & drop is very cool.

Nevertheless, there is a HUGE disadvantage o this approach:
it's not dynamic. If data is added to the table, the entries will be wrong.

This is THE benefit of a column formula: The values will be always correct.

Astonishing that there is no New Column Formula / JSL command Col N Categories ( ) available in JMP.