formula for element property in hierarchy

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

Post by mnasra »

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
Thanks
Micheline
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

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 ?
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
mnasra
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

Post by mnasra »

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
Thanks
Micheline
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

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
jrizk
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

Post by jrizk »

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.
J.Rizk
Tm1 for everyone
Post Reply