BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Re: Col (Sum) function by selecting specific rows


Does this solution only work in specific versions of JMP ? 

Because I am using JMP 12 and I keep running into this error 

 

 

dt = Open("$SAMPLE_DATA/Big Class.jmp");
dt << select Where(:age ==12); 
n = Col Sum(:age,Selected()); 

Here is my Log 

 

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << select Where( :age == 12 );
n = Col Sum( :age, Selected/*###*/() );
Row number not set or in range in access or evaluation of 'Selected' , Selected/*###*/()

Best
Uday
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Re: Col (Sum) function by selecting specific rows

@ian_jmp  / @ms / @txnelson  - Any thoughts ? 

Best
Uday
ms

Super User

Joined:

Jun 23, 2011

Re: Col (Sum) function by selecting specific rows

The Col Stat() functions are mainly useful in column formulas, and with optional grouping arguments they do not work at all as stand-alone functions (then use Summarize() instead).

 

My old post was intended to show how to update a column dynamically just by selecting rows by hand (or by script). More of a neat trick than anything very useful...

 

Example:

 

dt = Open("$SAMPLE_DATA/Big Class.jmp");
dt << New Column("Conditional sum", Formula(Col Sum(:age, Selected())));
dt << select Where(:age == 12);
// Or select rows by hand and see how numbers change dynamically

 

txnelson

Super User

Joined:

Jun 22, 2012

Re: Col (Sum) function by selecting specific rows

What you want to do is to use the ability to reference values in a data table as a vector of data, and pass to it the matrix of the selected rows

dt = Open("$SAMPLE_DATA/Big Class.jmp");
dt << select Where(:age == 12); 
n = Sum(:age[dt << Get Selected rows]);
Jim
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Re: Col (Sum) function by selecting specific rows

@txnelson : 
       Thank you . While I am familiar with this approach - what surprised me was the way @ms was solving the same problem . 

dt = Open("$SAMPLE_DATA/Big Class.jmp");
dt << New Column("Conditional sum", Formula(Col Sum(:age, Selected())));  // This Works 
dt << select Where(:age == 12);
n = Col Sum(:age,Selected());  // This yields an error 

     While I understand that line 4 can be easily replaced with your solution i.e. and that is my goto solution,

n = Sum(:age[dt << Get Selected Rows]);

     I dont understand why this line of code is resulting in an error in my case . 

n = Col Sum(:age,Selected()); 

    The documentation doesn't show that this feature exists ( atleast in JPM 12)  - so was really excited by this new trick but it seemed to work in the case @ms Solved the question earlier. Can either of you explain why the exact same code works in a column formula but throws an error outside ? 

Uday 

Best
Uday
txnelson

Super User

Joined:

Jun 22, 2012

Re: Col (Sum) function by selecting specific rows

Your example code:

dt = Open("$SAMPLE_DATA/Big Class.jmp");
dt << select Where(:age ==12); 
n = Col Sum(:age,Selected()); 

The Col Sum() function operates differently in open code vs. in a formula.  When processing, the data, in open code, if the second element is present, JMP cannot execute the statement since the resulting value is going to return more than one value.  That is, the returned value in your example would require the variable "n" to have 2 values.  The Col Sum() for selected rows and the Col Sum() for non selected rows.  I suppose, that JMP could return a list of values, but as @ms indicated the Summarize() function is used for this.  Therefore, the reason the second element in the Col Sum() function is not valid in open code.

Spoiler
 
Jim
twillkickers

Contributor

Joined:

May 21, 2018

Re: Col (Sum) function by selecting specific rows

When I use 

Col Sum( :ColumnName, Selected() )

I get the following error:

Row number not set or in range in access or evaluation of 'Selected' , Selected/*###*/()

Could somebody tell me what might be happening?