rolling sum

[Anonymous]

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

[ryan_mayu]

@Anonymous 

please see the attachment. hope this is helpful.

[Anonymous]

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

[ryan_mayu · Accepted solution]

@Anonymous 

sry, I fixed this. Please see the attachment.

[Anonymous]

Hello @ryan_mayu ,

I really appreciate your quick response.

Your approach is perfect.

Please allow me one more question before I accept it as the solution. May I only ask if there is a way to produce the same solution, without the additional tables/relationships that you created?

Thank you,

George

[ryan_mayu]

@Anonymous 

yes, that was my first attempt.However I found what I got is not as same as what you need. So I tried another workaround to get your expected output.