cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Submit your abstract to the call for content for Discovery Summit Americas by April 23. Selected abstracts will be presented at Discovery Summit, Oct. 21- 24.
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 VI

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.