739077_de-DE

liliconcom

Col Cumulative Sum, ohne den Wert der aktuellen Zeile aufzurufen

Hallo, ich bin ziemlich neu im Schreiben von JMP-Skripten und verwende für meine Arbeit hauptsächlich „Formel bearbeiten“. Ich habe nach Lösungen im Zusammenhang mit Col Cumulative Sum gesucht und konnte keine für mein spezifisches Problem finden. Die vereinfachte Version meiner Datentabelle lautet wie folgt.

Reihe	Woche	Col Cumulative Sum	Mein Wunsch
1	1	1	0
2	2	3	1
3	3	6	3
4	4	10	6
5	5	15	10
6	6	21	15

Die folgende Formel funktioniert, wenn der aktuelle Zeilenwert „Woche“ fest ist oder wenn ich zwei separate Spalten „Woche“ und „Mein Wunsch“ habe.

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

Das Problem besteht darin, dass der Wert der „Woche“ der vorherigen Zeile festgelegt ist, der Wert der aktuellen Zeile jedoch nicht. Und ich habe nur eine Spalte „Woche“, mit der ich arbeiten kann. Ich entwickle eine Formel, die den Fortschritt basierend auf dem vorherigen Simulationswert simuliert. Ich simuliere den „Woche“-Wert der aktuellen Zeile basierend auf dem der vorherigen Zeile.

Angenommen, ich arbeite an Zeile 4 in der Spalte „Woche“. Ich möchte alle vorherigen „Woche“-Werte kumulativ summieren, ohne den aktuellen „Woche“-Wert in der Funktion aufzurufen. Andernfalls entsteht in meinem Fall ein illegales Referenz- oder Fahrradproblem.

Ich habe versucht, Subscript anstelle von Lag zu verwenden, da die Lag-Funktion den „Woche“-Wert der aktuellen Zeile aufruft. Es hat nicht funktioniert.

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

Ich hoffe, meine Erklärung war klar genug. Vielen Dank im Voraus für Ihre Zeit und Ihr Fachwissen. Ich verwende JMP Pro 17.

Automatisierung und SkripterstellungPrädiktive Modellierung und maschinelles LernenRe: Col Cumulative Sum without calling the current row's value

You can deduct current weeks value from the cumulative sum

Col Cumulative Sum(:Week) - :Week

 

Re: Col Cumulative Sum without calling the current row's value

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.

 

Re: Col Cumulative Sum without calling the current row's value

Is "Sim Cases" a matrix or a column in a data table?

 

I do not understand 

You appear to be making your solution overly complex.

sim cases = [3,5,2,8];

sum1 = sum(sim cases);
sum2 = sum(([1 1 1 1]*sim cases)[1]);
show(sum1,sum2);

Both sum1 and sum2 provide the same value

sum1 = 18;
sum2 = 18;

I encourage you to read the Discovering JMP document available under the Help pull down menu.

Re: Col Cumulative Sum without calling the current row's value

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

 

Re: Col Cumulative Sum without calling the current row's value

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

Re: Col Cumulative Sum without calling the current row's value

If I am understanding correctly, I think this might be the solution

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

Re: Col Cumulative Sum without calling the current row's value

I would use separate columns to avoid all sorts of issues, but I'm not exactly sure what you are trying to achieve as you have full data in your example. Maybe something like this if Col Cumulative Sum doesn't work for you (calculate value using Lag and variable)

 

If(Row() == 1,
	val = 0,
	val = val + Lag(:Week);
);
val;

 

 

Re: Col Cumulative Sum without calling the current row's value

Thank you for your suggestion on using the Index subscript. I now encounter the issue "object not subscriptable."

 

My week example was over-simplified and I have a matrix Sim Cases of 4 rows and 1 column in place of week. To transform it into one summarized numerical value, I multiplied the matrix with [1 1 1 1] and added a subscript [1]. It was then followed by the Index subscript.

 

Sum( ([1 1 1 1] * :Sim Cases)[1][Index( 1, Row() - 1 )] )

I think the resulting numerical value from the original 4x1 matrix is not accepting any subscript after [1]. Or it could be because of the illegal reference (recursion) again.

Re: Col Cumulative Sum without calling the current row's value

Yes, at first, I tried to emulate in the JMP data table what I usually do in Excel spreadsheets by separating columns. I would then call the values of the previous row of a different column. The JMP data table does not behave as Excel spreadsheets, and I am encountering cyclical problems.

 

So, I keep changing strategies and my current understanding is to keep every required variable in one column to avoid cyclical problems.

Re: Col Cumulative Sum without calling the current row's value

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.

 

Re: Col Cumulative Sum without calling the current row's value

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.

Re: Col Cumulative Sum without calling the current row's value

The moment I use (:Week) in my formula I get an illegal reference problem. The current row (:Week) has not been fully calculated yet. I can only recall (:Week) from the previous row in theory.

Re: Col Cumulative Sum without calling the current row's value

I will keep learning JMP. For now, I will just stick to my Excel spreadsheet. Thanks everyone for your responses.

 

Sim Cases is a column and I am using Sim Cases of previous rows in creating new rows. So I guess it is both a column and a variable inside that same column.

Betreff: Col Kumulative Summe ohne Aufruf des Wertes der aktuellen Zeile

Das Verhalten von JMP-Tabellen und -Arbeitsblättern ist völlig unterschiedlich. Dieser alte Beitrag „JMP ist kein Arbeitsblatt“ könnte eine gute Lektüre sein, da er einige Beispiele und Erklärungen enthält. Die meisten Dinge können Sie in JMP erledigen, aber es kann einige Zeit dauern, bis Sie verstanden haben, wie sich Spalten und Zeilen in JMP im Vergleich zu einzelnen Zellen in Excel verhalten.

Betreff: Col Kumulative Summe ohne Aufruf des Wertes der aktuellen Zeile

Dieses Skript verwendet txnelsons Lösung als Ausgangspunkt und nimmt einige Änderungen daran vor. Ziel dieses Skripts ist es, Ihnen ein weiteres Beispiel für Lernzwecke mit anderen Techniken bereitzustellen (For Each Row anstelle einer For-Schleife, Werte werden aus :R in die Matrix übernommen und einige weitere Änderungen).

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