topic Re: How to properly use LAG function in Conditional Statements? in Discussions

How to properly use LAG function in Conditional Statements?

UserID16644 — Thu, 22 Feb 2024 02:27:23 GMT

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?

Re: How to properly use LAG function in Conditional Statements?

jthi — Thu, 22 Feb 2024 05:16:15 GMT

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

Re: How to properly use LAG function in Conditional Statements?

UserID16644 — Thu, 22 Feb 2024 05:27:21 GMT

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

Re: How to properly use LAG function in Conditional Statements?

jthi — Thu, 22 Feb 2024 06:14:29 GMT

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

Re: How to properly use LAG function in Conditional Statements?

UserID16644 — Thu, 22 Feb 2024 06:32:46 GMT

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

Re: How to properly use LAG function in Conditional Statements?

jthi — Thu, 22 Feb 2024 06:50:34 GMT

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

Re: How to properly use LAG function in Conditional Statements?

mmarchandTSI — Thu, 22 Feb 2024 13:36:32 GMT

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