num() function formatting

[surajap123]

Hi,

I always struggle with num() function.

when I use it to format the output. I always get funny output. I have to do multiple trial & error to get the desired output.

I am curious to understand how the num() function behaves. I have gone through the qlikview help section, but still not clear. I dont know what means format-code in the below syntax and when to use decimal and thousand seperator.

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

Also Could you please tell me when to use-

#

0

,

.

while formatting the number in num() function.

Regards,

Suraj

[#2]

num() works as a type definer, where you can type in any Field you have, and it will turn the format of the data into a number (e.g. if you enter a date field, you'll get 41700 instead of 12.04.2012). Example:

num(Field,'####.##','.',"'")

Format-code, decimal-sep and thousands-sep are optional inputs.

Cheers,

Lucas

[maxgro]

I think

format code is the "Format Pattern" you find in "Number" tab of "Chart Properties"

so go to that tab, choose your desired setting and copy the format in your Num(.....)

[MK_QSL]

NUM(SUM(Sales),'#,##0')

Will give you result like 5,456,124

NUM(SUM(Sales),'#,##0.00')

Will give you result like 5,456,124.12

NUM(SUM(Sales)/SUM(Total Sales),'#,##0%')

Will give you result like 46%

NUM(SUM(Sales)/SUM(Total Sales),'#,##0.00%')

Will give you result like 45.67%

UPDATE : Given result is assumption.. only for understanding....!!

[Anonymous]

in the beginning, when I used to get confused with the formats, I used to do it this way and worked just about everytime.

[PrashantSangle]

Hi,

As Reply given by Lucas Num() is type definer

in that

0 - define size of Your Number

example

Num(345,'0000') returns 0345

but ,

# is flexible size

Num(345,'####') returns only 345 does not include preceding 0

[surajap123]

Thanks a lot for all the inputs !!

Suppose if I have to format a number 3525 to 3,525, then How many hashes do I need to put in num function.

@Massimo -  I use the number tab in chart properties, but I want to know the basic structure of num function.

Regards,

Suraj

[jagan · Accepted solution]

Hi,

You have to use

Num('3525 ', '#,##0')

Give comma (,) after 3 digit from right.

Regards,

Jagan.

[#9]

You use decimal-sep and thousands-sep!

If you mean 3,525 as in three-thousand five-hundred twenty-five:

num(3525,####,'.',',')

If you mean 3,525 as in three point five two five:

num(3525,####,',',"'")

Cheers,

Lucas

PS: I guess you can also just go num(3525,'#,###') but then it might falsly recognize the number!

[PradeepReddy]

Hi Suraj,

use the below expression, it will format the number 1234 to 1,234

num(1234,'#,##0','.',',')  

Thanks, Pradeep