Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
.png "Former Employee"){kind=icon}
Numbers and dates are a never-ending source of concern when building any BI solution.
But in QlikView, there is always a way to solve a problem… First, QlikView can interpret and format numbers and dates using functions in the script:
Alternatively, it can format them in the user interface, where each object, dimension or expression potentially can have its own number formatting:
The common denominator for interpretation and formatting in QlikView is the Format Code. QlikView uses it as a parameter in many functions, and you can find it in the Format Pattern on the Number tab in the properties dialog.
Some rules of thumb around the format codes:
- Unrecognized characters in the format code can prevent QlikView from interpreting a number.
- The codes are case sensitive. For instance, M means month, whereas m means minutes. Some symbols can be written in either upper or lower case, e.g. AM/PM (or am/pm) and XIV (or xiv). For these, the format code must correspond to what you want: TT for AM/PM; tt for am/pm; (ROM) for XIV and (rom) for xiv.
- With the format code you can force a rounded display, e.g., by specifying two decimals on a multi-decimal number or by specifying year and month, but not day, when displaying a date. Such a rounding will only change the display and not the underlying number. To change the numeric value, you need to use a rounding function, e.g., Round(), Ceil() or MonthStart().
You can specify Integers to have leading zeros. You can also specify them as binary, octal, decimal and hexadecimal numbers. In fact, you can use any radix from 2 to 36. You can also format them as Roman numerals.
Num( Number, '000000' ) as Number // Always with at least 6 digits. Leading zeros!
Num( Number, '(R36)' ) as Number // Displayed in Radix 36 (like hex, but with 36 symbols)
Num( Number, '(ROM)' ) as Number // Upper case Roman numerals
See picture below for the different cases. Each column denotes a specific format code. The rows are examples of input numbers and how QlikView will format them.
Float numbers are similar. If you need QlikView to interpret a number that has a decimal symbol different from the one in the environment variables, be sure to use the third and fourth parameters of the Num#() function to specify decimal character and thousand separator. A correct format code is not enough.
Num#( Number, '0,0', ',' , '.' ) as Num // Number with decimal comma and point as separator
QlikView interprets the integer part of a date serial number as a date – the number of days from Dec 30, 1899. Date formats are different from country to country so you sometimes need to specify which format you want. Note that you can specify weekday also..
Date( MonthStart( date ), 'YYYY MMM' ) as YearMonth
QlikView interprets the fractional part of a date serial number as time of day. This can be specified in hours and minutes, etc. Note that the TT symbol denotes AM/PM. If this is not used, QlikView will assume 24-hour notation.
Time( Ceil( Time, 1/24/4 ), 'hh:mm' ) as Time // Time rounded downwards to nearest 15 min
I recommend that you use interpretation, rounding, and formatting functions in the script to transform data into a form that you want.
HIC
Further reading related to this topic: