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

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

UserID16644 — Mon, 01 Apr 2024 05:48:17 GMT

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:

Code	Qty	Total Count	Total % (Col Sum (qty)/total count)
0	50	250	-
0	25	250	-
1	60	250	205/250 x 100 = 82%
6	67	250	205/250 x 100 = 82%
3	78	250	205/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

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

jthi — Mon, 01 Apr 2024 06:43:56 GMT

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))
,
	.
);

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

WebDesignesCrow — Tue, 02 Apr 2024 03:46:15 GMT

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

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

Result:

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

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

BHarris — Tue, 02 Apr 2024 20:40:16 GMT

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.