TM1 Forum

Hello,

I would like to know if there is a way to activate calculation on the fly with hierarchical dimension.

For example I would like the consolidated element be calculated according to the filtered element from a subset.

Example :
For the moment I have :
A = 10
B = 10
C = 10
Total = 30
And If I use a subset with only A and B I have :
A = 10
B = 10
Total = 30 which is wrong in the presentation.

Is there a way to have Total = 20 ?

Thanks for your help.

Jonsulli wrote:Hello,

I would like to know if there is a way to activate calculation on the fly with hierarchical dimension. For example I would like the consolidated element be calculated according to the filtered element from a subset.

The short answer is no. A subset is merely a collection of elements from a dimension. Nothing more, nothing less. A subset cannot be used to calculate anything inside TM1. You could create the calculation you want as part of an active form report by placing the SUM formula just below the active form range and using the subset to build the rows of the report. The only other way to do what you want would be to create alternate rollups that add up only the items you want but that is not really "on the fly" unless you built the alternate rollups via a TI process that a user could run themselves.

Thanks Tomok,

The problem is the roll up is all the chart of accounts and I need all the accounts level so i dont think TI could help here.

Did you have a look at User Defined Consolidations?
It will not dynamically change the value of your Total element, but with this technique you can create custom rollups on the fly.

Michel

Yep, but I need to keep all the Chart of accout roll up in all the reports, i think it's not posible to keep al the chart of account roll up with user defined consolidation.

I didn't know about the subset defined consolidations myself. Can't an admin create one for general public use?

Sorry Jim, I did not have try this for the moment, now i wonder if Skipcheck and Feeders could be a way create calculation hierarchy on the fly...

Jonsulli wrote:now i wonder if Skipcheck and Feeders could be a way create calculation hierarchy on the fly...

Actually, I heard Skipcheck and Feeders might be used to solve world hunger....

Skipcheck and feeders are used to improve the performance of a cube by cutting down the number of cells that need to be calculated. It is not functionality in the sense of being able to do something new with the numbers.

Maybe you mean you can do something with rules.

tomok wrote:I heard Skipcheck and Feeders might be used to solve world hunger....

I heard world peace.....

Well I mean, may be we can use skipcheck and feeders to skip the accounts without a specific attribute.
For example we feed only the marketing accounts then the Total will calculate on the fly only the Marketing accounts and not the other accounts.

That's just an idea.

...somebody told me Skipcheck and Feeders killed Osama B...

Jonsulli wrote:Well I mean, may be we can use skipcheck and feeders to skip the accounts without a specific attribute.
For example we feed only the marketing accounts then the Total will calculate on the fly only the Marketing accounts and not the other accounts.

That's just an idea.

...somebody told me Skipcheck and Feeders killed Osama B...

Just to concentrate on what feeders can do ....
In theory it would be possible to do this "sub-total on the fly" once off via deliberately underfeeding a rule that was of the form ['B'] = N: ['A']

However it's not a viable solution, the reason I have stressed once off is that once a cell is fed it stays fed so if you then change the feeder to feed another subset of cells then you will have the result of last feed + this feed which isn't the desired result, and now with persistent feeders the situation of accumulating overfeeds over time is more difficult to manage than previously.

Using the user defined consolidation feature to insert a subset as a defacto consolidation is the closest I think you can get.

A simple solution for you may be have multiple dimension hierachies. You can have a marketing hierachy that shows

A= 10
C = 10
Total Marketing = A+C = 20

This can be a seperate hierachy to your example A + B + C = Total 30

This can be done via TI if you want it produced automatically,
1: Flag the elements with an attribute that identifies then to get stripped out of the alternate hierachies,
2: Copy the main hierachy (with diferent C element names) check if C element then add something to the name "Marketing" Total
3: Strip out all flagged accounts under C elements "Marketing"

This should produce you some alternate hierachies

Jonsulli wrote:Well I mean, may be we can use skipcheck and feeders to skip the accounts without a specific attribute.
For example we feed only the marketing accounts then the Total will calculate on the fly only the Marketing accounts and not the other accounts.

That's just an idea.

...somebody told me Skipcheck and Feeders killed Osama B...

Not one to start conspiracy theories but I seen feeders hide behind grassy knolls

Steve

jydell wrote: 2: Copy the main hierachy (with diferent C element names) check if C element then add something to the name "Marketing" Total
3: Strip out all flagged accounts under C elements "Marketing"

This should produce you some alternate hierachies

Sorry I don't understand 2 and 3.

I may have over complicated things.

Simpler solution would be to have two hierachies within you dimension structure one for "marketing" one for "all". Use your existing method (either TI or dimension speadsheet etc) you use for the "all" hierachy to create another one for marketing with different C element names and remove (delete) the N elements from under this consolidation as per your requirements. (this solution should build on your existing processes without adding complexity)

The option of using TI may be overly complicated for the solution you are after as it would use a mix of TI code including (for the delete step after you have created an alternate hierachy)
ATTRS() - to get the element attribute (base your "if" formula around this)
elpar - To determine for the alternate hierachy n element parents name (assuming the N elements have only 2 parents one under "all" and one under "Marketing", this may not suit your data structure)
DimensionElementComponentDelete - to delete the element from the "marketing" hierachy based on the above elpar function