Choose Language Hide Translation Bar
Highlighted

Community Trekker

Joined:

Aug 27, 2014

## reference to a baseline value

We perform tests that measure performance of a device over time. The output is a value relative to the starting value. Right now to get to such relative performance, I setup a jmp sheet in such a way that I know what the reference is: Example:
```Row     Device           Time               Performance
1             T1              0                     97
2             T2              0                     93
3             T3              0                     95
4             T1             100                    83
5             T2             100                    82
6             T3             100                    83```
For the relative performance, I setup a formula script
```perf_0 = match (Device, "T1", Performance[1], "T2", Performance[2], "T3", Performance[3]);
Performance / perf_0```
This works well with a limited number of data points, but is prone to typing errors for large data sets. Is there a better way to have this automated and less prone to errors? Note that the Performance value at time 0, is not necessarily the maximum or minimum value.
1 ACCEPTED SOLUTION

Accepted Solutions

Super User

Joined:

Jun 23, 2011

Solution

## Re: reference to a baseline value

If the table is sorted after ascending time, this formula should work

``:Performance / :Performance[Col Min(Row(), :Device)]``
5 REPLIES 5

Community Trekker

Joined:

Jun 5, 2014

## Re: reference to a baseline value

Hi, robhanssen!

As you've intuited, there are probably better ways to do what you intend.

I would recommend a script with an Associative Array.  Please reference the Scripting Guide, and the JMP mockup of your dataset and an example of a script enclosed.

Good luck!

Super User

Joined:

Jun 23, 2011

Solution

## Re: reference to a baseline value

If the table is sorted after ascending time, this formula should work

``:Performance / :Performance[Col Min(Row(), :Device)]``

Community Trekker

Joined:

Sep 30, 2016

## Re: reference to a baseline value

Some ideas:

1. Make a new column indicating that the row is a baseline value, then search for it just as @ms suggested.
2. Use a separate data table and a linked column to find the baseline.  I have not found a reliably way to script this yet but if/once that is possible it you could cross out #3.
3. Use a separate data table and look the value up in that table (similar to vlookup).  A sample is attached, maybe someone knows of an easier formula though.
4. Use a table variable. In the same sample script the device value pairs are on individual lines and parsed with regex.

New Contributor

Joined:

Aug 18, 2018

## Re: reference to a baseline value

Hi all, I have the same question. However, I need a more reliable solution, I mean, not relying on row number, as my sheet can be mixed up.

So, is there a way to get the value from :performance of the row having the lowest value in :Time for each device, or even better: the value corresponding at Time=0 for each Device...

Maybe I'm not that clear...

Thank you

Nico

Super User

Joined:

Jun 22, 2012

## Re: reference to a baseline value

Here is a formula that will work.  I have also attached a data table with it applied.

``````dt = Current Data Table();
currentDevice = :Device;
:Performance / :Performance[(dt << get rows where( :Device == currentDevice & :Time == 0 ))[1]];``````

Jim