Subscribe Bookmark RSS Feed

About formula : "How many (categorical data) by column ?

jameskim

Community Trekker

Joined:

May 17, 2012

Hi there~

Could you do me a favor?

I want to know

"How many (categorical data) by column

without calculation of repeated data?

=========================================================

for example

Open( "$SAMPLE_DATA/Big Class.jmp" );

I want to know how many (:age) types by (:sex)?

I want to know that function by using formula only

2916_Big class.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

Oh, I forgot that Col Number() only works for numeric columns as first argument.

Here two suggestion for a column formula solution (and not by JSL-script ):

Use a numeric column that's known to not have any missing values, e.g.

Col Number( :TRANS_TIME, :DVC )

Or if each DVC data always ends with a unique four digit number (as in the posted table) this would give the same result:

Col Number( Num( Right( :DVC, 4 ) ), :DVC )

4 REPLIES
ms

Super User

Joined:

Jun 23, 2011

Use this Statistical function:

Col Number( :age, :sex )

The second argument is the By-bariable. In formula editor the optional second argument (and third etc) is activated by selecting the first argument and clicking on ^ (insert).

jameskim

Community Trekker

Joined:

May 17, 2012

Dear MS,

I really appreciate with ur idea.

But I still cannot solve this problem.

b'cus my data is a little bit more complicate.

There are repeated same data in the "name" column,

.. therefore...  Col Number( :age, :sex ) is not working..

This is actual sample..

when I use this

Col Number(:LOT_ID, :DVC)


but it is not working,,, the result shows "0" only  like as below.

I think ... it is because of the same data in 'LOT_ID' column

but i still want to calculate it.

2923_sample.jpg

I soIve it, by using other column data "time"  like as below.

Col Sum(If(Col Minimum(:TRANS_TIME, :LOT_ID) == :TRANS_TIME, 1, 0), :DVC)

But I still want to solve it    without the other data.

Could you give me  the other idea?  I need ur help.. MS

Solution

Oh, I forgot that Col Number() only works for numeric columns as first argument.

Here two suggestion for a column formula solution (and not by JSL-script ):

Use a numeric column that's known to not have any missing values, e.g.

Col Number( :TRANS_TIME, :DVC )

Or if each DVC data always ends with a unique four digit number (as in the posted table) this would give the same result:

Col Number( Num( Right( :DVC, 4 ) ), :DVC )

jameskim

Community Trekker

Joined:

May 17, 2012

Oh~ MS~!

I truly appreciate you taking the time to answer my questions!

I hope I have a chance to help you, someday.

Have a good day! thanks.