TM1 Forum

I am trying to develop a rule where the consolidations at all levels of the date dimension displays the last childs value and not the rollup of the value. To complicate this further the consolidation only needs to behave this way when viewing a specific section of the accounts dimension. Here is my initial take at the rule:

['Specific Account','Amount']=C:DB('Cube',ELCOMP('Date',!Date,ELCOMPN('Date',!Date)),!Accounts,!Business Unit,'Amount');

Any help would be appreciated.

Garsdale49 wrote:['Specific Account','Amount']=C:DB('Cube',ELCOMP('Date',!Date,ELCOMPN('Date',!Date)),!Accounts,!Business Unit,'Amount');

This rule will do what you want, but not for a range of accounts. It's only going to work for a single account specified by 'Specific Account'. You will have to have a rule for every single account. The better solution would to be add an attribute to the Account dimension, we'll call it LastValue, and populate it with "Y" if we want the last value, or leave it empty if not. Then the rule would be:

['Amount']=C:IF(ATTRS('Accounts,!Accounts,'LastValue')@='Y',
DB('Cube',ELCOMP('Date',!Date,ELCOMPN('Date',!Date)),!Accounts,!Business Unit,'Amount'),
CONTINUE);

Thanks for the response. I like the idea of the attribute flag and have since added that to my account dim. The issue I face now is when I add the rule as specified the value for the 'Amount' always returns 0 (for elements and consolidations) where the flag is 'Y'. If I comment out the rule I get the expected values. Any idea why that would be?

Would I be correct in guessing that the accounts for which you want the last child of the date dimension are balance sheet nominals, while the accounts for which you don't want this are profit and loss nominals?

I would suggest that you consider using two alternate hierarchies in your time dimension eg

2010-Qtr1_Bal
2010-Jan 0
2010-Feb 0
2010-Mar 1

Only the last element has a weight of 1.

In this way if you expand Qtr1 you will still see the three months that make up the Qtr but only the value from the last will consolidate up into 2010-Qtr1_Bal

You will find that using consolidation rather than rules is a lot faster.

I have VBA and TI processes that build things this way.

Having said that I think it can be dangerous to mix point in time balances and movement over time P&L values in the same cube. I would advise keeping them in different cubes, or converting the balances to movements.

Most General Ledgers I query do represent balances as movements, so that the balance at any point in time is the starting balance plus cumulative movements.

I not sure if this is relevant to your problem, but I hope it helpds.

Regards


Paul Simon

Needs to be the same hierarchy. The end goal would be something like this:

###### Jan09 | Feb09 | Mar09 | Q1/09 | ... | Oct09 | Nov09 | Dec09 | Q4/09 | 2009
Acct (1) 15000 | 20000 | 10000 | 45000 | ... | 32000 | 12000 | 14000 | 58000 | 183000
...
Acct (Y) 10000 | 12000 | 15000 | 15000 | ... | 20000 | 20000 | 25000 | 25000 | 25000

For Acct1 the SUM(Q2+Q3) is the difference of 2009 - SUM (Q4+Q1).
Acct (Y) is where the flag is equal to 'Y'.

Garsdale49 wrote:Any idea why that would be?

I can't guarantee that the specific code I provided will work on any given cube. You didn't provide any background information about the cube(s) structure(s) so I don't know if what you provided is valid to begin with. All I did was take your supposedly "working" rule and show you how to modify it to work with the attribute so you wouldn't have to write a whole bunch of separate rules. If you want help debugging code you'll have to provide all the relevant background information.

Sorry, I wasn't implying there was anything wrong with the rule. It is clearly something in my cube but not sure where to start investigating.

My response was more a question of if this scenario happened with that exact rule what could be the possible reasons for it?

The Acct (1) and Acct (Y) are consolidations in the my Accounts dim, I am leaning towards this being the root cause as to why I am getting the zeros.

Garsdale49 wrote:Sorry, I wasn't implying there was anything wrong with the rule. It is clearly something in my cube but not sure where to start investigating.

My response was more a question of if this scenario happened with that exact rule what could be the possible reasons for it?

The Acct (1) and Acct (Y) are consolidations in the my Accounts dim, I am leaning towards this being the root cause as to why I am getting the zeros.

I don't have time to test this at the moment and it's more in the nature of a wild guess, but if you replace CONTINUE in Tomok's rule with STET does it make a difference?

Same result. I am working with a simple single cube, with 4 dimensions and one measure.

Maybe asking the question in a more generic way would help. What rule would be used to provide the last child of a consolidation for a specific dimension while maintaining the sum for other dimensions?

Based off the discussion is it correct to assume the following rule would work(or should)?

['Measure']=C:DB('CUBE',ELCOMP('Dim1',!Dim1,ELCOMPN('Dim1',!Dim1)),!Dim2,!DimN,'Measure');

Thanks for the help everyone. I ended up taking a different approach. In my time dim I added an attribute for the last value: for consolidations it is the element value of the last child(grandchild), for elements it is its own value. In addition to this I added a second measure element ('Rollup'). Here is the rule that ended up producing the desired functionality:

['Rollup']=C:IF(ATTRS('Accounts',!Accounts,'Flag')@='Y',DB('Cube',ATTRS('Date',!Date,'LastValue'),!Accounts,!Business Unit,'Amount'), DB('Cube',!Date,!Accounts,!Business Unit,'Amount'));

Garsdale49 wrote:Here is the rule that ended up producing the desired functionality:

Congratulations on finding a solution to your problem. I must warn you though that this rule may very well have unintended consequences on the other functionality of the cube. This is because writing a rule with C: tells TM1 to treat all consoldated nodes this way, not just the particular dimension you have intended. This may very well cause you headaches going forward. Sorry to be such a downer.

When you say headaches, do you mean from a performance standpoint or write capability or something else?

Incorrect results in the consolidation.

To follow on from tomok,
['Rollup']=C:IF(ATTRS('Accounts',!Accounts,'Flag')@='Y',DB('Cube',ATTRS('Date',!Date,'LastValue'),!Accounts,!Business Unit,'Amount'), DB('Cube',!Date,!Accounts,!Business Unit,'Amount'));

You can reduce the scope of the number of consolidations the rule applies to by doing this

['Rollup']=C:
If (Ellev ('Accounts', !Accounts)=0 & Ellev ('Business Unit', !Business Unit)=0, #(repeat for any other dims in your cube that are not date)
#Take the closing period position for BS accounts
IF(ATTRS('Accounts',!Accounts,'Flag')@='Y',DB('Cube',ATTRS('Date',!Date,'LastValue'),!Accounts,!Business Unit,'Amount'),
#You ought not to need this IMO
DB('Cube',!Date,!Accounts,!Business Unit,'Amount')),
#Continue or stet as required
stet);

This way the rule only applies to the consolidation of the date dimension.

One other thing, you probably have your dimension order wrong. You want dense dimensions last and Date is almost always a dense dimension, moving it to the last position should reduce your RAM usuage and probably improve your performance.

Cheers,