TM1 Forum

Hello Everybody,

I am modifiying an existing "balance sheet" cube and need to set up rules to display a value in either one chapter or another of the balance depending of it's signage.(For a defined list of accounts)

If net value YTD > 0 then display into Receivable
ElseIf net value YTD > 0 then display into Payable

My time period structure is as following :
Conso "YTD Jan" =
N Level "Wk1" + N Level "Wk2" + N Level "Wk3" + N Level "Wk 4" + N Level "Wk 5" + N Level "Jan Adj"

Conso "YTD Feb" =
N Level "Wk1" + N Level "Wk2" + N Level "Wk3" + N Level "Wk 4" + N Level "Wk 5" + N Level "Jan Adj"
+ N Level "Wk6" + N Level "Wk7" + N Level "Wk8" + N Level "Wk 9" + N Level "Feb Adj"

In my Accounts dimension, I have :

C Balance
C Assets
C Receivables
N "Acct 1 - Rec."

C Liabilities
C Payables
N "Acct 1 - Pay."

And Outside of the main balance hierachie
C Total Balancing Acct
N "Acct 1"

In my rules/feeders,
i tryed to feed and calculate the "YTD Jan", "Acct 1 - Rec." with the net value for "YTD Jan", "Acct 1".

Feeders :
["Acct 1","YTDJan"] => ["Acct 1 - Rec.", "YTD Jan"] ;
["Acct 1","YTDJan"] => ["Acct 1 - Pay.", "YTD Jan"] ;
Rule :
["Acct 1 - Rec.", "YTD Jan"] = C: IF ( ["Acct 1","YTDJan"] >0 , ["Acct 1","YTDJan"] , 0 ) ;
["Acct 1 - Pay.", "YTD Jan"] = C: IF ( ["Acct 1","YTDJan"] <0 , ["Acct 1","YTDJan"] , 0 ) ;

It works fine and the value i am expected is displayed for YTD Jan in front of the right account in receivables.

Problem,
the value is not taken into the further consolidations performed in the Account hierarchie...
I.E : Total Receivable won't take into account the result sent in "Acct 1 - Rec."

I guess it has something to do with the fact that i am calculating at Consolidated level.

Do i have then to manage the calculation througth rules up to the top level of consolidation in my account hierarchie ?
Or do i have the obligation to calculate at N Level in order to have the value taken into account ?


Thanks for any advice or comment.

Kind Regards,

Olivier

Olivier, I have found in practice (quite a lot of practice) that unless the C level rules you want to do are very simple, _or_ they are 'KPI' type rules where you want the same result at N and C, you are better off pushing the data down to N in another cube and working on it there. This seems to come up again and again with balance sheet manipulation (no surprise really). The feeders to get them down to N level can be rather heavy (eg a May movement needs to affect Mat YTD to Dec YTD) but the result is justmore usable. I had to do this recently at a client where they were doing some heavy manipulation of their returns to get them into the parent company's SAP system and the requirement was to split debits and credits on a YTD basis. Before we did this we kept getting niggly little differences. It didn't take long to do and everything started working.
HTH - though I feel sure the above will be challenged

By default TM1 calculates a high level consolidation not by adding the direct children of that consolidation, but of the low level elements, skipping out the consolidations in the middle. Another trick that gives TM1 its speed.

For example if you have a hierarchy made up of World, America, States, Cities, then a view on the consolidated World would skip America and the States and just get the values from Cities. (Cleverly, it doesn't ignore the consolidation weightings in the intermediary steps, but it DOES ignore the calculations in the middle).

So, two options. One is to use the ConsolidateChildren rule. Ech. Uses a ridiculous amount of RAM.

The other is forcing the calculation down to N level somehow.

Martin

Funny, I'm going to have to do this very soon.

The way I've done this before is to handle it in the data load. So if the value is < 0 then send it to the paybale account otherwise send it to the recieveable account. This reduces the complexity in the rules but can make the audit trail of the numbers coming into the system a lttle harder to follow.

Anyway if you have to do it with rules then like David says you are probably going to need to do this work at the N level. If you are sure that the rule is fed correctly then it sounds like you the consolidation you are looking at is going back to the N levels to pick up the total and skipping the ruled C level result. Even if you define a consolidation in a nested way with many sub-levels TM1 will go back to the N level to calculate the result and not consider the C levels in between. You can force the system to consider the consolidations by using ConsolidateChildren but this can have big performance impacts.

If you have space you could do it in the same cube but you will need a spare dimension to do the rule work.

['Values as loaded', 'Acct 1 - Pay.'] => ['Vales as Reported', "Acct 1 - Rec ];
["Acct 1','Values as loaded'] => ["Acct 1 - Pay.", 'Vales as Reported'] ;
Rule :
["Acct 1 - Rec.", 'Vales as Reported'] = N:IF ( ["Acct 1','Values as loaded',] >0 , ["Acct 1','Values as loaded',], 0 ) ;
["Acct 1 - Pay.", 'Vales as Reported'] = N: IF ( ["Acct 1','Values as loaded',] <0 , ["Acct 1','Values as loaded'], 0 ) ;

This has the advantage that you don't need a rule for every period either.

HTH

Thanks guys for your answers.

I am still working on it at the moment...
I am going to a logic where i use differents accounts for opening and calculate the opening balance and other accounts to follow up the monthly level.

I am not sure that it will sort me out completely yet, but i ll post my final structure to have your opinion .)


Kind regards,

Olivier