Numeric formatting help

Post Reply
luizg2019
Posts: 41
Joined: Thu Sep 12, 2019 11:02 pm
OLAP Product: TM1 - PAX-PAW-Perspectiv-Arc
Version: PA 2.0.9 - PAW 2.0.73
Excel Version: office 2016

Numeric formatting help

Post by luizg2019 »

Hello,

Can anyone explain me this formatting

Code: Select all

b:#,##0.00;(#,##0.00)♀CO|2|Y
I don't understand the last part "♀CO|2|Y", what does it mean? I looked up on IBM's official documentation but couldn't find anything about this last part...

Thank you!!
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Numeric formatting help

Post by gtonkin »

The closest I can find is this reference.
Basically the b: is for built in as far as I know and formats starting c: are custom.
The next bit is the mask i.e. show thousand separators as commas (or per your regional settings).
The bit after the non-ascii symbol probably relates to the above article i.e. Comma, 2 decimals (looks to be API and deprecated)

If you play around with the formatting, you will see the different codes based on your selection.

There may be "older" folk who have access to a developer/reference guide where this is explained better.

Also, look at Wim's page here.
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Numeric formatting help

Post by ascheevel »

In Luizg's example, the dialog helper string can be decoded as follows:

CO = all currency/comma formats will have this. I'm guessing "C" is for Currency and "O" is for cOmma
2 = number of decimals
Y = yes, show negative values in parentheses
luizg2019
Posts: 41
Joined: Thu Sep 12, 2019 11:02 pm
OLAP Product: TM1 - PAX-PAW-Perspectiv-Arc
Version: PA 2.0.9 - PAW 2.0.73
Excel Version: office 2016

Re: Numeric formatting help

Post by luizg2019 »

ascheevel wrote: Mon Oct 21, 2019 7:33 pm In Luizg's example, the dialog helper string can be decoded as follows:

CO = all currency/comma formats will have this. I'm guessing "C" is for Currency and "O" is for cOmma
2 = number of decimals
Y = yes, show negative values in parentheses
Oh I had no idea, Thank you!

The problem I'm having is that I want users to enter positive value in a cell that has a -1 weight but just for the sake of the user's understanding I want it to show as a negative value, if I use "b:(#,##0.00);#,##0.00♀CO|2|Y" in TM1 Architect it works fine, the positive value entered in that cell is showed between parenthesis but when I view it on P.A.Workspace, it shows as a positive value without the parenthesis... If I remove the last part "♀CO|2|Y"it works fine, the positive values entered are displayed between parenthesis on both PAW and Architect... :?

I'm guessing "b:(#,##0.00);#,##0.00" has the same effect as "b:(#,##0.00);#,##0.00♀CO|2|Y" right?
gtonkin wrote: Mon Oct 21, 2019 7:27 pm The closest I can find is this reference.
Basically the b: is for built in as far as I know and formats starting c: are custom.
The next bit is the mask i.e. show thousand separators as commas (or per your regional settings).
The bit after the non-ascii symbol probably relates to the above article i.e. Comma, 2 decimals (looks to be API and deprecated)

If you play around with the formatting, you will see the different codes based on your selection.

There may be "older" folk who have access to a developer/reference guide where this is explained better.

Also, look at Wim's page here.
Thank you, I'm gonna read Wim's page.
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Numeric formatting help

Post by ascheevel »

luizg2019 wrote: Mon Oct 21, 2019 7:46 pm I'm guessing "b:(#,##0.00);#,##0.00" has the same effect as "b:(#,##0.00);#,##0.00♀CO|2|Y" right?
Yeah should. I believe you could also define a custom format with just (#,##0.00);#,##0.00 and forget the rest.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Numeric formatting help

Post by lotsaram »

b: is for "built-in format"
c: is for "custom format"

until you get to the weird encoding characters at the tail of the built-in formats both follow the same convention as Excel number formatting with semi-colon delimiting the formats as follows
positive number format;negative number format;zero format;string format

Out of interest WHY DO YOU WANT TO FORMAT POSITIVE NUMBERS IN BRACKETS?? (This is just plain wrong as number wrapped in brackets is universally recognized as an alternate format for NEGATIVE numbers).

Number formats do need to be individually programmed in each and every user interface separately so don't be too surprised if you do a funky custom format and it doesn't work in all interfaces.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Numeric formatting help

Post by Wim Gielis »

lotsaram wrote: Tue Oct 22, 2019 7:29 amuntil you get to the weird encoding characters at the tail of the built-in formats
It's a Form feed character (ascii code 12) apparently.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply