Formating issues in Qlik

[draghici1109]

Hello,

a hardnecking issue I am "fighting" since a few days: I just want to format numbers like this:

+12345,67 or -23456,78

to following format:

xxxxx.00 sign // meaning: no thousandchar / the decimalchar is the point (.) and at the end of the number will be either be displayed a white space, in case the number is positiv, or a minus '-' in case the number is negative

Example:

+12345,67 should be represented like 12345.67 (white space)

and 

- 23456,78 should be displayed like 234567.78-

Various trials using num() failed. 

[vincent_ardiet_]

Something like this:
num(12345.67,'###0.00 ;###0.00-')
num(-12345.67,'###0.00 ;###0.00-')

[Javizh · Accepted solution]

Hello @draghici1109 

It deppends on what default thousand/decimal separation you have.

If you're default thousand separation is the comma: 

• num('- 23456,78','#.## ;#.##-','.',',')
• 

If you're default thousand separation is the dot, you have to tell qlik the input format first, with num#: 

• num(num#('- 23456,78','#,## ;-#,##',',','.'),'#.## ;#.##-','.',',')
• 

Hope its helpful.

Best regards.

[draghici1109]

Thank you for your reaction! THis formating of positive and negative numbers is not documented in help.

However, your solution did not work: I get following results:

original: 400520,24 --> formated: 40.05.20

[vincent_ardiet_]

Yes, I've missed the part with decimal separators:
num(12345.67,'#.##0,00 ;###0.00-' ,',' ,'.')

[draghici1109]

Thank you! This was the solution!