cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
  • Image to Table JMP extension imports single or batch images as data tables. Useful to extract curves from pictures or convert image data to curve data to use in analyses like Functional Data Explorer. Download and install the extension.

Discussions

Solve problems, and share tips and tricks with other JMP users.
Choose Language Hide Translation Bar
UserID16644
Level V

How to get Col Sum of a row in a conditional statement?

Hi all,

 

I have a column where I need to filter out some values to get the sum of columns (using Col Sum). Is it possible to get the col sum according to the condition? For example, every 0 code will be excluded from the computation:

 

CodeQtyTotal CountTotal % (Col Sum (qty)/total count)
050250-
025250-
160250205/250 x 100 = 82%
667250205/250 x 100 = 82%
378250205/250 x 100 = 82%

 

I tried using an If statement, but the code with 0 values still gets included when using the Col Sum formula

1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User

Re: How to get Col Sum of a row in a conditional statement?

You need two if-statements, one to avoid writing values to Code = 0 rows and one to perform the calculation

If(Code != 0,
	Col Sum(If(:Code == 0, ., :Qty))
,
	.
);

 

jthi_0-1711953822974.png

 

-Jarmo

View solution in original post

3 REPLIES 3
jthi
Super User

Re: How to get Col Sum of a row in a conditional statement?

You need two if-statements, one to avoid writing values to Code = 0 rows and one to perform the calculation

If(Code != 0,
	Col Sum(If(:Code == 0, ., :Qty))
,
	.
);

 

jthi_0-1711953822974.png

 

-Jarmo
WebDesignesCrow
Super User

Re: How to get Col Sum of a row in a conditional statement?

Hi @UserID16644 

Conditional formula is a bit tricky in JMP.

As @jthi mentioned, you need 2 IFs.

You can also add formula column as below;

 

-Add 1st IF = column sum if code not 0

 

WebDesignesCrow_1-1712029275306.png

-Add 2nd IF = display result only if code not 0

WebDesignesCrow_2-1712029413039.png

Result:

WebDesignesCrow_3-1712029497725.png

 

JSL Script (refer workflow builder / log)

Names Default To Here( 1 );

//Change column info: Total % (Col Sum Qty/Total Count)
Data Table( "dt1" ):Column 4 << Set Name( "Total % (Col Sum Qty/Total Count)" )
 << Set Formula(
	If( !:Code == 0,
		Col Sum( If( !:Code == 0, :Qty, . ) ) / :Total Count
	)
);

//Change column format: Total % (Col Sum Qty/Total Count)
Data Table( "dt1" ):"Total % (Col Sum Qty/Total Count)"n <<
Format( "Percent", 12, 2 );

 

 

BHarris
Level VII

Re: How to get Col Sum of a row in a conditional statement?

Maybe I'm missing what you're asking, but this might do what you want with a simple  Col Sum():

 

Col Sum( (:Code > 0) * :Qty, :Code > 0 )

":Code > 0" becomes 0 or 1 depending on whether it's zero or not, multiplied times Qty gives you the number you're looking for, then grouping cases where :Code >0 separate from the Code == 0 cases.

Recommended Articles