Calculation based on different colums

[ecemurun]

Hello,

I would appreciate your help for the below.

I am trying to do the calculation of  (A+E-I) in column Group_2 based on Group_1 and Date columns. The output should give me the result of (A+E-I) for each date in Date column based on Group_1  e.g. for the date 01/2018 for 1 (in Group_2).

I Would appreciate if you can show me a way. Thanks a lot from now!

Group_1	Date	Group_2	Group_3
1	01/2018	A	88499199
1	02/2017	E	20495841
1	02/2018	I	73523158
2	03/2016	A	3925154
2	03/2017	A	134788779
2	03/2018	E	56657015
3	04/2016	I	13015090
3	04/2017	A	24346367
3	04/2018	A	93157900
4	05/2016	A	22610337
4	05/2016	E	296460
4	05/2016	I	384300
5	05/2017	A	4635626
5	06/2016	A	32237117
5	06/2016	I	164700
6	06/2017	A	6638057
6	07/2016	A	29719962
6	07/2016	E	164700
6	07/2016	I	182350
6	07/2017	A	8344392

[BenMoss]

I would suggest you cros-tab your data.

Group by your 'Group_1' field and your 'Date' field.

Your header field will be 'Group_2' and then your value field will be 'Group_3' and choose the aggregation 'sum'.

Then you can use a formula tool to perform your A+E-I.

Alternatively you could do a formula tool which converts the 'Group_3' value to be negative when 'Group_2' = I.

Then you could use a summerize tool, grouping by your 'Group_1' and 'Date' fields and then SUM the ammended 'Group_3' field.

Ben

[BenMoss · Accepted solution]

Okay, it seems like the value in 'Group_2' is derived from the date field. So I would remove this from all the 'group bys' I mentioned, either in the cross-tab or summerize tool, depending on which method you use.

Solution attached. Let me know if this meets the requirements.

Ben

[ecemurun]

Hi Ben,

Thank you for your super fast answer. It worked.