subtract two columns by group of third column

[Anonymous]

suppose I have the following table 

id	group	val1
1	A	2
2	A	3
3	A	4
4	B	4
5	B	6
6	B	2

How would I get the following result?

id	group	val1	diff
1	A	2	0
2	A	3	1
3	A	4	1
4	B	4	0
5	B	6	2
6	B	2	-4

Explanation of new column diff:

1. It is the difference between the current row and previous row of column val1

2. Whenever a new group starts in Group column the difference resets to 0

Essestially, the problem is how to subtract current value from it's previous value in a column by group (based on some third column)?

[amitchandak · Accepted solution]

@Anonymous , Try a new column like

new column =
var _max = maxx(filter(table, [group] = earlier([group]) && [ID] < earlier([ID])),[ID])
return
val1 - maxx(filter(table, [group] = earlier([group]) && [ID] = _max),[val1])