Hi all,
i need some help with my workflow.
My input file is in the format of an excel file's pivot table - running total in Date and it has data all the way till December (this is just part of the data, from Jan to Mar)
| Region | Project | 1/1/2020 | 2/1/2020 | 3/1/2020 |
| AMER | A | -1 | -2 | -4 |
| AMER | B | 0 | -4 | -10 |
| AMER | C | -20 | -25 | -26 |
| APAC | A | -15 | -16 | -20 |
| APAC | B | -3 | -5 | -7 |
My desired output is:
| Region | Project | Month | Achieved savings | To-be Achieved savings |
| AMER | A | Jan | -1 | -3 |
| AMER | A | Feb | -2 | -2 |
| AMER | A | Mar | -4 | 0 |
| AMER | B | Jan | 0 | -10 |
| AMER | B | Feb | -4 | -6 |
| AMER | B | Mar | -10 | 0 |
| AMER | C | Jan | -20 | -6 |
| AMER | C | Feb | -25 | -1 |
| AMER | C | Mar | -26 | 0 |
| APAC | A | Jan | -15 | -5 |
| APAC | A | Feb | -16 | -4 |
| APAC | A | Mar | -20 | 0 |
| APAC | B | Jan | -3 | -4 |
| APAC | B | Feb | -5 | -2 |
| APAC | B | Mar | -7 | 0 |
The formula for the 'To-be achieved savings' is December's savings according to the correct region and project - the current month's savings.
eg. if we are calculating for AMER, Project B, March (and if the December's savings for this group of data = -100), to be achieved savings = -100 - (-10) = -90
this workflow might required a join tool..? but i cant figure out how to configure the workflow, please help!
Thank you in advance!
🙂
