Conditional cumulation of values between two differently dimensioned cubes
Posted: Fri Sep 22, 2017 1:27 pm
First of all, thanks to all of you for your great work! I've been working with TM1 for three months now and this forum has become a valuable source for answering my (admittedly often rather basic and even naïve) questions and made me start to understand the concepts of TM1 (at least that's what I think ). However, I've just encountered a problem I haven't found a satisfying solution so far. I hope you can help me and/or give some tips or hints. So, here it is:
I have two cubes, 'accountParameters' and 'accountGroupParameters', which solely exist to store some parameters (obviously) in a calculation model.
The dimensions are as follows:
accountParameters: company, year, month, version, currency, accountParams, accounts
accountGroupParameters: company, year, month, version, currency, accountGroupParams, accountGroups
The cubes share the first five dimensions, the other two, handling either the accounts themselves or their respective groups, naturally differ from each other.
The 'accounts' dimension contains 102 elements.
The only elements in the 'accountParams' dimension that are interesting for this problem are 'Value' and 'AccountGroup'.
The 'accountGroups' dimension corresponds with the entries in the 'accounts' cube, i.e. it contains the elements 'AccountGroup01', … 'AccountGroup20'. Furthermore, there's a text attribute that stores the account group's clear name called 'GroupName'.
Once again, the only element of the 'accountGroupParams' that matters right now, is 'CumulatedValue'.
The idea is to cumulate the values from all accounts belonging to an account group in the 'accountGroupParameters' cube.
My current rule on the 'accountGroupParameters' cube states:
Of course, this gets the work done, but is an awful mess to maintain being a conditional sum of 102 hard coded elements. Come the day someone changes the 'accounts' dimension, it gets quite hard to do the proper changes in the code. Is there any other way to accomplish the same result with a more elegant rule? A TI would get the job done fast and nicely, but I don't want to force users to execute a TI for each minor change in the 'accountParameters' cube. Inserting additional consolidations by account group doesn't work either, since the 'accounts' dimension is used by a bunch of cubes, where another bunch of TIs run, that fail if the dimension structure is altered (something I had to learn the hard way…).
If there's additional info you need or something is unclear (I'm not that good at explaining stuff...), please ask.
I'm looking forward to your replies!
Cheers,
Jan
I have two cubes, 'accountParameters' and 'accountGroupParameters', which solely exist to store some parameters (obviously) in a calculation model.
The dimensions are as follows:
accountParameters: company, year, month, version, currency, accountParams, accounts
accountGroupParameters: company, year, month, version, currency, accountGroupParams, accountGroups
The cubes share the first five dimensions, the other two, handling either the accounts themselves or their respective groups, naturally differ from each other.
The 'accounts' dimension contains 102 elements.
The only elements in the 'accountParams' dimension that are interesting for this problem are 'Value' and 'AccountGroup'.
The 'accountGroups' dimension corresponds with the entries in the 'accounts' cube, i.e. it contains the elements 'AccountGroup01', … 'AccountGroup20'. Furthermore, there's a text attribute that stores the account group's clear name called 'GroupName'.
Once again, the only element of the 'accountGroupParams' that matters right now, is 'CumulatedValue'.
The idea is to cumulate the values from all accounts belonging to an account group in the 'accountGroupParameters' cube.
My current rule on the 'accountGroupParameters' cube states:
Code: Select all
['CumulatedValue'] = N: If(
DB( 'accountParameters', !company, !year, !month, !version, !currency, 'AccountGroup', 'Account001' ) @= ATTRS( 'accountGroups', !accountGroups, 'GroupName' ),
DB( 'accountParameters', !company, !year, !month, !version, !currency, 'Value', 'Account001' ),
0
) +
… +
If(
DB( 'accounts', !company, !year, !month, !version, !currency, 'AccountGroup', 'Account102‘ ) @= ATTRS( 'accountGroups', !accountGroups, 'AccountName' ),
DB( 'accounts', !company, !year, !month, !version, !currency, 'Value', 'Account102' ),
0
);
If there's additional info you need or something is unclear (I'm not that good at explaining stuff...), please ask.
I'm looking forward to your replies!
Cheers,
Jan