Calculate difference within a column

[mphillips27]

Hello,

I have data that is grouped by groups (group 1, group 2, etc) and then sorted by the meeting date of that group. We are tracking the difference of several values between the latest meeting date and previous meeting date. So the original data looks like below

Group	Tile	Meeting date	Value	Value 2	Value 3
group 1	1	10/1/2021	100	100	100
group 1	2	9/1/2021	50	50	50
group 2	1	10/1/2021	400	400	400
group 2	2	9/1/2021	300	300	300
group 3	1	10/1/2021	800	800	800
group 3	2	9/1/2021	600	600	600
group 4	1	10/1/2021	200	200	200
group 4	2	9/1/2021	100	100	100

What I would like to do is calculate the difference between each groups latest and previous meeting. So one desired result would be this

Group	Tile	Meeting date	Value	Value 2	Value 3
group 1	1	10/1/2021	100	100	100
group 1	2	9/1/2021	50	50	50
group 2	3		50	50	50	< -- Variance between first meeting - second meeting
group 2	1	10/1/2021	400	400	400
group 2	2	9/1/2021	300	300	300
group 3	3		100	100	100	< -- Variance between first meeting - second meeting
group 3	1	10/1/2021	800	800	800
group 3	2	9/1/2021	600	600	600
group 4	3		200	200	200	< -- Variance between first meeting - second meeting
group 4	1	10/1/2021	200	200	200
group 4	2	9/1/2021	100	100	100
group 5	3		100	100	100	< -- Variance between first meeting - second meeting

or another result could be that it updates the 9/1/21 value with the difference instead of inserting a row for the difference like this

Group	Tile	Meeting date	Value	Value 2	Value 3
group 1	1	10/1/2021	100	100	100
group 2	3		50	50	50	< -- Variance between first meeting - second meeting
group 2	1	10/1/2021	400	400	400
group 3	3		100	100	100	< -- Variance between first meeting - second meeting
group 3	1	10/1/2021	800	800	800
group 4	3		200	200	200	< -- Variance between first meeting - second meeting
group 4	1	10/1/2021	200	200	200
group 5	3		100	100	100	< -- Variance between first meeting - second meeting

I'm assuming this is done with a multirow formula tool but not sure how! thanks!

[csmith11 · Accepted solution]

See solution attached.

Please let me know if this satisfies your requirements.

[csmith11]

This should satisfy the requirements for the second option. 

[atcodedog05]

Hi @mphillips27 

My take on this.

Workflow:

Hope this helps : )

[csmith11]

@atcodedog05 's take is a much more clever approach. Frankly, its the better solution. 

As bonus if the number of values many increase or decrease (could there be a value 4, value 5, value 6) you may consider Transposing before and cross tabbing after the summarize tool to make this workflow more dynamic. You'll also have to check the Dynamic or Unknown Fields option in the Multi Field Formula tool.

[atcodedog05]

Hi @csmith11 

Agreed, adding (transpose)-(summarize)-(crosstab) can definitely make the workflow dynamic 🙂