How to properly use LAG function in Conditional Statements?

[#1]

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?

[#2]

Where is the value 12 coming from to row 3+ calculations?

[#3]

Sorry, that's 10

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 - 10 +2)
3	20	3	Column 2 - Column 1 rows 1 to 3 (20 - 10 +2 +5)
2	15	2
1	15	13
3	15	12
4	15	9

[#4]

You can use col cumulative sum

If(
	Row() == 1, :Column 1
,  
	:Column 2 - Col Cumulative Sum(:Column 1) + :Column 1
)

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() == 1, :Column 1
		,  
			:Column 2 - Col Cumulative Sum(:Column 1) + :Column 1
		)
	)
);

I guess Column 2 is some sort of grouping which should reset your "counter"? You can use Col Min to detect that change and use that column as byVar if needed

[#5]

Yes, Column 2 acts like a reset counter. How do you use byVar in this? Haven't heard and used that function before

[#6 · Accepted solution]

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

[#7]

@UserID16644, if your :Column2 ever has the same value again, you'll get undesired results.

New Table( "Untitled 155",
	Add Rows( 12 ),
	New Column( "Column 1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [10, 2, 5, 3, 2, 1, 3, 4, 6, 2, 5, 3] )
	),
	New Column( "Column 2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [20, 20, 20, 20, 15, 15, 15, 15, 20, 20, 20, 20] )
	),
	New Column( "Result",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			If(
				:Column 2 != Lag( :Column 2 ) | Row() == 1, :Column 1,
				Row() == 2 | Lag( :Column 2, 2 ) != :Column 2,
					:Column 2 - Lag( :Column 1 ),
				Lag( :Result ) - Lag( :Column 1 )
			)
		)
	),
	New Column( "Result 2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula(
			If( Row() == Col Min( Row(), :Column 2 ),
				:Column 1,
				(:Column 2 - Col Cumulative Sum( :Column 1, :Column 2 )) + :Column 1
			)
		)
	)
)