cancel
Showing results for
Show  only  | Search instead for
Did you mean:
Choose Language Hide Translation Bar
Level III

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

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.

Chris

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

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

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.

-Jarmo
8 REPLIES 8
Level III

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

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

Super User

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

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 );``````

Jim
Level III

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

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

Super User

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

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.

Jim
Level III

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

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

Super User

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

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

Jim
Super User

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

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.

-Jarmo
Level III

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

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