topic Re: How to write a formula that counts number of rows that are not excluded? in Discussions

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

BrianB — Mon, 23 Sep 2024 20:55:01 GMT

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.

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

Thierry_S — Mon, 23 Sep 2024 21:08:08 GMT

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,

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

hogi — Mon, 23 Sep 2024 21:33:00 GMT

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.

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

hogi — Mon, 23 Sep 2024 21:37:31 GMT

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

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

BrianB — Tue, 24 Sep 2024 14:58:25 GMT

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