Calculation Hierarchy on the fly ?
-
- Posts: 39
- Joined: Tue Jan 04, 2011 3:42 pm
- OLAP Product: TM1
- Version: 9.5
- Excel Version: 2003
Calculation Hierarchy on the fly ?
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.
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.
-
- 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 ?
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.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.
-
- 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 ?
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.
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.
- 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 ?
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
It will not dynamically change the value of your Total element, but with this technique you can create custom rollups on the fly.
Michel
-
- 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 ?
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.
- 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 ?
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
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- 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 ?
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...
-
- 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 ?
Actually, I heard Skipcheck and Feeders might be used to solve world hunger....Jonsulli wrote:now i wonder if Skipcheck and Feeders could be a way create calculation hierarchy on the fly...
- 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 ?
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.
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
Jodi Ryan Family Lawyer
- 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 ?
I heard world peace.....tomok wrote:I heard Skipcheck and Feeders might be used to solve world hunger....
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
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- 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 ?
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...
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...
-
- 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 ?
Just to concentrate on what feeders can do ....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...
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.
-
- 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 ?
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
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
-
- 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 ?
Not one to start conspiracy theories but I seen feeders hide behind grassy knollsJonsulli 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...
Steve
-
- 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 ?
Sorry I don't understand 2 and 3.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
-
- 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 ?
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
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