- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How Do I create a Column formula for N Categories?
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How Do I create a Column formula for N Categories?
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How Do I create a Column formula for N Categories?
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How Do I create a Column formula for N Categories?
The function is
Col Number(<:column Name>)
See the Scripting Index for details
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How Do I create a Column formula for N Categories?
I would not do what you are asking for in a formula. Rather, I would:
- Click on Tables=>Summary and create a summary data table, grouping by Year, WW and ToolID
- 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.
- 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,
- Select the table to update from(the second summary table)
- Select to Match Columns
- Select Year from each table and click on Match
- Select WW from each table and click on Match
- Click on OK
The data will now be in your original data table
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How Do I create a Column formula for N Categories?
@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.
- Chapters
- descriptions off, selected
- captions settings, opens captions settings dialog
- captions off, selected
- en (Main), selected
This is a modal window.
Beginning of dialog window. Escape will cancel and close the window.
End of dialog window.
This is a modal window. This modal can be closed by pressing the Escape key or activating the close button.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How Do I create a Column formula for N Categories?
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.