topic Re: Format number within expression in QlikView

Format number within expression

gerhardl — Thu, 09 Jun 2011 06:57:49 GMT

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

Thu, 09 Jun 2011 07:07:39 GMT

use Num FUNC...

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

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

Format number within expression

pat_agen — Thu, 09 Jun 2011 07:08:00 GMT

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

gerhardl — Thu, 09 Jun 2011 07:19:39 GMT

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

christian77 — Thu, 09 Jun 2011 09:55:01 GMT

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

mvaugusto — Thu, 19 Sep 2013 17:19:38 GMT

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

Re: Format number within expression

jduenyas — Thu, 19 Sep 2013 18:21:12 GMT

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

Fri, 10 Jan 2014 13:07:10 GMT

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

christian77 — Fri, 10 Jan 2014 13:21:07 GMT

Hi.

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

45% load as 0.45.

Otherwise, you need to divide by 100 everytime.

Re: Format number within expression

evansabres — Thu, 09 Apr 2015 18:22:26 GMT

Hello -

Use

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

Best

Re: Format number within expression

Anonymous — Thu, 09 Apr 2015 19:31:55 GMT

try

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

Re: Format number within expression

sunny_talwar — Thu, 09 Apr 2015 19:33:51 GMT

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

md_anasabbasi — Mon, 11 Dec 2017 08:22:18 GMT

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

tresB — Mon, 11 Dec 2017 09:20:48 GMT

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

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