topic Re: Col (Sum) function by selecting specific rows in Discussions

Col (Sum) function by selecting specific rows

saitcopuroglu — Tue, 18 Oct 2016 22:32:21 GMT

Hello,

I would like to create a column which calculates another columns mean or sum but I would like to specify the function by :

Let’s say if we select the year the calculation to be occured by "Year" or,

Let’s say col (sum) of NetRevenueWithoutVat by Year or "NetRevenueWithoutVat" by "Month (Stay Date)" or

Col (Sum) functions of specific row numbers...

Would anyone please share his/her knowledge how to do it in scripting or by entering the Col(Sum) function as a formula in a column?

The logic behind it?

Many thanks in adance

Re: Col (Sum) function by selecting specific rows

ian_jmp — Wed, 13 Jun 2018 18:16:23 GMT

The answer may depend on what happens next. You can certainly do:

NamesDefaultToHere(1);
// (1) Get some data
dt1 = Open("$SAMPLE_DATA/Big Class.jmp");
// (2) Get summary info into a list and a vector
Summarize(groups = By(:sex), groupMeans = Mean(:height));
// (3) Make a table using the summary info
dt2 = NewTable("Height by Gender",
  NewColumn("Gender", Character, Nominal, Values(groups)),
  NewColumn("Mean Height", Numeric, Continuos, Values(groupMeans))
);

(see the JSL Scripting Guide for more options with Summarise).

If you need step 3, you may be better using either 'Tables > Summary' or 'Analyse > Tabulate'.

You can also aggregate across any set of rows, so long as you can express what that selection is.

Re: Col (Sum) function by selecting specific rows

ms — Mon, 09 Feb 2015 18:46:57 GMT

Use ColSum() in column formulas. The formula Col Sum( :NetRevenueWithoutVat, :Year ) will return the sum (not the cumulative sum) grouped by Year.

Try this formula for a ColSum() that changes dynamically with row selection:

Col Sum( :NetRevenueWithoutVat, Selected() )

Re: Col (Sum) function by selecting specific rows

saitcopuroglu — Mon, 09 Feb 2015 22:33:15 GMT

Thank you!

This is what I was looking for.

Does that mean I could go further and let's say Col Sum( :NetRevenueWithoutVat, :Year, :Agency ) ?

Re: Col (Sum) function by selecting specific rows

ms — Mon, 09 Feb 2015 22:36:20 GMT

Yes, more than one grouping variable is supported.

Re: Col (Sum) function by selecting specific rows

uday_guntupalli — Thu, 07 Sep 2017 16:51:19 GMT

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/*###*/()

Re: Col (Sum) function by selecting specific rows

uday_guntupalli — Sat, 09 Sep 2017 17:48:28 GMT

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

Re: Col (Sum) function by selecting specific rows

txnelson — Sat, 09 Sep 2017 18:40:08 GMT

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]);

Re: Col (Sum) function by selecting specific rows

ms — Sat, 09 Sep 2017 21:01:28 GMT

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

Re: Col (Sum) function by selecting specific rows

uday_guntupalli — Mon, 11 Sep 2017 16:27:49 GMT

@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

Re: Col (Sum) function by selecting specific rows

txnelson — Mon, 11 Sep 2017 17:12:06 GMT

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.

Re: Col (Sum) function by selecting specific rows

twillkickers — Mon, 06 Aug 2018 21:05:51 GMT

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?

Re: Col (Sum) function by selecting specific rows

Mitch103 — Fri, 07 Sep 2018 21:06:25 GMT

Are you able to use this formula and define the logic for a particular column? For example, I am trying to sum the dollar amounts for each record between two dates. So the formula would look like this:

Col Sum( :Dollar Amount, :Date)

But I am not sure how to define the date range.

Re: Col (Sum) function by selecting specific rows

uday_guntupalli — Fri, 07 Sep 2018 21:13:57 GMT

@Mitch103,
Try something like this as suggested by @ms:

dt = Current Data Table(); 

dt << Select Where(:Date > StartDate & :Date <EndDate); 

dt << New Column("ConditionalSum",Numeric,continuous,Formula(Col Sum(:Date,Selected()));

Re: Col (Sum) function by selecting specific rows

Mitch103 — Fri, 07 Sep 2018 21:26:09 GMT

So forgive me, but I am a new JMP user. I am only using the formula editor, not JSL. I am not sure if what you are giving me is for JSL or the forumula editor. Am I not able to put the conditions into the formula like this?

Col Sum(:Settlement Amount,:Date >= 8/1/2018 & :Date <= 8/7/2018,:Card Number)

Except the problem I am running into is that JMP is autocorrecting it to be division, not a date formula.

Re: Col (Sum) function by selecting specific rows

Mitch103 — Fri, 07 Sep 2018 21:31:26 GMT

Nevermind, got it!

Col Sum(
:Settlement Amount,
:Date >= 01Aug2018 & :Date <= 07Aug2018,
:Card Number
)

Re: Col (Sum) function by selecting specific rows

txnelson — Fri, 07 Sep 2018 22:10:24 GMT

You would have to create a separate column that contains a grouping column for each date range you want to use, or you might want to use something like:

Col Sum(
	If(
		:Event Date >= Informat( "02/01/2001", "mm/dd/yyyy" ) & :Event Date <=
		Informat( "02/28/2001", "mm/dd/yyyy" ),
		:Total Fatal Injuries,
		0
	)
)