The calc.format_number function in DIAL specifies a pattern for the output of numbers, including the number of decimals and digits, and determines the thousandths separator.
| Function | Description |
|---|---|
|
calc.format_number(value, format) |
Returns a formatted number. The value argument is an expression that evaluates a number. The format argument is a string enclosed in quotation marks that specifies a pattern to use in formatting the number. The format string is equivalent to the pattern that is used in the java.text.Decimal Format class in Java. Each format has at least one pattern. |
Pattern Syntax
A pattern consists of a positive pattern and an optional negative pattern, which is commonly referred to as a subpattern. Each positive pattern and a negative subpattern is separated by a semicolon (;).
positive pattern [';' negative pattern]
Each subpattern contains an integer and can optionally contain a prefix, a fraction, and a suffix, as shown by the following syntax:
[prefix] integer [ '.' fraction] [suffix]
- The prefix and suffix can contain any non-special characters such as currency symbols, parentheses, and percent (%). If % is used, the number is multiplied by 100 and shown as a percentage. To display a percent sign without performing the multiplication, enclose the sign in single quotation marks ('%').
- The asterisk (*) character indicates one or more instances. When specifying more than one integer, use a comma (,) separator.
- Integers can be:
- '#'*
- '0'*
- '0'
- Fractions can be:
- '0'*
- '#'*
Special Pattern Characters
Special pattern characters represent other characters, strings, or classes of characters. Unless otherwise noted, these characters must be contained in quotation marks ( " " ) if they are to appear in the prefix or suffix as literals.
| Character | Description |
|---|---|
| 0 | Placeholder for a digit. This character always appears as a digit. |
| # | Place holder for a digit. This does not display if not needed. |
| - | Negative sign |
| . | Decimal separator for locale |
| , | Grouping separator for locale (used in integer portion) |
| E | Separates mantissa and exponent for exponential formats |
| % | Multiplies by 100 and shows as a percentage |
Keep the following points in mind:
- If the value has more significant digits than the number of digits in the format fraction, the number is rounded up to the appropriate value—that is, if the number could be rounded to two numbers that are equidistant, the higher number is selected.
- If the format is an invalid pattern, format_number returns "********".
- When using cBases, if explicit formatting is not done in the DIAL script, any formatting specified in the Spectre Build or cPlan is used.
Calc Format Number Examples
The following examples demonstrate the usage of the calc.format_number function. Either calc.format_number or format_number can be specified.
Examples 1
The following examples demonstrate the use of the calc.format_number function. These examples assume US locale:
format_number(0, "#"); returns 0
format_number(20, "#,##0.##"); returns "20"
format_number(20, "#,##0.00"); returns "20.00"
format_number(20, "0000"); returns "0020"
format_number(12345.67, "##,##0.##"); returns "12,345.67"
format_number(12345.67, "##,##0.#"); returns "12,345.7"
format_number(12345.67, "####0.#"); returns "12345.7"
format_number(-20, "#,##0.##;(#,##0.##)"); returns "(20)"
format_number(12345.67, "aaaa"); returns "********"
Examples 2
The following examples show how locale affects the returned format:
format_number(1234.56, "#,##0.##"); returns "1,234.56" (in U.S.)
format_number(1234.56, "#,##0.##"); returns "1.234,56" (in Sweden)
In the U.S., the format returns the number with a decimal (.), while in Sweden, the format returns the number with a comma (,). Locale is determined from the system settings.
Examples 3
The following examples demonstrate the 17-digit level of precision when using the format_number function with the 0 and # placeholder characters. The values returned are precise up to 17 digits. When using the # placeholder, up to 17 digits are included in the return value; when using the 0 placeholder, the return value is padded with zeros beyond 17 digits.
For example, using sample data from a row where Bottles is 1, BPC is 12, and DollarCase is 38.75:
format_number((Bottles/BPC)*DollarCase,"##.##")
Returns 3.23. This calc contains two # characters to the right of the decimal. The return value includes two digits to the right of the decimal.
format_number((Bottles/BPC)*DollarCase,"##.00")
Returns 3.23. This calc contains two zeros to the right of the decimal. The return value includes two digits to the right of the decimal.
format_number((Bottles/BPC)*DollarCase,"##.############")
Returns 3.229166666667. This calc contains 12 # characters to the right of the decimal. The return value contains 12 digits to the right of the decimal.
format_number((Bottles/BPC)*DollarCase,"##.00000000000000000")
Returns 3.22916666666666652. This calc contains 17 zeros to the right of the decimal and returns 17 digits to the right of the decimal.
format_number((Bottles/BPC)*DollarCase,"##.####################")
Returns 3.22916666666666652. This calc contains 20 # characters to the right of the decimal. The return value includes 17 digits to the right of the decimal just as the previous example does. The three additional # characters included in the calculation are ignored in the return value.
format_number((Bottles/BPC)*DollarCase,"##.00000000000000000000")
Returns 3.22916666666666652000. This calc contains 20 zeros to the right of the decimal. The return value includes the same 17 digits as the example above but adds three zeros to the end of the value.