How to calculate accumulated amount in script?

[#1]

Hi!

YTD-questions seems to be a common question but I can't find a previous solution for my YTD-problem.

I got a table that inclueds Unit, Period and Amount_Month. I like to calculate a new field in the script with Amount_YTD as shown in the table below.

Unit	Period	Amount_Month	Amount_YTD
C	200910	25	25
C	200911	50	75
C	200912	-600	-525
A	201001	400	400
A	201002	-200	200
A	201003	0	200
A	201004	300	500
A	201005	-500	0
A	201006	-100	-100
A	201007	200	100
A	201008	300	400
A	201009	0	400
A	201010	100	500
A	201011	250	750
A	201012	-150	600
B	201001	200	200
B	201002	300	500
B	201003	0	500
B	201004	100	600
B	201005	250	850
B	201006	-150	700
B	201007	400	1100
B	201008	-200	900
B	201009	0	900
B	201010	200	1100
B	201011	300	1400
B	201012	-150	1250

Any ideas?

[Miguel_Angel_Baeyens · Accepted solution]

Hello Kristofer,

Using peek() and previous() functions will help you to do that. My untested guess for your new field is

If(Unit = Previous(Unit), Rangesum(Amount_Month, Peek(Amount_Month))) AS Amount_YTD,

Of course you can add new conditionals to compare with previous periods or previous fields.

Anyway, the more complex your field is, the longer the load will take.

Hope that helps!

[#3]

Thanx for your answer. Your tip got me on the right track. I had to adjust the last peek to Amount_YTD. This soleved my problem:

If

(Unit = Previous(Unit) and left(Period,4) = previous(left(Period,4)), rangesum(Amount_Month, Peek('Amount_YTD')),Amount_Month) as Amount_YTD