nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to write a formula that counts number of rows that are not excluded?

BrianB

I am trying to write a simple formula that will count the number of rows in my datatable that are not excluded. I want the total to be updated as I change row states using the graph builder. In another part of the formula I was able to successfully use the Excluded () function, e.g.

<jsl> X = Col Sum( :MG1_2520_LH_Setting, Excluded() );

However, I am using N Rows() to count the total number of rows and cannot see how to modify this function to only count the number of non-excluded rows.

Accepted answers

hogi

You cannot use Col sum in "plain" JSL code to assign the result to a variable.
It'a a cool aggregation function which can be used in Column Formulas.

Col Sum sums up the individual entries of a column - and as @Thierry_S indicates, you need a column with ones to count the rows. But actually, you don't need a real column, instead of a column, you can also specify "1".
If you just want to count rows which are NOT excluded, use:

New Column( "non excluded",Formula( Col Number( If( !Excluded(), 1 ) ) ))

The complicated syntax is necessary because by default the Col ... aggregation doesn't respect the excluded state - it uses all values of the column and therefore counts all rows.

All comments

Thierry_S

Hi,

I have an ugly but usable solution: create a Dummy column populated with the value = 1 and then use the formula as you showed in JSL:

Col Sum(:Dummy, Excluded())

There is probably a much more elegant solution, so you may want to wait to accept the solution.

Best,

hogi

You cannot use Col sum in "plain" JSL code to assign the result to a variable.
It'a a cool aggregation function which can be used in Column Formulas.

New Column( "non excluded",Formula( Col Number( If( !Excluded(), 1 ) ) ))

The complicated syntax is necessary because by default the Col ... aggregation doesn't respect the excluded state - it uses all values of the column and therefore counts all rows.

hogi

There are other approaches in JMP which automatically take into account which rows are excluded and which ones are not.

If you don't need a new column but just a single value, you can use a summary table with a transform column:

Summary( N( Transform Column( "one", Formula( !Excluded() ) ) ));

If you need the value to process it further via JSL, Summarize provides an easy alternative:

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

dt << Select Rows( 5::12 ) <<Exclude;
summarize ( count = count(:height));	
count

BrianB

I was able to use the code as below to achieve what I needed. The other solutions worked as well but this was the easiest to implement for me. Thanks!

Col Number( If( !Excluded(), 1 ) )