Subscribe Bookmark RSS Feed

Col (Sum) function by selecting specific rows

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

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

8029_Screen Shot 2015-02-07 at 10.40.11.png

1 ACCEPTED SOLUTION

Accepted Solutions
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() )

4 REPLIES
ian_jmp

Staff

Joined:

Jun 23, 2011

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.

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

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

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

ms

Super User

Joined:

Jun 23, 2011

Yes, more than one grouping variable is supported.