Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
cheese_stats
Level II

Formula Column for "N" Instances of Column 1 per value of Column 2

I apologize that this type of question has probably already been asked in the Discussions, but I cannot find a solution that works for the application I need. The search terms of "Sum" "Count" etc. are too generic and make it difficult to find a solution precisely for what I am trying to do.

 

I want to create a column with a formula of how many instances of "Column 1" occur per a given value of "Column 2"

 

Tabulate function can do this very easily by simply stating "N" for a given "Week" column, but I need it to be a Column Formula for the application.

 

I have created an example table with Apples, Oranges, and Bananas to demonstrate. I need a formula for the third column "Instances of this Fruit this Week" to list how many instances there are of "Fruit of the Day" for a given "Week". For example, the "Instances of Fruit per Week" column would list [5,2,5,5,5,5,2...] Because Apple occurs 5 times in Week=1, and Banana occurs 2 times in Week=1. 

 

Thanks for the continued support with this great software, JMP community.

 

 

 

New Table( "Fruit Table",
Add Rows( 28 ),
New Column( "Fruit of the Day",
Character( 8 ),
"Nominal",
Set Values(
{"Apple", "Banana", "Apple", "Apple", "Apple", "Apple", "Banana",
"Orange", "Apple", "Banana", "Apple", "Banana", "Apple", "Apple",
"Apple", "Banana", "Apple", "Apple", "Apple", "Apple", "Banana", "Apple",
"Banana", "Apple", "Apple", "Apple", "Apple", "Banana"}
)
),
New Column( "Week",
Numeric,
"Nominal",
Format( "Best", 12 ),
Set Values(
[1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 4, 4, 4,
4, 4, 4, 4]
)
),
New Column( "Instances of this Fruit this Week",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values(
[., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., .,
., ., ., .]
)
)
)

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Jeff_Perkinson
Community Manager Community Manager

Re: Formula Column for "N" Instances of Column 1 per value of Column 2

Here's a formula that will work. The Col Number() function counts the number of non-missing rows.

 

Col Number( :Week, :Fruit of the Day, :Week )

2020-05-06_17-23-20.487.png

-Jeff

View solution in original post

4 REPLIES 4
Highlighted
txnelson
Super User

Re: Formula Column for "N" Instances of Column 1 per value of Column 2

This formula will work, but see @Jeff_Perkinson response below for the better answer

curFruit = :Fruit of the Day;
N Rows( Current Data Table() << get rows where( :Fruit of the Day == curFruit ) );

 

Jim
Highlighted
Jeff_Perkinson
Community Manager Community Manager

Re: Formula Column for "N" Instances of Column 1 per value of Column 2

Here's a formula that will work. The Col Number() function counts the number of non-missing rows.

 

Col Number( :Week, :Fruit of the Day, :Week )

2020-05-06_17-23-20.487.png

-Jeff

View solution in original post

Highlighted
txnelson
Super User

Re: Formula Column for "N" Instances of Column 1 per value of Column 2

OK......so everybody use Jeff's answer......it is the correct one...…..I don't know what I was thinking with my response:-)
Jim
Highlighted
cheese_stats
Level II

Re: Formula Column for "N" Instances of Column 1 per value of Column 2

Yes, this works perfectly. I have created a script for the new column using the formula:

Col Number()

Thanks very much, everyone @Jeff_Perkinson @txnelson 

Article Labels

    There are no labels assigned to this post.