topic Re: How to subtract values from previous row values? in Discussions

How to subtract values from previous row values?

UserID16644 — Wed, 21 Feb 2024 07:06:23 GMT

Hi all

Can someone help me how to formulate a formula that would subtract Column 2 to the previous value in Column 1? It should look like this. The column Equation only shows how the subtraction of values should be.TIA

Re: How to subtract values from previous row values?

David_Burnham — Wed, 21 Feb 2024 07:30:29 GMT

You can use the LAG function:

:Column 1 - Lag( :Column 2, 1 )

Re: How to subtract values from previous row values?

UserID16644 — Wed, 21 Feb 2024 07:35:10 GMT

Hi, how about if I wanted to retain the row value before the values in Column 2 changes? Like this one

Re: How to subtract values from previous row values?

David_Burnham — Wed, 21 Feb 2024 07:53:25 GMT

Then you need some conditional logic. Something like:

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

Re: How to subtract values from previous row values?

UserID16644 — Wed, 21 Feb 2024 08:52:19 GMT

Hi, I have a follow up question tho. What if I needed to add the values from Column 1 to be subtracted in Column 2?

For example,

Column 1	Column 2	Result
10	20	10
2	20	8
5	20	3
3	20	0
2	15	5
1	15	13
3	15	12
4	15	5

Re: How to subtract values from previous row values?

mmarchandTSI — Wed, 21 Feb 2024 16:22:00 GMT

Could you double-check your numbers here? I'm not sure what's going on when :Column 2 == 15.

But I think this is what you want (based on David's script):

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

Re: How to subtract values from previous row values?

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

Column 2 are given values. Apologies, my table is a bit messed up. Values within the same row should not be subtracted on the added values. It should be

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 subtract values from previous row values?

mmarchandTSI — Thu, 22 Feb 2024 12:15:25 GMT

In that case:

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