formula help!

Question

I am using  JMP® Pro 14.2.0

I am not very experienced at writing JMP formulas. What I am trying to do is compare average medication levels for patients who have had a biopsy. The biopsies happen at irregular time intervals, therefore, most of the cells in the Bx Date column are blank (no biopsy happened at that date, but they were tested for their medication level). If I could write a formula to label the cells within 3 months of a given Bx Date I can then using that column to filter out only the data that is within 3 months of a biopsy date and start doing some statistics. Ideally the formula would also first make sure the drug levels are coming from the same patient (Tx# refer to individual patients).

thanks so much for your help

sample.jmp

cjw0 · Answer

This email and/or any documents in this transmission is intended for the addressee(s) only and may contain legally privileged or confidential information. Any unauthorized use, disclosure, distribution, copying or dissemination is strictly prohibited. If you receive this transmission in error, please notify the sender immediately and return the original.

Ce courriel et tout document dans cette transmission est destiné à la personne ou aux personnes à qui il est adressé. Il peut contenir des informations privilégiées ou confidentielles. Toute utilisation, divulgation, distribution, copie, ou diffusion non autorisée est strictement défendue. Si vous n'êtes pas le destinataire de ce message, veuillez en informer l'expéditeur immédiatement et lui remettre l'original.

jerry_cooper · Answer

Hi @cjw0 ,

Given your restated criteria, I think this formula becomes much more straight forward if you sort the table by ascending Tx# and descending Lab Date:

Then create a new formula column with the following:

If(
	Row() == 1 | :Lab Date == :Bx Date | :Tx# != Lag( :Tx# ), :Bx Date,
	Is Missing( :Bx Date ) & Lag( :Column 6 ) - :Lab Date <= In Days( 90 ), Lag( :Column 6 )
)

Hope this helps.

sorted sample.jmp

txnelson · Answer

The key to the formula, is the selection of which Biopsy dates to look at. If you don't want to examine any Biopsy date after the current Lab Date, then all one has to do, is to change the selection criteria dt = Current Data Table(); x = .; Tx = :Tx#; theRows = dt << get rows where( Tx == :Tx# & :Bx Date >= :Lab Date - In Days( 90 ) & :Bx Date <= :Lab Date ); If( N Rows( theRows ) > 0, If( N Rows( theRows ) == 1, x = :Bx Date[theRows[1]], x = :Bx Date[theRows[1]]; For( i = 2, i <= N Rows( theRows ), i++, If( Abs( :Bx Date[theRows[i]] - :Lab Date ) < Abs( x - :Lab Date ), x = :Bx Date[theRows[i]] ) ); ) ); x;