cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
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.
Discovery is online this week, April 16 and 18. Join us for these exciting interactive sessions.
Choose Language Hide Translation Bar
Kast
Level II

How can you calculate the product of non-empty rows up to the current row?

I'm trying to write a column formula that will calculate the product of all of the (non-empty) rows up to the current row.

I've attached an example data table (& screenshot).

 

I am trying to get the "Product" column to be the product of all of the non-empty rows from the "Value" column. I show the manually calculated result and math in the "Desired Result" & "Desired Result Explanation" to the right.

Kast_0-1712075899063.png

 

A couple data set issues I tried to highlight in the example data table.

1. The number of rows per "Group" will vary between groups.

2. The "Group" will always be together and in order. All group 1 will be together. Group1 will always come before group 2.

3. The Value column will only have one non-empty row per group. It will always be the first row of that group.

 

I think this might require a "loop" or "for" type of formula, but have limited experience with building these up.

 

Even if you don't have a solution I appreciate any thoughts or links to guides or other discussions you think might lead me to a solution.

 

Previous Attempts:
I thought that txnelson's response to this question (https://community.jmp.com/t5/Discussions/How-to-multiply-all-elements-in-a-list/td-p/667633) might work but haven't gotten it to give the result I'm looking for.

 

Thank you for your time and help!

 

JMP Version: 17.2.0, Windows 10

1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User

Re: How can you calculate the product of non-empty rows up to the current row?

One option using the knowledge that the value is always found from the first row for each group

If(Row() == 1,
	val = 1;
);
If(Row() == Col Min(Row(), :Group),
	val = val * :Value;
,
	.;
);

jthi_0-1712079017042.png

Edit:

And here is other checking if :Value is missing or not

If(Row() == 1,
	val = 1;
	);
If(!IsMissing(:Value),
	val = val * :Value;
,
	.
);

You should also be able to replace first If with As Constant

As Constant(val = 1);
If(!IsMissing(:Value),
	val = val * :Value;
,
	.
);
-Jarmo

View solution in original post

2 REPLIES 2
jthi
Super User

Re: How can you calculate the product of non-empty rows up to the current row?

One option using the knowledge that the value is always found from the first row for each group

If(Row() == 1,
	val = 1;
);
If(Row() == Col Min(Row(), :Group),
	val = val * :Value;
,
	.;
);

jthi_0-1712079017042.png

Edit:

And here is other checking if :Value is missing or not

If(Row() == 1,
	val = 1;
	);
If(!IsMissing(:Value),
	val = val * :Value;
,
	.
);

You should also be able to replace first If with As Constant

As Constant(val = 1);
If(!IsMissing(:Value),
	val = val * :Value;
,
	.
);
-Jarmo
Kast
Level II

Re: How can you calculate the product of non-empty rows up to the current row?

Thank you Jarmo, worked perfectly!