Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Thanks for your quick reply Roland,
unfortunately it still doesn't work, result is the same with or without.
regards
Nik
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
Sorry Roland,
There was another mistake, now it works as you suggested !
thank you
Nik
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
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)
Thanks!