Help with improving a rule

Post Reply
Chaos
Posts: 3
Joined: Wed Apr 29, 2020 12:43 pm
OLAP Product: Perspectives?
Version: Planning analytics 2.0 ?
Excel Version: 365

Help with improving a rule

Post by Chaos » Tue Sep 29, 2020 8:54 am

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

User avatar
orlando
Community Contributor
Posts: 142
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: Help with improving a rule

Post by orlando » Tue Sep 29, 2020 11:30 am

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
Posts: 3
Joined: Wed Apr 29, 2020 12:43 pm
OLAP Product: Perspectives?
Version: Planning analytics 2.0 ?
Excel Version: 365

Re: Help with improving a rule

Post by Chaos » Thu Oct 22, 2020 12:35 pm

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

Chaos
Posts: 3
Joined: Wed Apr 29, 2020 12:43 pm
OLAP Product: Perspectives?
Version: Planning analytics 2.0 ?
Excel Version: 365

Re: Help with improving a rule

Post by Chaos » Fri Oct 23, 2020 12:49 pm

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

User avatar
orlando
Community Contributor
Posts: 142
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: Help with improving a rule

Post by orlando » Mon Oct 26, 2020 9:41 am

Chaos wrote:
Fri Oct 23, 2020 12:49 pm

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

Post Reply