Calculate difference between values in consecutive rows by group

[#1]

Hi There,

I have a data table with 2 columns like the below:

group value
1     10
1     20
1     25
2     5
2     10
2     15 

I want to create a new column, "diff", that calculates the differences between consecutive rows, grouped by "group", so I would have something like this:

group value diff
1     10    . # because there is a no previous value
1     20    10 # value[2] - value[1]
1     25    5  # value[3] value[2]
2     5     . # because group is changed
2     10    5  # value[5] - value[4]
2     15    5  # value[6] - value[5]

Please can you help me with the jsl for the column formula?

Many thanks,

Alicia

[#2 · Accepted solution]

If the table is always sorted by group like your example you could do this with an if statement:

If( Lag( :group ) == :group,
	:value - Lag( :value ),
	.
)

[#3]

Works perfectly! Thanks so much