topic How to define a Count formula within a Subject in Discussions

How to define a Count formula within a Subject

jswislar — Tue, 03 Oct 2017 14:55:07 GMT

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

Re: Count formula?

mpl34 — Mon, 21 Nov 2016 16:48:53 GMT

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?

Re: Count formula?

jswislar — Mon, 21 Nov 2016 16:59:31 GMT

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

Re: Count formula?

Dan_Obermiller — Mon, 21 Nov 2016 17:15:13 GMT

You want something like this:

If( Row() == 1, 1,

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

)

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

Re: Count formula?

ron_horne — Mon, 21 Nov 2016 17:36:37 GMT

try this one:

index

Re: Count formula?

Byron_JMP — Mon, 21 Nov 2016 17:49:08 GMT

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."

Re: Count formula?

mpl34 — Mon, 21 Nov 2016 18:24:09 GMT

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.

Re: Count formula?

txnelson — Mon, 21 Nov 2016 20:03:27 GMT

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