Hi experts,
I want to use the same hierarchy of Balance sheet for 2 sets of data with different signs.
First set of data, the signs are negative and positive which gives me the balance sheet to zero. (all elements properties are +1)
the second set of data, all the amounts are positive, but I need the element property in the hierarchy to subtract the assets from liabilities (which means some elements properties are -1. )
Is it possible to have a formula as element property.
Thanks
formula for element property in hierarchy
-
- Posts: 136
- Joined: Tue Aug 10, 2010 5:40 pm
- OLAP Product: Planning Analytics
- Version: 2.0
- Excel Version: EXCEL 2013
formula for element property in hierarchy
Thanks
Micheline
Micheline
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: formula for element property in hierarchy
Hello,
Weights are always fixed: the weight of an element in (one of his) parent(s) does not change.
What is the relation between bots sets of data ? Can you change the sign when loading the data ?
Or use a rule to convert values such that the natural consolidations with fixed weights can do their job ?
Weights are always fixed: the weight of an element in (one of his) parent(s) does not change.
What is the relation between bots sets of data ? Can you change the sign when loading the data ?
Or use a rule to convert values such that the natural consolidations with fixed weights can do their job ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 136
- Joined: Tue Aug 10, 2010 5:40 pm
- OLAP Product: Planning Analytics
- Version: 2.0
- Excel Version: EXCEL 2013
Re: formula for element property in hierarchy
Thank you very much Wim. I have been doing exactly what you suggested for the last 6 years.
I was wondering if there is something more 'clever' found by the experts.
Micheline
I was wondering if there is something more 'clever' found by the experts.
Micheline
Thanks
Micheline
Micheline
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: formula for element property in hierarchy
You can consider alternative hierarchies in the most recent versions of TM1. But if the 9.5 in your profile is accurate then you could find newer versions (and that’s an understatement).
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 48
- Joined: Thu Nov 19, 2009 10:38 pm
- OLAP Product: Tm1
- Version: 10.2.2
- Excel Version: 2010
Re: formula for element property in hierarchy
Assuming you have a measures dimension and are are initially loading the data as +ve and -ve and all you weightings are 1.
If so, a suggestion would be to set up a second measure(s) and have a rule to push the values into the second measure.
Then set the formatting for the measures respectively (you'll need to in the set/change the formatting in the }ElementAttributes).
eg
Format
Value1 b:#,##0;-#,##0CO|0|Y
Value2 b:#,##0;#,##0CO|0|Y
Note the - sign is missing from the format for Value2 - this will enable all the values to be shown as +ve. It will then be a matter a switching between the 2 measures.
Not sure how well this will work for you as the consolidations will also show a +ve, so it may get confusing if the consolidation are -ve.
If so, a suggestion would be to set up a second measure(s) and have a rule to push the values into the second measure.
Then set the formatting for the measures respectively (you'll need to in the set/change the formatting in the }ElementAttributes).
eg
Format
Value1 b:#,##0;-#,##0CO|0|Y
Value2 b:#,##0;#,##0CO|0|Y
Note the - sign is missing from the format for Value2 - this will enable all the values to be shown as +ve. It will then be a matter a switching between the 2 measures.
Not sure how well this will work for you as the consolidations will also show a +ve, so it may get confusing if the consolidation are -ve.
J.Rizk
Tm1 for everyone
Tm1 for everyone