topic Re: num() function formatting in QlikView

num() function formatting

surajap123 — Wed, 12 Mar 2014 15:13:12 GMT

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-

while formatting the number in num() function.

Regards,

Suraj

Re: num() function formatting

Wed, 12 Mar 2014 15:17:47 GMT

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

Re: num() function formatting

maxgro — Wed, 12 Mar 2014 15:18:29 GMT

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(.....)

Re: num() function formatting

MK_QSL — Wed, 12 Mar 2014 15:20:32 GMT

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....!!

Re: num() function formatting

Anonymous — Wed, 12 Mar 2014 15:24:48 GMT

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

Re: num() function formatting

PrashantSangle — Wed, 12 Mar 2014 15:28:12 GMT

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

Re: num() function formatting

surajap123 — Thu, 13 Mar 2014 09:33:16 GMT

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

Re: num() function formatting

jagan — Thu, 13 Mar 2014 09:53:01 GMT

Hi,

You have to use

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

Give comma (,) after 3 digit from right.

Regards,

Jagan.

Re: num() function formatting

Thu, 13 Mar 2014 09:55:06 GMT

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!

Re: num() function formatting

PradeepReddy — Thu, 13 Mar 2014 10:10:12 GMT

Hi Suraj,

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

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

Thanks, Pradeep

Re: num() function formatting

PradeepReddy — Thu, 13 Mar 2014 10:13:27 GMT

Hi jagan,

If I am not wrong,

the above expression will give the result as 3525,000.

the expression for the proper format is.. num(3525,'#,##0','.',',')

Thanks, Pradeep

Re: num() function formatting

KD_ — Sat, 02 Dec 2023 05:40:55 GMT

Thank you for the explanation. Can you or someone explain in layman terms what this part '.',',' is doing. Thank you

Re: num() function formatting

MarcoWedel — Sun, 03 Dec 2023 17:02:38 GMT

https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/Scripting/FormattingFunctions/Num.htm#:~:text=script%20are%20used.-,dec_sep,ThousandSep%20that%20is%20set%20in%20the%20data%20load%20script%20is%20used.,-Example%3A%20%C2%A0