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

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

cheese_stats — Wed, 06 May 2020 20:01:09 GMT

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(
[., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., ., .,
., ., ., .]
)
)
)

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

txnelson — Wed, 06 May 2020 21:37:54 GMT

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 ) );

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

Jeff_Perkinson — Wed, 06 May 2020 21:27:57 GMT

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 )

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

txnelson — Wed, 06 May 2020 21:36:30 GMT

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

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

cheese_stats — Wed, 06 May 2020 21:44:34 GMT

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