Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- reference to a baseline value

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Oct 31, 2017 12:26 PM
(8701 views)

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 83For the relative performance, I setup a formula script

perf_0 = match (Device, "T1", Performance[1], "T2", Performance[2], "T3", Performance[3]); Performance / perf_0This 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.

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

6 REPLIES 6

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: reference to a baseline value

Created:
Oct 31, 2017 2:16 PM
| Last Modified: Nov 1, 2017 7:36 AM
(8678 views)
| Posted in reply to message from robhanssen 10-31-2017

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!

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: reference to a baseline value

Some ideas:

- Make a new column indicating that the row is a baseline value, then search for it just as @ms suggested.
- 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.
- 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.
- Use a table variable. In the same sample script the device value pairs are on individual lines and parsed with regex.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: reference to a baseline value

Very nice! Thank's

Just to understand, what is the function of [1] in the formula?

Nico

Just to understand, what is the function of [1] in the formula?

Nico

Article Labels

There are no labels assigned to this post.