Help with improving a rule

[Chaos]

Hi,

A few months ago I wrote a set of rules which temporarily achieved what I needed but they were long winded and inefficient. I'm looking for some help in tidying them up.

Scenario is balance sheet set up, pulling in opening balances from one account to another. Below is an example of the code pulling the period 13 forecast (Q3) balances of eg account "900_ITE" into the period 1 budget (BU) account "900_ITE Opening Balance". In order to get this to work I have had to include a line for every account code.

Is there a smarter way to code this so that all the accounts can fall into the same rule?

Code: Select all

['BU' , '900_ITE Opening Balance'] =  N:  IF (  SUBST (!Period, 5,2)  @='01',
			 DB('X_AX Cost Reporting',!Currency,'Q3',  'YTD' | SUBST (!Period , 1, 3) | '013'   , !X_F8 Cost Centres ,'900_ITE',!X_F8 Value Measure)
			,0);

['BU' , '900_MVC Opening Balance'] =  N:  IF (  SUBST (!Period, 5,2)  @='01',
			 DB('X_AX Cost Reporting',!Currency,'Q3',  'YTD' | SUBST (!Period , 1, 3) | '013'   , !X_F8 Cost Centres ,'900_MVC',!X_F8 Value Measure)
			,0);

Thanks for any advice offered

Andrea

[orlando]

Hi,

i would recommend an own dimension for opening balance, movement and closing balance.
but i think you can't rebuild your cube - so here is a quick idea.
every opening balance account gets two attributes
IsOpenBalance and AccountForOpenBalance
isOpenbalance is numeric with a 1
second is the account where you get the figures form.

and then (very quick and extremly dirty):
['BU'] = N: IF ( SUBST (!Period, 5,2) @='01' & ATTRN(ACCOUNTDIM, !ACCOUNT, 'isOpenBalance'=1,
DB('X_AX Cost Reporting',!Currency,'Q3', 'YTD' | SUBST (!Period , 1, 3) | '013' , !X_F8 Cost Centres ,ATTRS(ACCOUNTDIM, ACCOUNT, 'AccountForOpenBalance') ,!X_F8 Value Measure)
,0);

Best regards,
orlando

[Chaos]

Thanks Orlando, that looks like a good solution. I will give it a go.

[Chaos]

Hi Orlando,

I'm still working on getting the formula to work but this concept made me think about other areas I could use it.

If I expended the use I am thinking about building a new cube from fresh to clean up the data.

You mentioned another alternative to this using Dimensions. What did you mean?

Thanks

Andrea

[orlando]

You mentioned another alternative to this using Dimensions. What did you mean?

Andrea

Hi Andrea,

something like that:

I would delete the "opening balance" elements from the account dimension and create a separate dimension for it.
This has the advantage that the account dimension becomes smaller. In the new dimension I would then have the elements "opening balance", "variation", "closing balance". Closing balance is then the sum of opening and variation.
So you can determine the opening and closing balances for each element of the account.
Using an attribute in the monthly dimension, you can then control from which month the closing balance is to be taken over.

Hope this (very) short description gives you an idea.

Best regards
orlando