topic Re: how to use num() function in qlikview? in QlikView

how to use num() function in qlikview?

ananyaghosh — Mon, 23 Mar 2015 14:17:23 GMT

Please give examples to me.

Re: how to use num() function in qlikview?

giakoum — Mon, 23 Mar 2015 14:20:19 GMT

Num

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.

Example:

The examples below assume the two following operating system settings:

	Default setting 1	Default setting 2
Number format	# ##0,#	#,##0.#

num( A, '0.0' ) where A=35648.375 returns:

	Setting 1	Setting 2
String	35 648 375	35648.375
Number	35648375	35648.375

num( A, '#,##0.##', '.' , ',' ) where A=35648 returns:

	Setting 1	Setting 2
String	35,648.00	35,648.00
Number	35648	35648

num( pi( ), '0,00' ) returns:

	Setting 1	Setting 2
String	3,14	003
Number	3.141592653	3.141592653

Re: how to use num() function in qlikview?

giakoum — Mon, 23 Mar 2015 14:20:59 GMT

Note the difference : Num formats a number, Num# evaluates a text for example as number :

Num#

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

The num# function evaluates 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 default number format set by script variables or in the operating system is used.

Re: how to use num() function in qlikview?

Mon, 23 Mar 2015 14:21:08 GMT

Is there a specific example you're looking to use the function to fix?

Re: how to use num() function in qlikview?

sunny_talwar — Mon, 23 Mar 2015 14:23:40 GMT

It can be used to assign a specific format to your numeric field in a particular chart. For instance if you data within the chart is like this:

Dim 1	=Sum(Value)
A	12.362563253
B	15.632634521

then you can use Num function to show only two decimal points like this:

Dim1	=Num(Sum(Value), '#,##0.00')
A	12.36
B	15.63

Note: Num won't round the number for you, it will just truncate the number

So the basic idea is: Num helps in formatting a numeric field.

HTH

Best,

Re: how to use num() function in qlikview?

rubenmarin — Mon, 23 Mar 2015 14:23:44 GMT

Hi Sandip, Num is used to format numbers, it takes 2 parameters: number and format, ie:

Num(2000, '#.##0,00 €;-#.##0,00 €')

I think your best choice is go to the numbers tab of any chart and look how the numbers are formatted in the 'Format pattern' text box

Re: how to use num() function in qlikview?

ananyaghosh — Mon, 23 Mar 2015 15:18:07 GMT

What is the functionality of # and 0 here?

Re: how to use num() function in qlikview?

rubenmarin — Mon, 23 Mar 2015 15:32:29 GMT

Hi Sandip:

0 --> Means digit that has to be shown

# --> Digit is showed only if it's necesary

Examples to format the number 1:

Num(1, '00') will show 01 --> this format is useful to create dates, ie. Year(Today()) & Num(Month(Today()), '00')

Num(1, '##') will show 1

Re: how to use num() function in qlikview?

ananyaghosh — Mon, 23 Mar 2015 15:48:28 GMT

can anyone give example of num# function? what is the difference between them?

Re: how to use num() function in qlikview?

rubenmarin — Mon, 23 Mar 2015 16:10:00 GMT

Hi, Num# is used to tell QV how to transform a string to a number.

In example in my country decimal separator is comma, so if i retrieve a number with dot as decimal separator QV can't understand the number and I have to use Num#() to tell the format.

If I use "=Num('10.2')+2" it returns null

If I use "=Num#('10.2', '0.0', '.', ',')+2" it returns "12,2"

Re: how to use num() function in qlikview?

KD_ — Mon, 04 Dec 2023 00:24:37 GMT

Hello, what is the purpose of '.' , ',' here...num( A, '#,##0.##', '.' , ',' ) where A=35648 returns: Thank you

Re: how to use num() function in qlikview?

KD_ — Mon, 04 Dec 2023 00:26:29 GMT

Hi Sunny can you teach me what the purpose of specifying '.' & ',' in the Num() is? Thank you