cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
floydfoxfb
Level II

How to calculate percentage differences between rows referenced to a cell in another column

Hello all,

 

I have the following table.  For each subject, I would like to calculate the % difference of the d1, d2, d3 and d4 values in the Value column relative to the screen value.  The formula is ((d1-screen)/screen)*100, repeating for the d2 through d4 values for subject 1.  Then when the subject number changes, the screen value for subject 2 is referenced for the calculation using subject 2's d1 through d4 values, etc.  I am sure there is a way to do this but I have not been successful in identifying one. 

 

Thank you in advance

Floyd Fox

 

 

11313_pastedImage_0.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: How to script % differences between rows referenced to a cell in another column

Here is a simple script that does what you want:

Names Default To Here( 1 );

dt = Current Data Table();

// Create a data table with just screen values

dt << select where( :day == "screen" );

dtscreen = dt << subset( selected rows(1 ), selected columns(0 ) );

dtscreen << delete columns( "day" );

dtscreen:Value << set name( "Screen Value" );

// Combine the data back together

dt << Update( With( dtscreen ), Match Columns(:subject = :subject ) );

Close( dtscreen, nosave );

// Make the final calculation

dt << New Column( "% dif from screen", formula((:Value - :screen value) / :Screen Value ), Format( "Percent", 7, 2 ));

Jim

View solution in original post

ms
Super User (Alumni) ms
Super User (Alumni)

Re: How to script % differences between rows referenced to a cell in another column

Here's two more aways to do it. Both use the function Col Min() for finding the first row for each subject, and the thus depends on current sorting (Jim's solution has the advantage of being independent of sorting).

dt = Current Data Table();

// Set static values

col1 = dt << New Column("Screen Value", numeric);

col2 = dt << New Column("% dif from screen", numeric, Format(percent, 1));

For Each Row(

    col1[] = :Value[Col Min(Row(), :Subject)];

    col2[] = (:Value - col1[]) / col1[];

);

// Column formula, all in one step.

dt << New Column("% dif from screen",

    Format(percent, 1),

    formula((:Value - :Value[Col Min(Row(), :Subject)]) / :Value[Col Min(Row(), :Subject)])

);


View solution in original post

6 REPLIES 6
txnelson
Super User

Re: How to script % differences between rows referenced to a cell in another column

Here is a simple script that does what you want:

Names Default To Here( 1 );

dt = Current Data Table();

// Create a data table with just screen values

dt << select where( :day == "screen" );

dtscreen = dt << subset( selected rows(1 ), selected columns(0 ) );

dtscreen << delete columns( "day" );

dtscreen:Value << set name( "Screen Value" );

// Combine the data back together

dt << Update( With( dtscreen ), Match Columns(:subject = :subject ) );

Close( dtscreen, nosave );

// Make the final calculation

dt << New Column( "% dif from screen", formula((:Value - :screen value) / :Screen Value ), Format( "Percent", 7, 2 ));

Jim
floydfoxfb
Level II

Re: How to script % differences between rows referenced to a cell in another column

Jim,

Thank you very much this is very usefull.  If I have two (or more) value columns representing different sets of observation on the screen d1, d2 etc, what changes would I have to make in order to calculate the difference for each value column.  Thank you

Floyd

txnelson
Super User

Re: How to script % differences between rows referenced to a cell in another column

If I am interpreting your expansion request correctly, your data table would now look like this?

11318_pastedImage_0.png

Using the method that I originally proposed, the script would have a couple of minor changes:

Names Default To Here( 1 );

dt = Current Data Table();

// Create a data table with just screen values

dt << select where( :day == "screen" );

dtscreen = dt << subset( selected rows(1 ), selected columns(0 ) );

dtscreen << delete columns( "day" );

dtscreen:Value << set name( "Screen Value" );

dtscreen:Value 2 << set name( "Screen Value 2" );

// Combine the data back together

dt << Update( With( dtscreen ), Match Columns(:subject = :subject ) );

Close( dtscreen, nosave );

// Make the final calculation

dt << New Column( "% dif from screen", formula((:Value - :screen value) / :Screen Value ), Format( "Percent", 7, 2 ));

dt << New Column( "% dif from screen 2", formula((:Value 2 - :screen value 2) / :Screen Value 2 ), Format( "Percent", 7, 2 ));

This would give you a final table looking like:

11319_pastedImage_1.png

There is an advantage in using the methodology that MS proposed, in that it can be accomplished just by specifying a formula for the new column.  However, it does rely on determining the Screen row position , which may not be a valid assumption when you are using this with real data.

Jim
ms
Super User (Alumni) ms
Super User (Alumni)

Re: How to script % differences between rows referenced to a cell in another column

Yes, Screen row position may be fiddled with. But if Screen is bound to always have the maximum value within a subject, this formula works too:

:Value / Col Max(:Value, :Subject) - 1



ms
Super User (Alumni) ms
Super User (Alumni)

Re: How to script % differences between rows referenced to a cell in another column

Here's two more aways to do it. Both use the function Col Min() for finding the first row for each subject, and the thus depends on current sorting (Jim's solution has the advantage of being independent of sorting).

dt = Current Data Table();

// Set static values

col1 = dt << New Column("Screen Value", numeric);

col2 = dt << New Column("% dif from screen", numeric, Format(percent, 1));

For Each Row(

    col1[] = :Value[Col Min(Row(), :Subject)];

    col2[] = (:Value - col1[]) / col1[];

);

// Column formula, all in one step.

dt << New Column("% dif from screen",

    Format(percent, 1),

    formula((:Value - :Value[Col Min(Row(), :Subject)]) / :Value[Col Min(Row(), :Subject)])

);


floydfoxfb
Level II

Re: How to script % differences between rows referenced to a cell in another column

MS,

Thank you very much.  I have asked Jim a question and I would pose the same to you.

  If I have two (or more) value columns representing different sets of observation on the screen d1, d2 etc, what changes would I have to make in order to calculate the difference for each value column.  Thank you

Floyd