Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
LOAD [Cost Center],
Account,
Period,
Budget,
Period * Budget as YTD
FROM
[Tablename];
Thanks Vamshi
Sorry I have should have mentioned that budget value per month can vary so cant do a simple times.
Regards
please explain in details what you want
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;
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
try
Rangsum(Above(Sum(Budget),0, rowno()))