nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Column Formula, Relative Value Calculations

FaxMachine

I have been trying to script a new column formula for a data table, where the calculated value changes based on values of columns to the left. Example of what I mean using the "Big Class" example file. Let's say there are 200 rows as opposed to 20, each name appearing 10 times in succession as though the heights and wieghts were recoreded once a year for ten years, additionally there would be another column in the data table with the years the data was collected. All the names would have the same years (i.e. 2001, 2002, 2003... 2010). The new colummn I would like to script a formula for would be the height of that year minus year 2003 let's say. Maybe that year they started taking their vitamines and you want to show how that affected their height growth. The new column would then be populated with values such as -.5, 0, .25, .7... all of them relative values to the specified height at year 2003 on a name-by-name basis.

Since there would be 20 year 2003, what kind of if/while/summarize/by/conditional formula would be used so each Name's heights are compared to that of the same person and not one at the other end of the data table?

Thanks in advance!

Accepted answers

txnelson

Try something like this:

dt = Current Data Table();
tname = :name;
tyear = Col Min( :Year, :name );
trow = (dt << get rows where( tname == :name & tyear == :Year ))[1];
-1 * :weight[trow] + :weight[Row()];

All comments

txnelson

Try something like this:

dt = Current Data Table();
tname = :name;
tyear = Col Min( :Year, :name );
trow = (dt << get rows where( tname == :name & tyear == :Year ))[1];
-1 * :weight[trow] + :weight[Row()];

FaxMachine

For the thirds line with Col Min, those tables would technically be characters, however according to this help page http://www.jmp.com/support/help/Statistical_Functions_2.shtml min can have either all numeric or all strings so that shouldn't be a probelm, correct?

Second, what if I dont want the data to be compared to the minimum value, but rather one I choose. Would setting up a :Year == " x y z" suffice in this scenario?

txnelson

Correct on both assumptions......but rather than asking, I suggest that you start trying such ideas on your own. One really doesn't learn by asking, but by trying....failing and then succeeding

FaxMachine

If it helps explain it better, let's redraw the example. With the Big Class sample, I want a new column that it will compare all the female F heights individually to KATIE's height, and all the male M heights to JOHN's height. This new column would be called "height difference", and each row would have that person's height minus KATIE or JOHN's height depending on M/F. So rows 2, 3, 4 with LOUISE, JANE, and JACLYN will have "height difference" values of 2, -4, and 5 respectivly. (Negative number are acceptable). However, when it comes to TIM, his height will be compared to JOHN's 65 not KATIE's 59.

Taking it one step further, what if I wanted this done for every name on the list besides the ones who's age = 14. All the rows where age != 14 will still have their respective "height difference" compared to KATIE and JOHN. For those of age 14 I would like the cell to be blank.

Hopefully this clears things up, and gives an example to work on as opposed to just taking about it. If it makes it more confusing then completely ignore it

FaxMachine

Indeed. I have been failing and succeeding with this the past couple days making progress slowly but surely. This hiccup is just a part of a script that I've been stuck on.

txnelson

All you are doing, is adding conditional items to your formula. You will need to apply IF statements to the formula to cover the conditions that you have specified. There is nothing difficult to this. The method I showed you still fits, you just need to add the other conditions.