Hi,
Check Qlik help file it has all the details regarding this
From Qlikview Help file:
Format Codes for Interpretation
and Formatting Functions
In number format controls and in several of the interpretation and formatting functions it is possible to set the format for numbers and dates by using a format code. This describes how to format a number, date, time or time stamp.
Numbers
- To denote a specific number of digits, use the symbol "0" for each digit.
- To denote a possible digit, use the symbol "#". If the format contains only #'s to the left of the decimal point, numbers less than 1 begin with a decimal point.
- To mark the position of the thousands separator or the decimal separator, use the thousands separator and the decimal separator.
The format code is used for defining the positions of the separators. It is not possible to set the separator in the format code. Use the respective control (in dialogs) or parameter (in script functions) for this.
It is possible to use the thousand separator to group digits by any number of positions, for example, a format string of "0000-0000-0000" (thousand separator="-") could be used to display a twelve-digit part number as "0012-4567-8912".
Special Number Formats
QlikView can interpret and format numbers in any radix between 2 and 36 including binary, octal and hexadecimal. It can also handle roman formats.
To indicate binary format the format code should start with (bin) or (BIN).
To indicate octal format the format code should start with (oct) or (OCT).
To indicate binary format the format code should start with (hex) or (HEX). If the capitalized version is used A-F will be used for formatting (e.g. 14FA). The non-capitalized version will result in formatting with a-f (e.g. 14fa). Interpretation will work for both variants regardless of the capitalization of the format code.
The use of (dec) or (DEC) to indicate decimal format is permitted but unnecessary.
To indicate a format in any radix between 2 and 36 the format code should start with (rxx) or (Rxx) where xx is the two-digit number denoting the radix to be used. If the capitalized R is used letters in radices above 10 will be capitalized when QlikView is formatting (e.g. 14FA). The non-capitalized r will result in formatting with non-capital letters (e.g. 14fa). Interpretation will work for both variants regardless of the capitalization of the format code. Note that (r02) is the equivalent of (bin), (R16) is the equivalent of (HEX) and so on.
To indicate roman numbers the format code should start with (rom) or (ROM). If the capitalized version is used capital letters will be used for formatting (e.g. MMXVI). The non-capitalized version will result in formatting with lower cap letters (mmxvi). Interpretation will work for both variants regardless of the capitalization of the format code. Roman numbers are generalized with minus sign for negative numbers and 0 for zero. Decimals are ignored with roman formatting.
Dates
- To describe the day, use the symbol "D" for each digit.
- To describe the month number, use the symbol "M" or "MM" for one or two digits. "MMM" denotes short month name in letters as defined by the operating system or by the override system variable MonthNames in the script. "MMMM" denotes long month name in letters as defined by the operating system or by the override system variable LongMonthNames in the script.
- To describe the year, use the symbol "Y" for each digit.
- To describe the weekday, use the symbol "W". One W will return the number of the day (e.g. 0 for Monday) as a single digit. "WW" will return the number with two digits (e.g. 02 for Wednesday). "WWW" will show the short version of the weekday name (e.g. Mon) as defined by the operating system or by the override system variable DayName in the script. "WWWW" will show the long version of the weekday name (e.g. Monday) as defined by the operating system or by the override system variable LongDayName in the script
- Arbitrary separators can be used.
Times
- To describe the hours, use the symbol "h" for each digit.
- To describe the minutes, use the symbol "m" for each digit.
- To describe the seconds, use the symbol "s" for each digit.
- To describe the fractions of a second, use the symbol "f" for each digit.
- To describe the time in AM/PM format, use the symbol "tt" after the time.
- Arbitrary separators can be used.
Time Stamps
The same notation as that of dates and times above is used.
Examples of Format Codes
Examples (Numbers):
# ##0 describes the number as an integer with a thousands separator.
###0 describes the number as an integer without a thousands separator.
0000 describes the number as an integer with at least four digits. E.g. the number 123 will be shown as 0123.
0.000 describes the number with three decimals.
0.0## describes the number with at least 1 decimal and at most three decimals.
Examples (Special Number Formats):
| num(199, '(bin)') | returns | 11000111 |
| num(199, '(oct)') | returns | 307 |
| num(199, '(hex)') | returns | c7 |
| num(199, '(HEX)' ) | returns | C7 |
| num(199, '(r02)' ) | returns | 11000111 |
| num(199, '(r16)') | returns | c7 |
| num(199, '(R16)' ) | returns | C7 |
| num(199, '(R36)') | returns | 5J |
| num(199, '(rom)') | returns | cxcix |
| num(199, '(ROM)' ) | returns | CXCIX |
Examples (Date):
YY-MM-DD describes the date as 01-03-31.
YYYY-MM-DD describes the date as 2001-03-31.
YYYY-MMM-DD describes the date as 2001-Mar-31.
31 MMMM YYYY describes the date as 31 March 2001.
M/D/YY describes the date as 3/31/01.
W YY-MM-DD describes the date as 6 01-03-31.
WWW YY-MM-DD describes the date as Sat 01-03-31.
WWWW YY-MM-DD describes the date as Saturday 01-03-31.
Examples (Time):
hh:mm describes the time as 18:30
hh.mm.ss.ff describes the time as 18.30.00.00
hh:mm:tt describes the time as 06:30:pm
Examples (Timestamps):
YY-MM-DD hh:mm describes the timestamp as 97-03-31 18:30
M/D/Y hh.mm.ss.ffff describes the timestamp as 3/31/97 18.30.00.0000
.png "Luminary Alumni"){kind=icon}