How to properly use LAG function in Conditional Statements?

UserID16644

I have this formula but it is adding the value on the same row.

 

If(
	:Column 2 != Lag( :Column 2 ) | Row() == 1, :Column 1,
	Row() == 2 | Lag( :Column 2, 1 ) != :Column 2, :Column 2 - (:Column 1 + Lag( :Column 1, 1 )),
	Lag( :Result ) - :Column 1
)

 

 

 

 

What I would want to do is something like this:

Column 1 and Column 2 are given values. If Column 2 value changes, the result should be like the starting value in Column 1.

Column 1	Column 2	Result	(should be computation)
10	20	10	same value in Column 1
2	20	10	Column 2 - Column 1 row 1 (20-10)
5	20	8	Column 2 - Column 1 rows 1 to 2 (20 - 12 +2)
3	20	3	Column 2 - Column 1 rows 1 to 3 (20 - 12 +2 +5)
2	15	2
1	15	13
3	15	12
4	15	9

How can I create a table equation like this?

jthi

By vars are arguments which you can use with Col statistical functions Statistical Functions (jmp.com)

 

Names Default To Here(1);

dt = New Table("Untitled 4",
	Add Rows(8),
	Compress File When Saved(1),
	New Column("Column 1", Numeric, "Continuous", Format("Best", 12), Set Values([10, 2, 5, 3, 2, 1, 3, 4])),
	New Column("Column 2", Numeric, "Continuous", Format("Best", 12), Set Values([20, 20, 20, 20, 15, 15, 15, 15])),
	New Column("Result", Numeric, "Continuous", Format("Best", 12), Set Values([10, 10, 8, 3, 2, 13, 12, 9]))
);

dt << New Column("Calc",
	Numeric,
	Continuous,
	Formula(
		If(Row() == Col Min(Row(), :Column 2),
			:Column 1
		,  
			:Column 2 - Col Cumulative Sum(:Column 1, :Column 2) + :Column 1
		)
	)
);