13 Replies Latest reply: Dec 11, 2017 4:22 AM by Tresesco B

# Format number within expression

Hi,

How can I limit the number of decimals within my expression?

I have an expression in my chart title, which is supposed to show me 22% but I don't know how to format it, so it shows 0.2212341241 or something. I can of course multiply with 100, but I want it to round off (have no decimals, or just one is also okay).

Here is the title of my gauge chart:

='Credit Utilisation (Limit Spent) - '& Sum (Limit-[OTB (excl. oversell)])/Sum (Limit)*100

• ###### Re: Format number within expression

use Num FUNC...

Num( Value,'#,##0 ')

=num(Sum (Limit-[OTB (excl. oversell)])/Sum (Limit),'#,##0')

• ###### Re: Format number within expression

I was looking for this '#,##0'

• ###### Format number within expression

hi,

wrap your expression with the num() function. Look it up in the help.

#### num( expression [ , format-code [ , decimal-sep [ , thousands-sep ] ] ] )

The num function formats the expression numerically according to the string given as format-code. Decimal separator and thousands separator can be set as third and fourth parameters. If the parameters 2-4 are omitted, the number format set in the operating system is used.

for the second parameter - the format code - a tip I was given and which works is to go to the chart->properties->number dialgue and copy preferred format with cut and paste it into your num function. The format goes between simple quotes.

hope this helps.

• ###### Re: Format number within expression

Thank you very much.

Could someone PLEASE HELP ME report the following. Except for what I say below, I cannot send an email to Jason Long, whenever I click send it just tells me to enter the name of the recipient, which it then just ignores again. Please someone forward this to Jason Long or support or anyone who can help me, I cannot do anything on this site!

Hi Jason,

I'm having big problems with the site - All of the dropdown-links at the top of the page (New, Your Stuff, History, Browse) do not work - at the bottom of the screen the path shows javascript:void(0) and nothing happends when I click teh links. The same thing for replying on threads. I can start a new one, but then I can only reply to it by replying directly to the email notification.

Also, whenever I use brackets in a post to show a field with spaces, when I post it it changes the field name to a hyperlink of the discussion! crazy.

Please look into this or tell me what to do. I also cant mark answers as correct anymore, dont know if this is just due to the new site or what.

Thanks,

Gerhard

• ###### Format number within expression

Hi:

These '#,##0' formating tails can be picked up from any Number dialog, so you won't have to remember them or hit the manual. Look at the picture.

• ###### Re: Format number within expression

All of the posts above are correct. However if you want to show the percent sign appended to the number use this formating string: '#,##0%'  (i.e. Num(<Expression or value>,'#,##0%')

• ###### Re: Format number within expression

Just a word of caution, when using the '%' remember that qlikview automatically multiplies the number by a 100. So the actual evaluated value of the expression must be in decimal

• ###### Re: Format number within expression

Hi.

To solve % situation, load your % per one, that means, the real number, not the view.

Otherwise, you need to divide by 100 everytime.

• ###### Re: Format number within expression

Hello -

Use

=num(AVG([Total CTR]), '#.##%')

Best

• ###### Re: Format number within expression

try

=num('Credit Utilisation (Limit Spent) - '& Sum (Limit-[OTB (excl. oversell)])/Sum (Limit)*100),'#,##0.00%') or Properties

• ###### Re: Format number within expression

Guys do you realize that this post is from 2011? They might have already solved the issue.

Just wanted to let you know if you did not already know.

Best,

Sunny

• ###### Re: Format number within expression

Hi Sunny,

Is there a way to round your number up to the nearest million, e.g. 2,893,754,164 so if I write it in million it should be 2,894M not 2,893M because I want to round up. But everywhere I see they suggest to divide the number by 1 million and add M at the last.

Thanks,

Anas

• ###### Re: Format number within expression

Ideally, you should have created a new thread for not so-much related topic help.

You can use Ceil() like:   Ceil( <number> , 1000000 ) &'M'