Summing by Attribute

[lostit]

I realize that this is probably a simple answer for some of you but I'm stuck on this and can't find an answer in the documentation or an existing thread regarding this on the board, so any help that can be provided would be greatly appreciated.

I would like to know how I would go about summing up all of the base level elements of a dimension, based on the value in an attribute.

For example:

I have 30 base level accounts (elements) in my dimension, and half of them are type 1 and half are type 2. The type (1 or 2) is denoted in an attribute entitled "Acct_Type". I then have 2 additional accounts ("total1" & "total2"), one to hold the sum of the type one 1 accounts and the second to hold the sum of the type 2 accounts. What command, in a rule, would I use to populate the sum that is held in accounts "total1" and "total2".

***** Without going into a long explaination on this, using a hierarchy to store these accounts, is not an option here.

Thanks in advance for any help that you may be able to provide.

[LoadzaGrunt]

***** Without going into a long explaination on this, using a hierarchy to store these accounts, is not an option here.

It seems odd that you cannot use a hierarchy. What is the reason ?

Otherwise, your rule could work like this:

Code: Select all

['Total1']=N:['Acct1']+['Acct2']+['Acct3']...['AcctN'];

But that is a very, very poor substitute for the hierarchy, hence my question.

[lostit]

***** Without going into a long explaination on this, using a hierarchy to store these accounts, is not an option here.

It seems odd that you cannot use a hierarchy. What is the reason ?

Otherwise, your rule could work like this:

Code: Select all

['Total1']=N:['Acct1']+['Acct2']+['Acct3']...['AcctN'];

But that is a very, very poor substitute for the hierarchy, hence my question.

I was hoping to avoid a rule that specifies each element because there actually thousands of elements that this applies to (the number 30 was only as an example), and the number of affected elements changes dynamically every month, so the maintenance on this would become its own monster. The other reason that a hierarchy is not preferable is that a rule has to be used because there is a shift in the time dimension thats part of this calc, but does not need to be addressed as I already have that part of the calc solved.

The easiest way to work with this large population is to utilize the attribute table to "flag" my elements properly, a process which is already automated for us. So basically we're at the point where the calc would be complete if there was a way, or a command, that would essentially say "sum up all the base level elements with an attribute of 'Type 1' and place that result into account 'X' ".

Thanks

[LoadzaGrunt]

The other reason that a hierarchy is not preferable is that a rule has to be used because there is a shift in the time dimension thats part of this calc, but does not need to be addressed as I already have that part of the calc solved.

Sounds like you've painted yourself into a corner if the time dimension is preventing you using consolidations in your account dimension. What is the 'shift' in the time dimension ? I appreciate you are happy with your solution but there may be other solutions that are preferable.

The easiest way to work with this large population is to utilize the attribute table to "flag" my elements properly, a process which is already automated for us.

I would suggest that having the consolidation of accounts to totals as an automated task is what you want (if you re-work your time dimension to allow this). By 'automated' do you mean a Turbo Integrator process ?

So basically we're at the point where the calc would be complete if there was a way, or a command, that would essentially say "sum up all the base level elements with an attribute of 'Type 1' and place that result into account 'X' ".

The 'way' to do this - adding up base level elements - is with a consolidation (a hierarchy) ! There isn't a function or command to do what you want.

I think you need to go back to the drawing board.

[Malcolm MacDonnell]

Hi,

You could try adding a new element into your measures dimension and populate it with a rule like:

['new element'] = N: IF (ATTRS(???) @= 'Y', ['old element'], 0);

And then sum up the new element.

Mal

[lotsaram]

I'm with Loadza on this one. The usual way to "sum by attribute" in TM1 (without using fancy MDX) is to use TI to build hierarchies based on attribute flags. Put quite simply hierarchies are how TM1 handles additive and subtractive (and weighted) summation.

If there is a requirement to also handle a transformation on a time dimension this should not preclude using a hierarchy in the account or product or whatever else dimension. You would still be best advised to use an account hierarchy to do the base summing up and then create a new measure and a simple "look up rule" to handle the time dimension transformation.

I think Mal's suggestion will also work for you but your rules will need to also do whatever is is you need to do with regards to time transformation.

[par3]

Hi!

Not sure if this will work and I have not tested it.

Why not try this:

['Total1'] = IF ((Attrs('Accdim',!Acc,'Acctype') = 'Type1'),
['TempAcc'] + ['!Acc'],continue);