turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Col (Sum) function by selecting specific rows

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 7, 2015 12:50 AM
(3335 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Solution

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

10 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 9, 2015 9:23 AM
(2668 views)

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 9, 2015 2:33 PM
(2668 views)

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 **) ?**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Feb 9, 2015 2:36 PM
(2668 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 7, 2017 9:51 AM
(960 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 9, 2017 10:48 AM
(920 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 9, 2017 2:01 PM
(908 views)

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
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 9, 2017 11:40 AM
(915 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 11, 2017 9:27 AM
(861 views)

@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

Uday

This widget could not be displayed.