topic Re: Calculate difference between values in consecutive rows by group in Discussions

Calculate difference between values in consecutive rows by group

Alicia — Sat, 10 Jun 2023 23:36:10 GMT

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

Re: Calculate difference between values in consecutive rows by group

ih — Tue, 24 Aug 2021 14:24:09 GMT

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

Re: Calculate difference between values in consecutive rows by group

Alicia — Tue, 24 Aug 2021 14:28:45 GMT

Works perfectly! Thanks so much