Calculate the diff. of balance of particular account

[Former Member]

hi

I have the following info.

1. Account: PL100; Month: April2010; Balance=10000;

2. Account: PL100; Month: may2010; Balance=12000;

if want the difference of the two months:

i.e. now the result is 2000;

12000-10000 =2000

subtracting May Balance with April Balance

So this means 2000 is the transaction occured in the month of May 2010;

What is the best approach for this?

One is script Logic:

I need to select the data for Account=PL100 & respective months and do subtract >

If i use script logic ; i need to save tat difference (For that should i create one account & Save)?

or can we do this on fly while displaying report?

Thanks

Srinath

[Former Member]

Hi,

As other experts have indicated, there are various ways of doing this.

1. Use of script logic: You will require an additional account, and the result will be stored in the database. This might increase the size of the database, eventually. If you dont want to store the result, dont use this option.

2. Use of dimension formula: You will require an additional account for this, as well. However, the result will not be stored in the database, and the calculation will be on fly.

3. Use of excel formulas in the template: This can be done if you want to show the difference in only one or few records. If you are using excel formulas, and if you want to display the result in multiple reports, you need to write the same formula in all the reports. You can use normal excel function to calculate the different. You can combine this with AfterRange to keep it dynamic.

Hope this helps.

[krishna_priya1]

There are several ways to achieve this .

1) Simple way is to retrieve these 2 accounts in two different cells and calculate difference in excel .

2) To calculate on the fly ,dimension logic can be used .But it requires an another account , for which ,in the Formula property dimension logic can be placed.

3) As you have mentioned Script logic is another way and this requires creation of new account.

4)EVGET formula can also be used. Place this formula in one cell EVGET(PL100,MAY2010)-EVGET(PL100,APR2010) , and refresh report .

Hope this helps.

[Former Member]

hi pkrishnas

thanks for ur reply.

I am using BPC 10 for this requirement, still can we use EVGET?

Pl. confirm

Also u said to keep the formula

in which row i need to keep;

as we do not know how many rows will retrieve becoz day by day no of accounts changes

and i need to display at certain row will not be same every time? this was one concern to use cell.....

srinath

[former_member190501]

Hi,

All Ev Functions support in BPC 10 from SP04 onwards.

Thanks,

Raju

[krishna_priya1]

Rowkeyrange can be divided into two 1) PL100 2) dynamic memberset ,with one row in between ,which is not included in

Then difference of balance can be calculated in that row. For example , RowkeyRange =EVRNG(B2:B4,B6:B10) . In ExpansionRange , Memberset PL100| BAS(someaccount) (I is pipe) , which would create blank row at ROW no 5 . In this blank row , excel calculation can be placed .

Hope this helps.