TM1 Forum

High all apparently this can be done in EP and the client wants this same feature in TM1. My scenario is cube A with cell A has the number 1,000,000 and that same cell needs to be formatted to show 1,000. Is this possible even possible to do with an input cell in TM1? Thanks in advance!

Of course its possible, anything is possible.

I'm guessing you havent tried much, or anything at all?

This is a simple number formatting issue, simply set a custom number format #,##0,
This works for the TM1 cube viewer exactly as in Excel for example. The trailing thousand separator is in effect telling the UI to remove the 3 digits or effectively to "display as /1000"

Thus a value of 100,000 will display as 100.
However watch out for data entries as this is formatting only. To enter a value of 100000 the user still needs to enter 100000 or 100k, if they enter 100 they will see 0 or maybe 0.1 depending on the precision of the number format.

I am an advocate of such number formatting for display/reporting but not for data entry as it confuses users and inevitably leads to incorrect entry.

Thanks Lotsaram. No I didn't go down that road because at first glance it appeared to be a calculation and also the syntax of the formatting in this manner is pretty new to me.

This is a really bad idea because it will not round the numbers, just truncate the digits. Unless all your truncated digits all happen to be 0's then this will lead to confusion and consolidation points that do not appear to foot. Better to round the numbers using the ROUND or ROUNDP rule and display those.

Thanks Tomok! I knew something didn't taste right with modifying the display but I couldn't put my finger on it.

Natlandry wrote:Thanks Tomok! I knew something didn't taste right with modifying the display but I couldn't put my finger on it.

I wouldn't be quite so quick to dismiss the display option raised by Lotsaram if I were you. Generally speaking I lean toward that way of doing things too, but Tomok also has a valid point.

Say you have the number 2.449 three times (and that those numbers are indeed accurate to 3 decimal places), followed by the sum of it. Using a display only rounding method to get back to 1 decimal place you'd get 2.4, 2.4, 2.4 with a total of 7.3, when clearly 2.4*3 <> 7.3.

If you take Tomok's suggestion and round the three values instead your total will be 7.2, which looks right to the average PHB who may be adding the values on a calculator.

The problem is that it may look right, but it isn't right. The sum of those numbers is in fact 7.347, which rounds to the 7.3 that you get from the display method. The difference may be material or it may not, depending on the circumstances. But one thing is for sure, the more numbers you have, and the more levels of calculation you put between your end result and the raw data (if you're rounding all along the way), the bigger that difference will be. That is why I don't like physically rounding the numbers but rather using display formatting unless they are the very end values which will be used for final reporting and nothing else, or you are rounding to ensure that the numbers match the realistic precision of the values.

Alan Kirk great picture man. Thanks!