Col Cumulative Sum without calling the current row's value

[#1]

Hello, I am quite new to writing JMP scripts and primarily use "Edit Formula" for my work. I have been browsing solutions related to Col Cumulative Sum and could not find one for my specific problem. The simplified version of my data table is as follows.

Row	Week	Col Cumulative Sum	My Wish
1	1	1	0
2	2	3	1
3	3	6	3
4	4	10	6
5	5	15	10
6	6	21	15

The formula below works if the current row value of "Week" is fixed or if I have two separate columns "Week" and "My Wish."

If( Row() != 1,
	Lag( Col Cumulative Sum( :Week ), 1 ),
	0
)

The issue is that the value of the previous row's "Week" is fixed but the current row's is not. And I only have one column "Week" to work with. I am developing a formula that simulates progression based on the previous simulation value. I am simulating the current row's "Week" value based on the previous row's.

Suppose I am working on Row 4 in the column "Week." I want to cumulatively sum all the previous "Week" values without calling the current "Week" value in the function. Otherwise, it creates an illegal reference or cycling problem in my case.

I tried using subscript instead of lag since the lag function calls the current row's "Week" value. It did not work.

If( Row() != 1,
	Col Cumulative Sum( :Week )[Row() - 1],
	0
)

I hope my explanation was clear enough. Thank you for your time and expertise in advance. I use JMP Pro 17.

[#2]

Hello again. I am rephrasing my question in the hope that I can make the frustration go away.

Row	R	Cases
1	.	[1, 1, 2, 1]
2	1.4
3	1.67
4	2.3

I have 2 columns, R and Cases. Cases are calculated by multiplying R from the current row with Cases from the previous row. In addition, R needs to be modified (reduced) based on the sum of Cases from the previous row. 

R (row2) in column Cases = R (row2) * modifier

modifier = 1 - ((cumulative sum of Cases from row1 to the row before the current row * 4)/Total Population)

Total Population is a parameter and is 100. 

The resulting modifier is multiplied with Cases from row1 to create Cases in row2. 

For instance, I am expecting the modified R (row2) to be 1.12 and Cases (row2) to be [1.12, 1.12, 2.24, 1.12]

1.4 * 1 - (5 * 4/100) = 1.12

1.12 * [1, 1, 2, 1] = [1.12, 1.12, 2.24, 1.12]

Then, I expect the modified R (row3) to be 0.71 and Cases (row3) to be [0.8, 0.8, 1.6, 0.8]

1.67 * 1 - (10.6 * 4/100) = 0.71

10.6 is from 5 cases in row1 and 5.6 cases in row2.

0.71 * [1.12, 1.12, 2.24, 1.12] = [0.8, 0.8, 1.6, 0.8]

I am having a problem in getting the green value. My current attempt at writing a JMP script is as follows.

If( Row() == 1,
	Matrix( {1, 1, 2, 1} ),
	:R * (1 - (Summation( i = 1, N Row() - 1, Sum( :Cases ) ) * 4) / Total Population) * Lag( :Cases, 1 )
)

I tried Index, Lag, Row() - 1, Col cumulative sum, Summation, etc to get the modifier, and nothing has worked. I mainly get either

1) illegal referencing or

2) unsubscriptable

Thank you once again for your time and expertise.

[#3 · Accepted solution]

Here is my solution, however, it does not match your solution.  I believe your calculations as shown are incorrect for Row 3. See my script, which includes your sample data table.

Names Default To Here( 1 );
dt = New Table( "Example",
	Add Rows( 4 ),
	New Column( "R",
		Numeric,
		"Continuous",
		Set Values( [., 1.4, 1.67, 2.3] )
	),
	New Column( "Cases",
		Expression,
		"None",
		Set Values( {[1, 1, 2, 1], Empty(), Empty(), Empty()} )
	)
);

// The calculation script
casesSum = 0;
For(i=2,i<=nrows(dt),i++,
		Eval( Eval Expr( casesMatrix = Expr( :cases[i - 1] ) ) );
		:R[i] = :R[i] * (1 - (Sum( casesMatrix )+casesSum) * 4 / 100);
		:Cases[i] = casesMatrix * :R[i];
		casesSum = casesSum + sum(casesMatrix);
);


// Row 3 R does not seem to be correct
// I can not come up with .71, I come up with .96192
show(1.4 * (1 - (5 * 4/100)) ); 
show(1.67 * (1 - (10.6 * 4/100)) ); 

[#4]

Thank you very much. The for loop seems to be the key here. I have yet to learn the functions, Eval, Eval Expr, and Expr.

And you are right, my calculations in the example were off.

[#5]

Hello, I've been learning a lot about writing JSL scripts through the forum and watching lecture videos. I have a follow-up question regarding the following script. I got the same results with the second line. May I know why you would choose the first line over the second one? 

Eval( Eval Expr( casesMatrix = Expr( :cases[i - 1] ) ) )

Eval( casesMatrix = :cases[i - 1]  )

 Additionally, is there a way to avoid modifying the column "R" and keeping the original values? I want the modified R values to remain inside the column "Cases" without appearing anywhere. I tried assigning a different variable name, "Rx", to the modified R as below. However, this approach requires me to create an empty column named "Rx" to work.

:Rx [i] = :R[i] * (1 - (Sum( casesMatrix )+casesSum) * 4 / 100);

Many thanks in advance for your time and expertise.

[#6]

You suggested 2nd line is great.  My reason for using the syntax I used is just an old habit.

The way that Rx can be used without creating an empty column, is to use it as a memory variable reference.  Here is the code to do that

// The calculation script
casesSum = 0;
For(i=2,i<=nrows(dt),i++,
		Eval(  casesMatrix = Expr( :cases[i - 1] ) );
		Rx = :R[i] * (1 - (Sum( casesMatrix )+casesSum) * 4 / 100);
		:Cases[i] = casesMatrix * Rx;
		casesSum = casesSum + sum(casesMatrix);
);

[#7]

This script uses txnelson's solution as the starting point and makes some modifications to it. Purpose of this script is just to provide you other example for learning purposes using other techniques (For Each Row instead of For loop, gets values to matrix from :R and some other changes)

Names Default To Here(1);
dt = New Table("Example",
	Add Rows(4),
	New Column("R", Numeric, "Continuous", Set Values([., 1.4, 1.67, 2.3])),
	New Column("Cases", Expression, "None", Set Values({[1, 1, 2, 1], Empty(), Empty(), Empty()}))
);

r_m = :R << Get values;
casesSum = 0;
For Each Row(dt,
	If(Row() == 1, Continue()); // skip first row
	
	casesMatrix = Lag(:cases, 1);
	r_m[Row()] = r_m[Row()] * (1 - (Sum(casesMatrix) + casesSum) * 4 / 100);
	:Cases = casesMatrix * r_m[Row()];
	casesSum = casesSum + Sum(casesMatrix);
);