Cumulative Totals using Stage Variables

[Dsnew]

Hi,


I am looking to get a column F2 having cumulative totals from column F1
Also I need it to reset for each year.
I am guessing stage Variables is the way to, but no clue as to how to go about it.
Can someone help me out with.
Please let me know if my requirement is not clear.
Thanks in advance.


Example

YEAR,F1,F2
--------------
2005,20,20
2005,10,30
2005,20,50
2005,10,60
2005,10,70
2005,20,90
2006,10,10
2006,30,40
2006,10,50
2006,20,70
2006,10,80

[ray.wurlod]

Create two stage variables to detect change in year, and one to accumulate the running total. Initialize all to zero. Ensure that your data are sorted by year for this to make any sense.

Code: Select all

svYearChange    In.Year <> svPrevYear
svPrevYear      In.Year
svRunningTotal  If svYearChange Then In.F1 Else In.F1 + svRunningTotal

[laknar]

Sort The Year by using sort stage. And in Transformer Stage define

Svyear(Initial Variable '0')
--------------------

Code: Select all

YearColumn<>Svyear Then 1 Else SvYear+1 

SvF1(Initial Variable '0')
--------------------

Code: Select all

Svyear=1 Then F1 Else F1 + SvF1

i haven't tested the code.

[ray.wurlod]

For non-premium readers, I would like to point out that laknar's post is quite unlike mine. I have tested mine, and it works.

[Dsnew]

Ray,

Thank you very much, It solved my problem.
You are the man!

[kiran259]

Could you post your solution?

Thanks
Kiran