Issue with number formatting in text box

[#1]

Hi all,

I have an issue with number formatting. In a pivot table I see my sales data perfectly formatted, where total sales are displayed as 125.418,60 (european thousandschar and decimalschar). When I display the same number in a textbox I get 12541860. I then try to apply the num formatting, but I'm never able to get the right one:

with num(sales, '#.##0,00') I get 12.541.860,00 --- this is how sales are formatted in the pivot
with num(sales, '###.###,##') I get 12.541.860,00 again
with num(sales, '#') I get 1e+007
with num(sales, '########') I get 12541860
with num(sales, '#.#') I get 1,3e+007

I could continue, no way to get the figure to look right !! any clue ? how the hell QLV formatting works ? not much help form the documentation as usual.

Thanks

Nik

[#2]

Hi,

specify the two Seperators explicit:

=num(sum(sales), '#.##0,00', ',', '.')

I am sure in your pivot they are implicit defined below the format definition, right ?

Regards, Roland

[#3]

Thanks for your quick reply Roland,

unfortunately it still doesn't work, result is the same with or without.

regards

Nik

[#4]

Hi Nik,

I am a little confused. What is the correct value of sales in your pivot table:

a) " In a pivot table I see .... where total sales are displayed as 125.418,60"

b) "I get 12.541.860,00 --- this is how sales are formatted in the pivot"

If a) is correct, your unformatted number in the textbox would be wrong : "in a textbox I get 12541860"

if b) is correct, your unformatted number in the textbox would be also correct.

What about a simple example application ?

RR

[#5]

Sorry Roland,

There was another mistake, now it works as you suggested !

thank you

Nik

[#6]

Hello Roland,

this is embarassing, but I tried to apply your suggestion in order to show the thousand separator as follows: #'##0 - but I can´t get it to work as it gives me an error in the expression as soon as a replace the " . " with " ' ".

Could you help me out on this?

Thanks a lot!

Adi

[gandalfgray]

Hi Adi

try this:

=num(sum(Kostnad) ,'#'&chr(39)&'##0','.',chr(39))

you cannot write 'explicitly in the num function, but you can use chr(39) (which is the ' character)

[#8]

Thanks!