Num - script and chart function

Num() formats a number, that is it converts the numeric value of the input to display text using the format specified in the second parameter. If the second parameter is omitted, it uses the decimal and thousand separators set in the data load script. Custom decimal and thousand separator symbols are optional parameters.

Syntax:

Num(number[, format[, dec_sep [, thou_sep]]])

Return data type: dual

The Num function returns a dual value with both the string and the numeric value. The function takes the numeric value of the input expression and generates a string representing the number.

Arguments:

Arguments
Argument	Description
number	The number to be formatted.
format	String specifying how the resulting string is to be formatted. If omitted, the decimal and thousand separators that are set in the data load script are used.
dec_sep	String specifying the decimal number separator. If omitted, the value of the variable DecimalSep that is set in the data load script is used.
thou_sep	String specifying the thousands number separator. If omitted, the value of the variable ThousandSep that is set in the data load script is used.

For more information on number formatting see Conventions for number and time formats.

Example:

The following table shows the results when field A equals 35648.312.

Results
A	Result
Num(A)	35648.312 (depends on environment variables in script)
Num(A, '0.0', '.')	35648.3
Num(A, '0,00', ',')	35648,31
Num(A, '#,##0.0', '.', ',')	35,648.3
Num(A, '# ##0', ',', ' ')	35 648

Load script

Num can be used in load script to format a number, even if the thousand and decimal separators are already set in the script. The load script below includes specific thousand and decimal separators but then uses Num to format data in different ways.

In the Data load editor, create a new section, and then add the example script and run it. Then add, at least, the fields listed in the results column to a sheet in your app to see the result.

SET ThousandSep=','; SET DecimalSep='.'; Transactions: Load *, Num(transaction_amount) as [No formatting], Num(transaction_amount,'0') as [0], Num(transaction_amount,'#,##0') as [#,##0], Num(transaction_amount,'# ###,00') as [# ###,00], Num(transaction_amount,'# ###,00',',',' ') as [# ###,00 , ',' , ' '], Num(transaction_amount,'#,###.00','.',',') as [#,###.00 , '.' , ','], Num(transaction_amount,'$#,###.00') as [$#,###.00], ; Load * Inline [ transaction_id, transaction_date, transaction_amount, transaction_quantity, discount, customer_id, size, color_code 3750, 20180830, 12423.56, 23, 0,2038593, L, Red 3751, 20180907, 5356.31, 6, 0.1, 203521, m, orange 3752, 20180916, 15.75, 1, 0.22, 5646471, S, blue 3753, 20180922, 1251, 7, 0, 3036491, l, Black 3754, 20180922, 21484.21, 1356, 75, 049681, xs, Red 3756, 20180922, -59.18, 2, 0.333333333333333, 2038593, M, Blue 3757, 20180923, 3177.4, 21, .14, 203521, XL, Black ];

Qlik Sense table showing the results from different uses of the Num function in the load script. The fourth column of the table contains incorrect formatting use, for example purposes.
No formatting	0	#,##0	# ###,00	# ###,00 , ',' , ' '	#,###.00 , '.' , ','	$#,###.00
-59.18	-59	-59	-59###,00	-59,18	-59.18	$-59,18
15.75	16	16	16###,00	15,75	15.75	$15,75
1251	1251	1,251	1251###,00	1 251,00	1,251.00	$1,251.00
3177.4	3177	3,177	3177###,00	3 177,40	3,177.40	$3,177.40
5356.31	5356	5,356	5356###,00	5 356,31	5,356.31	$5,356.31
12423.56	12424	12,424	12424###,00	12 423,56	12,423.56	$12,423.56
21484.21	21484	21,484	21484###,00	21 484,21	21,484.21	$21,484.21

Load script

Num can be used in a load script to format a number as a percentage.

In the Data load editor, create a new section, and then add the example script and run it. Then add, at least, the fields listed in the results column to a sheet in your app to see the result.

SET ThousandSep=','; SET DecimalSep='.'; Transactions: Load *, Num(discount,'#,##0%') as [Discount #,##0%] ; Load * Inline [ transaction_id, transaction_date, transaction_amount, transaction_quantity, discount, customer_id, size, color_code 3750, 20180830, 12423.56, 23, 0,2038593, L, Red 3751, 20180907, 5356.31, 6, 0.1, 203521, m, orange 3752, 20180916, 15.75, 1, 0.22, 5646471, S, blue 3753, 20180922, 1251, 7, 0, 3036491, l, Black 3754, 20180922, 21484.21, 1356, 75, 049681, xs, Red 3756, 20180922, -59.18, 2, 0.333333333333333, 2038593, M, Blue 3757, 20180923, 3177.4, 21, .14, 203521, XL, Black ];

Qlik Sense table showing the results of the Num function being used in the load script to format percentages.
Discount	Discount #,##0%
0.333333333333333	33%
0.22	22%
0	0%
.14	14%
0.1	10%
0	0%
75	7,500%

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here