Decimal precision

Post Reply
vins
Posts: 35
Joined: Wed Jul 31, 2013 4:54 pm
OLAP Product: TM1
Version: 10.1
Excel Version: 2010

Decimal precision

Post by vins »

Hi ,

Environment - Cognos TM1 10.2
I have a rule calculated cell which is calculating to = - 0.0000000000000345

TM1 has limitations with the no of decimal values it takes. Hence, TM1 is storing this value as -0.000000000

As minus zero does not make any sense and while exporting the value. It is treating this value as Zero (0).

Can you please suggest to round up "- 0.0000000000000345" the calculated value instead of showing "-0.000000000".


Thanks,
Vinay
tomok
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: Decimal precision

Post by tomok »

Ever heard of ROUND and ROUNDP?
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
vins
Posts: 35
Joined: Wed Jul 31, 2013 4:54 pm
OLAP Product: TM1
Version: 10.1
Excel Version: 2010

Re: Decimal precision

Post by vins »

Thanks for the reply Tomok...

As i require the value till 9 precision. Hence, Round is not suitable.

ROUNDP function is not working beyond 9 decimal precisions to display or extract the value
ex -
ROUNDP(- 0.0000000000000345,10) = (0.000000000)

Let me know if i am missing any thing.
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: Decimal precision

Post by David Usherwood »

I trust you are not working with accounting data 8-)
Depending on your rule (which you haven't posted) you might get somewhere by scaling up the intermediate results into a range which TM1 can work with - assuming that your results don't go up to the high end of the range as well.
TrevorGoss
Community Contributor
Posts: 217
Joined: Thu Aug 15, 2013 9:05 am
OLAP Product: TM1
Version: 10.2.1.1
Excel Version: 14.0.6129.5000

Re: Decimal precision

Post by TrevorGoss »

Apologies if this is not entirley relevant but..

as far as I am aware, TM1 stores data as the value type of Double...can anyone confirm this?

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

Re: Decimal precision

Post by qml »

TrevorGoss wrote:as far as I am aware, TM1 stores data as the value type of Double...can anyone confirm this?
https://www-304.ibm.com/support/docview ... wg21515228

The below section is relevant to both your question and the OP's question.
TM1 Precision

Technote (FAQ)

Question
What is the decimal precision of TM1?

Answer
TM1 stores data in IEEE-754 Floating Point Double.
The maximum number of digits is 15+ (almost 16, but some 16 digit number will not fit with full precision).
TM1 will clip to 14 digits for display.
By default Architect will display 9 digits of precision after the decimal point. This can be modified by by defining a custom format for the cell. Of course, the same overall 15 digit limit still applies. If one has 10 digits to the left of the decimal point, there will be only 5 digits to the right.
Kamil Arendt
TrevorGoss
Community Contributor
Posts: 217
Joined: Thu Aug 15, 2013 9:05 am
OLAP Product: TM1
Version: 10.2.1.1
Excel Version: 14.0.6129.5000

Re: Decimal precision

Post by TrevorGoss »

Just what I was looking for, thank you.
whitej_d
Community Contributor
Posts: 103
Joined: Mon Sep 05, 2011 11:04 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2010

Re: Decimal precision

Post by whitej_d »

Someone will correct me if I'm wrong, but wouldn't TM1 be able to handle small numbers by using E-20 type stuff. For example 0.0000000000000345 would actually be 3.45E-14, therefore there should be no limit to the size of a number, large or small, only the precision.

In the example given by the OP, I would think it can be solved with formatting - ie use a scientific formatting that outputs 3.45E-14 as most other systems will interpret this correctly.

This is all assumptions and speculation on my part, so I would be interested if anyone can back this up.
whitej_d
Community Contributor
Posts: 103
Joined: Mon Sep 05, 2011 11:04 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2010

Re: Decimal precision

Post by whitej_d »

To provide more on my point, the following rule works, and evaluates ['Gross Revenue'] as 2.

Code: Select all

['Existing Stores Revenue'] = N: 1 / 1E20;
['New Stores revenue'] =  N: ['Existing Stores Revenue'] * 2;
['Gross Revenue'] = N: ['New Stores revenue'] * 1E20;
User avatar
qml
MVP
Posts: 1094
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.9 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: Decimal precision

Post by qml »

whitej_d wrote:there should be no limit to the size of a number, large or small, only the precision.
You are not wrong. Quite the opposite, you are right. The limitation is in how many significant figures of a number TM1 can store and handle. You can do a simple test and type into any numeric cell the following value:
12345678901234567890123456789012345678901234567890 (50 digits)
and TM1 will happily store it, but will display it in CubeViewer as:
12345678901234000000000000000000000000000000000000 (50 digits, but truncated after the 14th significant figure)
and internally it will in fact be stored with a slightly larger precision:
12345678901234560000000000000000000000000000000000 (50 digits, but truncated after the 16th significant figure).
Kamil Arendt
Duncan P
MVP
Posts: 600
Joined: Wed Aug 17, 2011 1:19 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2
Excel Version: 2003 2007
Location: York, UK

Re: Decimal precision

Post by Duncan P »

whitej_d wrote:there should be no limit to the size of a number, large or small, only the precision
TM1 uses IEEE754 double precision for its numbers (explained here http://en.wikipedia.org/wiki/Double-pre ... int_format) and that format does have limits, if you can call them that.

From that page ...

Code: Select all

0000 0000 0000 000116 = 2^(−1022−52) = 2−1074  ≈ 4.9406564584124654 × 10^−324 (Min subnormal positive double)
000f ffff ffff ffff16 = 2^−1022 − 2^(−1022−52) ≈ 2.2250738585072009 × 10^−308 (Max subnormal double)
0010 0000 0000 000016 = 2^−1022 ≈ 2.2250738585072014 × 10^−308 (Min normal positive double)
7fef ffff ffff ffff16 = (1 + (1 − 2^−52)) × 2^1023 ≈ 1.7976931348623157 × 10^308 (Max Double)
vins
Posts: 35
Joined: Wed Jul 31, 2013 4:54 pm
OLAP Product: TM1
Version: 10.1
Excel Version: 2010

Re: Decimal precision

Post by vins »

Thanks all !!

I was able to do a workaround..
Post Reply