tm1 format to nearest thousand

[dan.kelleher]

I'm looking to format a figure to the nearest thousand:

1,234 displayed as 1,000

As far as I can tell I'll need to create a format dimension and do this via a rule (not ideal)

Before I venure down this path, is there something obvious that I'm missing that means I can do this using custom formatting?

Thanks in advance.

Dan

[rkaif]

You can do this by using MOD() function

Lets say if your number is 1234 then use

MOD(1234, 1000) to get the remainder. Once you have the remainder then you can Subtract it from the original number.

So

Code: Select all

234 - MOD(1234, 1000) will give you 1000
5234 - MOD(5234, 1000) will give you 5000

Hope it helps!

[Martin Ryan]

I don't think there's any way of doing custom formatting like this in TM1, but in my experience it's worth doing this via rules anyway. If you simply format it then some bright spark always notices that the sub total is not the sum of the lines above it. They then declare the report is rubbish and stop looking at the numbers.

Note to avoid this problem you'll need to do the rules at N level, and allow the aggregations to work as normal. You'll also need to introduce a "rounding" account line so that the two figures (the rounded and the unrounded numbers) at the top of the hierarchy can still balance.

Martin