Forum Discussion
rolling sum
Hello Community,
Hope you are well.
I need your help with the below challenge.
I want to produce a table at which :
- Hard Benefit 1 : the total sum of per year will be the sum of the value of this year plus all the values of the previous ones
- Hard Benefit 2 : same as above
For example, the correct answer to the question "what is the value of hard benefit 1 that the project PR1 has delivered in 2020?" will be 104+27, instead of 27.
Table 1 : Raw Data
| Project | Year | Hard Benefit 1 | Hard Benefit 2 | Hard Benefit 3 |
| PR1 | 2019 | 104 | 0 | 108 |
| PR1 | 2020 | 27 | 41 | 109 |
| PR1 | 2021 | 105 | 67 | 0 |
| PR2 | 2021 | 88 | 63 | 30 |
| PR2 | 2022 | 110 | 0 | 79 |
| PR3 | 2020 | 98 | 52 | 66 |
Table 2 : Desired Outcome
| 2019 | 2020 | 2021 | 2022 | 2023 | ||
| PR1 | Hard Benefit 1 | 104 | 104+27 | 104+27+105 | 104+27+105 | 104+27+105 |
| Hard Benefit 2 | 0 | 41 | 41+67 | 41+67 | 41+67 | |
| Hard Benefit 3 | 108 | 109 | 0 | 0 | 0 | |
| PR2 | Hard Benefit 1 | 0 | 0 | 88 | 88+110 | 88+110 |
| Hard Benefit 2 | 0 | 0 | 63 | 63 | 63 | |
| Hard Benefit 3 | 0 | 0 | 30 | 79 | 79 | |
| PR3 | Hard Benefit 1 | 0 | 98 | 98 | 98 | 98 |
| Hard Benefit 2 | 0 | 52 | 52 | 52 | 52 | |
| Hard Benefit 3 | 0 | 66 | 0 | 0 | 0 |
Hope my explanation makes sense.
Thank you in advance,
George
Anonymous
sry, I fixed this. Please see the attachment.
5 Replies
Anonymous
please see the attachment. hope this is helpful.
Hello ryan_mayu
Thank you for your reply. I noticed that your solution has an error. More specifically, it summarizes the values per year, but it ignors the breakdown per benefit type.
For example, the value of project PR1, hard benefit 1, year 2019 is 104. Your solution shows 212 instead, which is the sum of hard benefit 1 (value 104) and hard benefit 3 (value 108) for year 2019.
Thank you,
George
Anonymous
sry, I fixed this. Please see the attachment.