Help with formula: Calculating change relative to an initial timepoints for multiple samples

[#1]

Hello All,

I am somewhat new to making conditional formulas in JMP and am having some difficulty with this one.  

I will be collecting data at specific timepoints for multiple different samples.  Each sample has a corresponding value for the initial (T=0) timepoint and I would like to have a column for the %difference at a given timepoint relative to T=0.  The table will include multiple replicates of each sample (arranged in rows) and a dozen or so metrics (in columns).  Here's the general idea of the table format:

I've tried to set the math to Match based on the Sample Designation column but am having difficulty with the conditional syntax for pointing a given row to its respective T=0 datum.  I found the following searching for similar issues but was unable to modify the script to match my layout:

https://community.jmp.com/t5/Discussions/Divide-column-by-reference-point-with-conditions/m-p/432899...

Granted: I am less-than-a-novice with scripts...

I've included the jmp table as well.

Thanks in advance for your help. 

Chris

[#2]

Note in Review!

The image in the thread doesn't accurately represent the replicate measurements...sorry.  The jmp table is correct.

This image is correct also:

Chris

[#3]

Here is the formula that I used.  

currentSample = :Sample Designation;
timePointZero = :Data[(Current Data Table() << get rows where( :"Time Point (h)"n == 0 & :Sample Designation == currentSample ))[1]];
Abs( (timePointZero - :Data) / timePointZero );

[#4]

Jim,

Thanks for the response.  I've tried the above and get the following error message:

Note: I had pasted in your script to a "New Script" and run it.  I also tried using the script in the formula editor...in that case there was an error stating that the Current Data Table function has side effects or is inappropriate to use in a formula.  

I might be missing something simple...any thoughts?

Thanks!

Chris

[#5]

I reapplied the formula I provided to the example data table that you provided and found not error.  I have attached the formula to your sample table and attached it to this response.  Please verify that it runs correctly in your system.

[#6]

Hi Jim,

When I open the data table I see the correct values in the last column.  However, there is no formula in the column info (and the column isn't indicated as a formula column).

Thanks,

Chris

[#7]

My error.....here is a corrected table with formula.

[#8 · Accepted solution]

I like using Col-functions to get the needed row number. In this case it seems like the Time Point = 0 is always before the later time points, so simple Col Min should be enough

:Data/:Data[Col Min(Row(), :Sample Designation)] - 1;

This part

Col Min(Row(), :Sample Designation)

is used to get the smallest row number of each Sample Designation and because data is sorted it will get Time Point = 0.

[#9]

Hi Jarmo,

This works perfectly...thanks!  I am not perfectly clear what is happening with this script.  Can you please re-explain?

Thanks again!

Chris