BookmarkSubscribe
Choose Language Hide Translation Bar
cjw0
Community Trekker

formula help!

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

0 Kudos
6 REPLIES 6
txnelson
Super User

Re: formula help!

Here is one way to do it.  It first finds all of the Biopsy dates within the range of the current Lab Date, and then finds the biopsy closest to the current Lab Date from the found Biopsys

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 + In Days( 90 ) );
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;
Jim
cjw0
Community Trekker

Re: formula help!

Thanks for your fast reply. If I apply your formula I get the following…. (See screenshot). Unfortunately the dates in the new “column 6” go past the Bx Date…..thus is not ideal since my question is how do the medication level leading up to the biopsy effect the biopsy result? Is there a way to tweak this so that at the time of the biopsy the date in column 6 will change to the date of the next biopsy from the Bx Date column?

Thanks, sorry to bother you with this.






0 Kudos
txnelson
Super User

Re: formula help!

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;
Jim
0 Kudos
jerry_cooper
Staff (Retired)

Re: formula help!

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:

Capture.PNG

 

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.

cjw0
Community Trekker

Re: formula help!

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.
0 Kudos
Highlighted
Ryan_Gilmore
Community Manager Community Manager

Re: formula help!

Here is a slightly different approach than @txnelson . Rather than populate the column with date values, the formula indicates if the lab date is within 3 months of the biopsy date. It uses this approach since some of the lab dates fell within 3 months of more than one biopsy date.

 

dt = Current Data Table();
currentPatientId = :Tx#;
latestBxDates = :Bx Date[dt << get rows where(
	!Is Missing( :Bx Date ) & currentPatientId == :Tx#
)];
nDays = In Days( 90 );
Any( :Lab Date - nDays < latestBxDates < :Lab Date + nDays );
0 Kudos