cancel
Showing results for 
Search instead for 
Did you mean: 

Need help on Calculating Balance & Turnover - SAP BODS

Former Member
0 Kudos

Hi,

I have a table with Date,Monetory_Amount etc.. as columns and Another table (JRNL_Header) with columns Accounting_Period, Fiscal Year etc..

I need to calculate balance and Turnover from source data and load into target.

I asked my lead for a clue and got reply as below.

We have to get Year-To-Date balance for Income statement and Balance sheet accounts. You need to identify "Period Zero Record" from JRNL_Header table and aggregate all periodic activity.

For e.g. if you are planning to get data for Period 201409 then you need to first identify period zero balance and then add periodic activity for Period 1 to Period 9.

Period 9 Ending Balance = Sum(Period 0 balance) + sum(Period 1 activity) + sum(Period 2 activity) + sum(Period 3 activity) + .....................+ sum(Period 9 activity)


I created query transform for the balance field and mapping SUM(MONETORY_AMOUNT). I am joining the above two tables And in where clause, FiscalYear=2014, Accounting_Period>=0 and Accounting_Period<=09.


So from the above query transform, I am calculating SUM of Monetory_Amount for the year 2014 and for the Accounting periods 0 to 9.


But i was told that i have to use custom function for this. I am not very familiar with custom functions.


Please help me how to achieve this.

Accepted Solutions (0)

Answers (2)

Answers (2)

former_member186897
Contributor
0 Kudos

Can you share some sample records (Input) and how you want in the output. This will give more clarify as how exactly you want to calcluate.

Former Member
0 Kudos

Hi,

So you have to calculate sum(monetary amount) for accounting periods 0 to 9 correct?

Use a case statement and split the incoming records into 9 different output for each accounting period.

Then calculate the sum(monetary amount) for each of the 9 different accounting period and use a merge transform to do Union all.

Does that help?

Arun