Substract value from current row against previous row and vice versa

[Krishnan92]

Hello All,

I have a scenario where I have to subtract the value from current record against the previous record. Below is the example. I need to achieve the results as in column C

A	B	C
1	1000
1	200	800
1	200	600
1	500	100
2	1000
2	300	700
2	100	600
2	50	550

[ggijben · Accepted solution]

Hi @Krishnan92 , 

If you want to do this in the script, you can use the following expression:

IF( PEEK(A) = A,  IF(ISNULL( PEEK(C) ), PEEK(B) - B,  PEEK(C) - B)) AS C

Result:

[Krishnan92]

Hello @ggijben thank you so much for your response. But I have fields A and B alone. C should be my output derived field. 

[ggijben]

Hi @Krishnan92 ,

Yes, that is correct. Column C will be created with the formula in my previous post. You will only need columns A + B. See the attached app for an example.

[Krishnan92]

@ggijben Thank you! It worked perfectly. Also do you know how to add all the ID 1 values together and then do the subtraction one by one as mentioned above.  Like the example below

A	B	C
1	1000	1900	>Sum of all ID 1 Values
1	200	1700
1	200	1500
1	500	1000
2	1000
2	300	700
2	100	600
2	50	550