Decimal Places used in TM1 Calculation

Post Reply
Goatship
Posts: 33
Joined: Tue May 25, 2010 4:15 am
OLAP Product: TM1
Version: 9.5
Excel Version: 2007

Decimal Places used in TM1 Calculation

Post by Goatship » Wed Jul 07, 2010 4:04 am

Dear All,

The calculation in TM1 always takes the rounded 4 decimal places, regardless of their format.
The ROUNDP function can be used only for a specified cell.
How could we set the default on the calculation to incorporate higher # of decimal places ?

Thank you very much.

Image
I'm new to TM1.

Andy Key
MVP
Posts: 341
Joined: Wed May 14, 2008 1:37 pm
OLAP Product: TM1 CX
Version: 2-5 to 10-2-x
Excel Version: 2003 2007 2010 2013
Location: Sydney
Contact:

Re: Decimal Places used in TM1 Calculation

Post by Andy Key » Wed Jul 07, 2010 4:51 am

If you are trying to say that calculations only use 4dp, then you are incorrect. Calculations in TM1 will always use all available decimal places (which, as you have noted, you can control with functions like RoundP).

However, the Trace Calculation window will only display 4dp. As far as I know there isn't a way to change this.

You can prove this to yourself (taking a guess at your calculation) by multiplying 5374368948.2000 by 0.0385 and you will see that you do not get your calculated value of 206945450.7194.

Now multiply it by 0.038506 instead, and you will get that value - to 4dp.
Andy Key

Goatship
Posts: 33
Joined: Tue May 25, 2010 4:15 am
OLAP Product: TM1
Version: 9.5
Excel Version: 2007

Re: Decimal Places used in TM1 Calculation

Post by Goatship » Wed Jul 07, 2010 9:28 am

Thank you very much.
I'm new to TM1.

User avatar
garry cook
MVP
Posts: 196
Joined: Thu May 22, 2008 7:45 am
OLAP Product: TM1
Version: Various
Excel Version: Various

Re: Decimal Places used in TM1 Calculation

Post by garry cook » Wed Jul 07, 2010 12:07 pm

Just a small point of note - the floating point issue can arise if you're dealing with some very large numbers.

eg, we generate our costing / pricing rates against a VERY large number and proportionally spread over a large number of areas.

Because the spread %ages are calc'd, they tend to give 0.1x10E-25 type numbers. Multiply one by the other and TM1 gives up eventually (working on the basis that there's no point in giving any further detail as >15dp* seems a reasonable level of granularity to use in the multiplication). Multiply out a huge number as we do for our total costbase and you find 20k drops through the net due to what is effectively system rounding.

We get round it by multiplying out the % by 1000000 and then dividing the answer back down again after the multiplication has been done to effecitvely move the decimal place further to the right and avoid the floating point issue. System knows what the number is, it in essence just rounds it internally before the calc.

If anyone's got a better way of changing the FP (maybe some arcane, undocumented config setting?) then I'd love to hear it, would get rid of some unrequired calcs in the system.

* Point of note - no idea if it is 15dp that it stops working, that's just a high lvl observation but think it's somewhere around that level of granularity.

dhims
Posts: 21
Joined: Sun Oct 23, 2011 12:14 pm
OLAP Product: TM1
Version: 951 952 101 10101
Excel Version: 2003 2007 2010

Re: Decimal Places used in TM1 Calculation

Post by dhims » Tue Nov 22, 2011 6:00 am

Hi,

Is there a way by which we can see calculated amount till the last decimal in Trace Calculation window?

User avatar
qml
MVP
Posts: 1074
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.6 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: Decimal Places used in TM1 Calculation

Post by qml » Tue Nov 22, 2011 10:07 am

Not without writing your own GUI.

However, there might be a workaround (depending on what you want to achieve). You can create additional rule-calculated trace measures that would be computed by multiplying your original measure by 1000, 1000000 etc. Then you can run your trace on those new measures - the precision will stay at 4 decimal places, but those places have moved to the right, so you've increased the effective display precision.
Kamil Arendt

Post Reply