Calculation Hierarchy on the fly ?

Post Reply
Jonsulli
Posts: 39
Joined: Tue Jan 04, 2011 3:42 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2003

Calculation Hierarchy on the fly ?

Post by Jonsulli »

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.
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Calculation Hierarchy on the fly ?

Post by tomok »

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Jonsulli
Posts: 39
Joined: Tue Jan 04, 2011 3:42 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2003

Re: Calculation Hierarchy on the fly ?

Post by Jonsulli »

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.
User avatar
Michel Zijlema
Site Admin
Posts: 712
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Calculation Hierarchy on the fly ?

Post by Michel Zijlema »

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
Jonsulli
Posts: 39
Joined: Tue Jan 04, 2011 3:42 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2003

Re: Calculation Hierarchy on the fly ?

Post by Jonsulli »

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.
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Calculation Hierarchy on the fly ?

Post by jim wood »

I didn't know about the subset defined consolidations myself. Can't an admin create one for general public use?
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Jonsulli
Posts: 39
Joined: Tue Jan 04, 2011 3:42 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2003

Re: Calculation Hierarchy on the fly ?

Post by Jonsulli »

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...
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Calculation Hierarchy on the fly ?

Post by tomok »

Jonsulli wrote:now i wonder if Skipcheck and Feeders could be a way create calculation hierarchy on the fly...
:roll: Actually, I heard Skipcheck and Feeders might be used to solve world hunger....
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Calculation Hierarchy on the fly ?

Post by Martin Ryan »

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.
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Calculation Hierarchy on the fly ?

Post by jim wood »

tomok wrote:I heard Skipcheck and Feeders might be used to solve world hunger....
I heard world peace.....
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Jonsulli
Posts: 39
Joined: Tue Jan 04, 2011 3:42 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2003

Re: Calculation Hierarchy on the fly ?

Post by Jonsulli »

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... :ugeek:
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Calculation Hierarchy on the fly ?

Post by lotsaram »

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... :ugeek:
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.
jydell
Posts: 32
Joined: Fri Jul 09, 2010 12:12 am
OLAP Product: tm1
Version: TM1 Build Number: 11.8.01300.
Excel Version: Version 2401

Re: Calculation Hierarchy on the fly ?

Post by jydell »

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
stex2727
Posts: 66
Joined: Tue Sep 15, 2009 11:29 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2007

Re: Calculation Hierarchy on the fly ?

Post by stex2727 »

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... :ugeek:
Not one to start conspiracy theories but I seen feeders hide behind grassy knolls

Steve
Jonsulli
Posts: 39
Joined: Tue Jan 04, 2011 3:42 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2003

Re: Calculation Hierarchy on the fly ?

Post by Jonsulli »

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.
jydell
Posts: 32
Joined: Fri Jul 09, 2010 12:12 am
OLAP Product: tm1
Version: TM1 Build Number: 11.8.01300.
Excel Version: Version 2401

Re: Calculation Hierarchy on the fly ?

Post by jydell »

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
Post Reply