topic Re: Format Number with apostrophe in QlikView

Format Number with apostrophe

Tue, 21 Apr 2015 12:23:18 GMT

Hi all,

I would like to format a number in an expression. The format should look like this: 999'999.00

I've tried several things, but I cant get the result I whish.

if i use; =num(amount,'#''##0.00','.',chr(39)) the result looks like this: 99'9'999.00

when replacing the 2 apostrophe with a comma =num(amount,'#,##0.00','.',chr(39)) the result looks like this: 999999,# #0.00

Can someone tell me how to format this number correctly?

Thanks

Ilonka

Re: Format Number with apostrophe

fvelascog72 — Tue, 21 Apr 2015 15:20:13 GMT

In Properties, Tab Number choose Fixed to 2 decimals and change Decimal Separator with . and Thousand Separator with '.

Or perhaps with this:

Replace(Replace(num(Number,'#.##0,00'),'.',chr(39)),',','.')

Re: Format Number with apostrophe

Tue, 21 Apr 2015 15:57:56 GMT

Thanks for the suggestion.

In the Tab Number I cant see any dimension. (my number is a Dimension).

With Replace(Replace(num(Number,'#.##0,00'),'.',chr(39)),',','.') I get 99999999'900.00 (original number is 9'999'999.99)

Re: Format Number with apostrophe

anlonghi2 — Tue, 21 Apr 2015 16:07:24 GMT

Hi Ilonka,

try with this little modified Ferico's solution:

replace(num(sum([Material Code]),'###,##0.00'),',',chr(39))

Best regards

Re: Format Number with apostrophe

NickHoff — Tue, 21 Apr 2015 16:18:27 GMT

= num(($vTestNum), '#''##0', '.', chr(39))

Re: Format Number with apostrophe

anbu1984 — Tue, 21 Apr 2015 16:35:38 GMT

=Replace(num(9999999,'#,##0.00'),',',chr(39))

Re: Format Number with apostrophe

NickHoff — Tue, 21 Apr 2015 16:53:29 GMT

There isn't a need to use replace, my solution works just replace the variable with your field.

Re: Format Number with apostrophe

Wed, 22 Apr 2015 06:31:23 GMT

still get as result 999'9'999

Is it possible that some system region settings are messing up the format?

Re: Format Number with apostrophe

PrashantSangle — Wed, 22 Apr 2015 06:40:42 GMT

Hi,

Post your sample apps.

Regards

Re: Format Number with apostrophe

robert_mika — Wed, 22 Apr 2015 06:51:44 GMT

Replace 999999 with your dimension

=TextBetween(num(999999,'#,##0.00'),'',',')&Chr(39)&mid(num(999999,'#,##0.00'),FindOneOf(num(999999,'#,##0.00'),',')+1,100)

You can replace the 100 with any number or len(num(999999,'#,##0.00'))

Re: Format Number with apostrophe

jonathandienst — Wed, 22 Apr 2015 06:56:50 GMT

I agree with Nick Hoff

=Num(999999999.99, '#''##0.00', '.', chr(39)) --> 999'999'999.99

Can you show use the exact expression you are using that is giving the wrong result. I don't think its system settings as the 2nd and 3rd parameters are overriding the system settings.

Note that the apostrophes in the format string is a pair of single apostrophes, not a double apostrophe.

HTH

Jonathan

Re: Format Number with apostrophe

Wed, 22 Apr 2015 08:29:02 GMT

Thanks for all the suggestions. Unfortunately none of them gives the correct result. (I used 2 single apostrophes, not double). The picture below shows the different results I've got.

What is interesting, if I format a number in the 'number' tab with 'money' I get the same result as with =Num(999999999.99, '#''##0.00', '.', chr(39)). Only when I remove one of the 2 single apostrophes in the format, I get the correct result. (which made me believe that some system settings may mess up the result). I'll go for the comma as thousands-sep. for the moment.

kind regards

Re: Format Number with apostrophe

robert_mika — Wed, 22 Apr 2015 09:03:38 GMT

Could you attached your app or sample of your data?

Re: Format Number with apostrophe

PrashantSangle — Wed, 22 Apr 2015 09:28:41 GMT

Hi,

What is your

SET ThousandSep=',';

in script.

Regards

Re: Format Number with apostrophe

Wed, 22 Apr 2015 11:15:12 GMT

SET ThousandSep="'"; (double quote, single quote, double quote)

Re: Format Number with apostrophe

PrashantSangle — Wed, 22 Apr 2015 11:56:13 GMT

Hi,

Modify as i suggested in my earlier post then reload.

and then use

=replace(num(999999,'###,##0.00'),',',chr(39))

text object

Regards

Re: Format Number with apostrophe

Wed, 22 Apr 2015 12:07:47 GMT

wow! It's working now!

Many thanks and kind regards.