formula for element property in hierarchy

Post Reply
mnasra
Posts: 68
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: TM1
Version: 9.5
Excel Version: EXCEL 2010

formula for element property in hierarchy

Post by mnasra » Mon Feb 05, 2018 5:48 pm

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: 1673
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: formula for element property in hierarchy

Post by Wim Gielis » Mon Feb 05, 2018 7:51 pm

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

mnasra
Posts: 68
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: TM1
Version: 9.5
Excel Version: EXCEL 2010

Re: formula for element property in hierarchy

Post by mnasra » Fri Feb 09, 2018 8:07 pm

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: 1673
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: formula for element property in hierarchy

Post by Wim Gielis » Sat Feb 10, 2018 8:41 pm

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

jrizk
Posts: 41
Joined: Thu Nov 19, 2009 10:38 pm
OLAP Product: Tm1
Version: 9.4
Excel Version: 2007

Re: formula for element property in hierarchy

Post by jrizk » Mon Feb 12, 2018 1:43 am

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