topic Issue with number formatting in text box in QlikView

Issue with number formatting in text box

Sun, 21 Nov 2010 16:40:36 GMT

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

Issue with number formatting in text box

Sun, 21 Nov 2010 18:06:06 GMT

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

Issue with number formatting in text box

Sun, 21 Nov 2010 20:07:18 GMT

Thanks for your quick reply Roland,

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

regards

Nik

Issue with number formatting in text box

Sun, 21 Nov 2010 20:46:04 GMT

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 ?

Issue with number formatting in text box

Sun, 21 Nov 2010 21:53:24 GMT

Sorry Roland,

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

thank you

Nik

Issue with number formatting in text box

Tue, 19 Apr 2011 12:05:28 GMT

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

SV:Re: Issue with number formatting in text box

gandalfgray — Tue, 19 Apr 2011 13:10:42 GMT

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)

SV:Re: Issue with number formatting in text box

Tue, 19 Apr 2011 13:32:04 GMT

Thanks!