DSXchange

Posted: **Sun Sep 06, 2009 6:21 pm**

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

Posted: **Sun Sep 06, 2009 8:22 pm**

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

Posted: **Sun Sep 06, 2009 9:23 pm**

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.

Posted: **Sun Sep 06, 2009 9:57 pm**

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.

Posted: **Sun Sep 06, 2009 10:11 pm**

Ray,

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

Posted: **Sun Sep 06, 2009 10:45 pm**

Could you post your solution?

Thanks
Kiran

DSXchange

Cumulative Totals using Stage Variables

Cumulative Totals using Stage Variables