Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- How to define a Count formula within a Subject

Topic Options

- Start Article
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Nov 21, 2016 7:32 AM
(7648 views)

My data are organized such that an individual subject can have multiple lines of data, and each subject can have multiple dates/visits, and multiple lines of codes within each date/visit. However, the number of lines of data is different for each subject. I'm trying to count up the number of lines of data per subject, per date/visit, and lines of code within each date/visit. So, line 1=1, line 2=2, etc. All the formulas seem to require that I set a start and end number for a count, but that's not possible since the end varies.

Can you tell me which formula I should be using and provide some advice on setting it up? Apparently I'm missing this in my interpretation.

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

You want something like this:

If( Row() == 1, 1,

If( :Subj[Row()] == :Subj[Row() - 1], :Count Formula[Row() - 1] + 1,

1)

)

Note that this assumes the data is sorted by the SUBJ column.

Dan Obermiller

7 REPLIES 7

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Count formula?

It sounds like you can get this through a table summary:

If you go to Tables-->Summary then select the columns you want to group by (e.g. subject, visit date). This will give you the number of rows that satisfy those conditions.

Below gives number of students by age and gender:

dt = Open("$SAMPLE_DATA/Big Class.jmp")<< Summary(Group( :age, :sex ));

Is that what you mean?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Count formula?

I don't need to the actual counts, but a new variable that I can use to sort and group by. For example, in another data set I was able to create a new variable that counted the number of rows and gave each row a number, per subject. That was possible sine every subject had 24 rows of data. In my current data set, some subjects have 1 row while other have 5 or 38 or 10, etc.

so...

Subj visit code Counts_I_Want

001 1 7 1 1

001 1 5 2 2

001 2 3 3 1

001 3 7 4 1

001 3 6 5 2

002 1 4 1 1

003 1 7 1 1

003 2 5 2 1

003 2 0 3 2

so...

Subj visit code Counts_I_Want

001 1 7 1 1

001 1 5 2 2

001 2 3 3 1

001 3 7 4 1

001 3 6 5 2

002 1 4 1 1

003 1 7 1 1

003 2 5 2 1

003 2 0 3 2

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

You want something like this:

If( Row() == 1, 1,

If( :Subj[Row()] == :Subj[Row() - 1], :Count Formula[Row() - 1] + 1,

1)

)

Note that this assumes the data is sorted by the SUBJ column.

Dan Obermiller

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Count formula?

I see, then Dan's code should work. You can also use the lag function, so for you example data Subject Counts could be:

If(Lag(:Subj, 1) != :Subj | Is Missing(Lag(:Subj, 1)), 1, Lag(:Subj Counts, 1) + 1)

and visit counts could be:

If(Lag(:Subj, 1) != :Subj | Is Missing(Lag(:Subj, 1)) | Lag(:visit, 1) != :visit, 1, Lag(:Visit Counts, 1) + 1)

This still relies on sorting by subj and visit though.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Count formula?

The formula you want to use is:

~~col number(:Subj,:Visit)~~

col number(:Subj,:Subj,:Visit);

Each row's value for Counts_I_Want will have the count of the number of rows for that subject for the visit

Jim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Count formula?

Col Number( :age, :age )

Col Number is a formula function in the Statistical group of function

It counts the number of things in a column

col number(:age) would ruturn the number of rows

col number(:age,:age) returns the number of times each Age occurs in the column for each row

if "14" shows up 10 times in the column, then for each row with a 14 in it, the formula returns "10."

JMP Systems Engineer, Pharm and BioPharm Sciences

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Count formula?