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 |
Gelöst! Gehe zu Lösung.
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
Hi Ben,
Thank you for your super fast answer. It worked.