63464_en-US

liliconcom

Best way to track initial value for a time trend

All,

 

Need an advice on how to best organize this.

 

So, I have a table that tracks a parameter for few categories over time. I need to have a column that shows percent change from some initial value.

Let's say it's a weight competition tracker. We got initial weight and we track percent change. The one that gets more than 15% in 30 days - wins.

To track that I created a table like this:

In this table I put initial weights as table variables, and created a hidden column that just displays initial weight based on the name:

 

 

After that all I need to do is to click the Dashboard script and it displays all the graphs.

 

 

 

My goal is to make it as user friendly as possible, so that pretty much anybody could enter the data without too much thinking. So I have few questions for the community:

Now, let's say we have a new person joining the game, and we need to enter a new name.

1. To do that you need to create a new table variable with the proper name.
2. Then edit the formula in the hidden column "InitialWeight" ot add that name
3. Then add this name as an allowed option for the "Name" column.

All that is not very user friendly. We enter essentially the same data three times. Is there a way to do that only once? Let's say we entered a new variable.

• Is there a way to say in formula - "return the value of variable with the same name as value of "Name" column"?
• Is there a way to say - "my allowed values are the names of my table variables"?

 

Or maybe there is another way of setting new name and initial way and pull that info into formulas organically? All I can think of is writing a full blown app for managing that, but maybe there is a handy way of doing that out of box?

 

Thanks,

M.

 

 

 

 

Re: Best way to track initial value for a time trend

I agree, full-blown script would be the best. However, this excersize was aimed at something that you can set up in 10-15 minutes with minimal functionality. Or, using functionality that already exists.

 

Thanks for the suggestions! I think it is a more elegant way. However, not everything is immediately clear in the code, so will have to sit on it for a while.

 

Thanks again!

Re: Best way to track initial value for a time trend

Miguello,

 

My interpretation of what you described is that you have this table that a community of users can access. I would probably do something different like an add-in.  But using your table interface, I would:

1. Remove the name variables and remove the hidden column
2. Add a script called New Member Registry, where the user is prompted for a name and weight. The information is used to update an Associative Array called registy = Associative Array({"Marvin", 280}, {"Smith", 205}, {"Jones",201},   etc.);  Once defined,  registy["Marvin"] returns the value 250.
3. Add a script called Initialize or Update, this is the script that runs the registry definition, so the values are in memory.
4. Change the formula to be t0 = registry << get value(:Name); (:Weight - t0) / t0 )*100

The attached file is a mockup. You did not mention what version of JMP you are using?  This works for JMP14. If you have an earlier version of JMP registry will need to be a function instead of an associative array.

 

I think using an add-in script would be more robust than scripts and constants embedded  in a table that users can change.

Re: Best way to track initial value for a time trend

---

 

The only problem is that in order for it to go and read the updated variables you need to go to formula edit screen and at least click Apply button. Any way around this?

 

---

I take that back, it does update the formula. Not immediately, but as soon as you hit enter and add another row.

Re: Best way to track initial value for a time trend

Ok, the first step would be to replace the Match() expression in Formula for column "InitialWeight" with the following code:

 

Names = Current Data Table() << Get Table Variable Names();
formula_pre = "Match( :Name";
formula = formula_pre;
formula_post = ")";
For( i = 1, i <= N Items( Names ), i++,
 formula = formula || ", \!"" || Names[i] || "\!", :" || Names[i]
);
formula = formula || formula_post;
Eval( Parse( formula ) );

This will take the list of your table variables and wil construct and evaluate a Matach() formula based on the list.

 

The only problem is that in order for it to go and read the updated variables you need to go to formula edit screen and at least click Apply button. Any way around this?

 

Thanks,

M.