topic Re: Help with formula: Calculating change relative to an initial timepoints for multiple samples in Discussions

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

ClusterFerret68 — Sun, 11 Jun 2023 00:02:05 GMT

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#M68261

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

I've included the jmp table as well.

Thanks in advance for your help.

Chris

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

ClusterFerret68 — Thu, 08 Jun 2023 02:00:08 GMT

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

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

txnelson — Thu, 08 Jun 2023 04:39:17 GMT

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

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

jthi — Thu, 08 Jun 2023 10:35:04 GMT

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.

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

ClusterFerret68 — Thu, 08 Jun 2023 13:21:20 GMT

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

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

ClusterFerret68 — Thu, 08 Jun 2023 13:27:00 GMT

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

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

txnelson — Thu, 08 Jun 2023 16:59:14 GMT

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.

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

ClusterFerret68 — Thu, 08 Jun 2023 16:48:53 GMT

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

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

txnelson — Thu, 08 Jun 2023 20:50:39 GMT

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