Conditional Sum

[Anonymous]

Hi

I need to create new fields giving me YTD figures for Budget.

So say

YTD1     80111.75

YTD2     160223.5     (adding budget for period 1 & 2)

YTD3     240335.25     (adding budget for periods 1,2 & 3)     

Cost Centre	Account	Period	Budget
12345	953211	1	80111.75
12345	953211	2	80111.75
12345	953211	3	80111.75
12345	953211	4	80111.75
12345	953211	5	80111.75
12345	953211	6	80111.75
12345	953211	7	80111.75
12345	953211	8	80111.75
12345	953211	9	80111.75
12345	953211	10	80111.75
12345	953211	11	80111.75
12345	953211	12	80111.75

Regards

[Anonymous]

LOAD [Cost Center],

     Account,

     Period,

     Budget,

     Period * Budget as YTD

FROM

[Tablename];

[Anonymous]

Thanks Vamshi

Sorry I have should have mentioned that budget value per month can vary so cant do a simple times.

Regards

[sujeetsingh]

please explain in details what you want

[Anonymous]

I think the RangeSum() and Peek() functions can help you here. First you need to load your data and then perform a resident load with the RangeSum() function calculating the cumulative YTD value. The Peek() function takes the value of the previous row for the selected field. Finally we drop the original data table.

BaseData:

LOAD [Cost Centre],

          Account,

          Period,

          Budget

From [Data Source]

CalculatedData:

NoConcatenate

LOAD [Cost Centre],

          Account,

          Period,

          Budget,

          RangeSum(Budget, Peek('YTD')) as YTD

RESIDENT BaseData;

DROP TABLE BaseData;

[Anonymous]

Hi Sujeet

I am trying to create a Budget cost centre report which has the following figures

                                                                        Month                                                       YTD

Cost Centre Code     Annual Budget     Bugdet  Spend     Variance          Budget YTD     Spend YTD     Variance YTD

I am pulling the data from an Oracle database which doesnt have a YTD field.  The raw would look something like:

Period     Budget     Cost Centre     Account

1               1000         ******               *******
2               1200

3               1100

4                1300

Spend field would data in same format.

Regards

[israrkhan]

try

Rangsum(Above(Sum(Budget),0, rowno()))