How do I assign a start date?

[#1]

I have a longitudinal study with sites entering on different dates. To make them comparable, I need to assign each entry date as "1" and the second data collection point as "2" and so on. The data are in long format and there are a lot of missing data (missed data collection)

The data look like this, I want to generate "Count":

ID    Date   Var1  Count

001  Jan-1   .          .

001  Feb-1   .          .

001  Mar-1   10       1

001  Apr-1    13       2

002  Jan-1    8         1

002  Feb-1   10        2

002  Mar-1   .           .

I used a JRP formula, but it counts every line whether the first measure is missing or not.

[#2]

Here is one version of the formula that will work

If( Lag( :ID ) != :ID,
	If( Is Missing( :Var1 ) == 0,
		TheCount = 1,
		TheCount = .
	),
	If( Is Missing( :Var1 ) == 0,
		TheCount = Sum( TheCount, 1 )
	)
);
TheCount;

[#3]

This formula gives me the following error

"Illegal Reference (recursion) 1 times At rows:2"

I follow the formula, but I can't figure out where the error is.

[#4]

attached is a data set with the formula set for column Count

[#5]

Hmm. Same error message. I made up the data I gave you, but it's the same idea, except ID is character in my data. Would that make a difference?

Also, noticed that your formula stopped counting at 2.

Thanks!

[#6]

I appears that you opened the data table I attached.  And from what you stated, you can see the values for column Count.  If you are seeing those values, then the formula is working.  The reason the count only goes to 2 is that the counting resets when the ID column changes, the counter restarts, just like in the example you provided.

Are you changing anything in the formula I am providing?  The recursive issue can be happening if you are attempting to use the name "Count" in the formula for the column named "Count".  That is why I use the variable name "TheCount" for the calculation.  The formula will use the last item calculated to pass to the value to use for the formula

Finally, what version of JMP are you using?

[#7]

Jim,

Thanks for following up.

1. Can we make theCount column stop counting when the value is missing? In you example, it repeats the last count number, will will presummably do so until the ID number changes.

2. I copied and pasted your formula. The only thing I changed in your formula is the name of the variables so that it exactly matches my data set.

3. The word "count" does not appear in any of my variable names. I renamed my "TheCount" variable to "Column 10" and got the same error message. I've also tried renaming my site variable. Same problem.

4. I'm using JMP Pro 13.1

Is Missing (Var1) == 0 Then TheCount=1

Translates as if Var1 is missing then the new variable (TheCount)=1? But what does "==0" do?

This is what I have, including my variable names.

If( Lag( :SiteID ) != :SiteID,

If( Is Missing( :NozeroValue ) == 0,

:Column 10 = 1,

:Column 10 = .

),

If( Is Missing( :NozeroValue ) == 0,

:Column 10 = Sum( :Column 10, 1 )

)

);

:Column 10;

[#8]

1. I have attached a new example data table with the new formula that handles what you are requesting

2. Yes, you need to change the :ID column name to whatever the variable name is that restarts the counter, and  :Var 1 to whatever the measurement column name is.

3.  You should not have changed the name of the Memory Variable called "TheColumn";  This is not a column name, it is just intended to do some calculations in the formula.  In the new formula I changed the variable name to "Zippy" in an attempt to show that it is not related to any column name.  The only connection to the column that the final listing of the variable at the end of the formula.  JMP takes whatever the last thing specified (in this case, the calculated value of Zippy) and passes it to the value to be used for the column the format is being applied to, for the row the formula is currently being worked on.

2. Is Missing() is a function that if the contents of the function ":Var 1" is a missing value, then the function will return a 1.  If the value is not missing, it returns a 0.  the "==" is a comparison oprator of "Equal To".  So "== 0" is asking if the "Is Missing()" function returning a "0", which would say the variable value is a valid value.

[#9]

Great! Thank you for the clarification and the revised fomula! I appreciate it.

The revision works EXCEPT if the missing pattern is mixed. For example, see "Site=001" below, my results look like this with the revised formula (Count) and what I want it to give me.

Site   date  Var1  Count  What_I_Want

001   May   .          .             .

001   Jun   10       1             1

001   Jul      .         .             .

001   Aug   12       .             2

001   Sep   15       .             3

002   May   13      1             1

002   Jun    14      2             2

[#10 · Accepted solution]

I have attached a new version of the formula.  It should work as your latest modification was stated.

Please note, the Discussion Community is not intended as a "Code Factory".  It is there to help users solve problems so they can learn and from that be able to solve their own problems.

JMP provides a wealth of documentation.  Please go to 

     Help==>Books==>Using JMP

and

     Help==>Books==>Scripting Guide