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

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

Kast — Tue, 02 Apr 2024 16:55:14 GMT

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.

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

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

jthi — Tue, 02 Apr 2024 17:41:25 GMT

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

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

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

Kast — Tue, 02 Apr 2024 18:10:09 GMT

Thank you Jarmo, worked perfectly!