Numeric Functions in Integrator

Returns the absolute value of the given numeric expression.

abs(expr : numeric) : numeric

For example:

abs(-25) returns 25

abs(0) returns 0

abs(Actual Dollars) where Actual Dollars contains -5, returns 5

abs(-.87) returns .87

Returns the sum of x and y.

add(x : numeric, y : numeric) : numeric

For example:

add(2, 2) returns 4

add(1, 1.5) returns 2.5

add(2, -1) returns 1

Returns the closest integer equal to or greater than the given number. For negative numbers, this is equivalent to the trunc() function.

ceil(number : numeric) : numeric

For example:

ceil(23.3) returns 24

ceil(23.5) returns 24

ceil(23.7) returns 24

ceil(-1.3) returns -1

ceil(-1.5) returns -1

ceil(-1.7) returns -1

Returns the quotient of x divided by y.

div(x : numeric, y : numeric) : numeric

For example:

div(4, 2) returns 2

div(2, 4) return 0.5

div(-4, 2) returns -2

div(2, 0.5) returns 4

Returns the exponential x, raised to the y power.

exp(x : numeric, y : numeric) : numeric

For example:

exp(2,6) returns 64

exp(.5,2) returns .25

exp(-3,4) returns 81

exp(-11,5) returns -161051

Returns the closest integer equal to or less than the given number. For positive numbers, this is equivalent to the trunc() function.

floor(number : numeric) : numeric

For example:

floor(23.3) returns 23

floor(23.5) returns 23

floor(23.7) returns 23

floor(-1.3) returns -2

floor(-1.5) returns -2

floor(-1.7) returns -2

Returns a formatted number. Specifies a pattern for the output of numbers including the number of decimals and digits, and determines the thousandths separator. The value parameter is an expression that evaluates a number. The format parameter is a string (enclosed in quotes) that specifies a pattern to be used in formatting the number. The format string is equivalent to the pattern used in the java.text.Decimal Format class in Java. Each format has at least one pattern.

format_number(value : numeric, format : string) : numeric

See Pattern Syntax for format_number for more information.

Inserts a decimal point into number giving number_places as the number of decimal places. If number_places is longer than the string, the string is filled with zeros after the decimal point.

insert_decimal(number : numeric, number_places : numeric) : numeric

For example:

insert_decimal(123456,2) returns 1234.56

insert_decimal(-100005,4) returns -10.0005

insert_decimal(8,2) returns .08

insert_decimal("",4) returns .0000

Sample Calc object using insert_decimal

The following example illustrates the insert_decimal feature. A new column is created called Exact Cost, which contains the Unit Cost with the decimal point inserted at the proper place.

Copy

object ’PROC' "insert decimal" {
process_type = "calc",
input = "sales",
calcs = {
            {
            Name = "Exact Cost",
            Value = "insert_decimal(unit cost,2)"
        }
    }
};

Returns the natural logarithm of x.

ln(x : numeric) : numeric

For example:

ln(64) returns 4.158883

ln(1000) returns 6.907755

ln(.25) returns -1.386294

ln(-64) returns null value

ln(1) returns 0

ln(0) returns null value

Returns the log base 10 of x.

log10(x : numeric) : numeric

For example:

log10(64) returns 1.80618

log10(1000) returns 3

log10(.25) returns -0.60206

log10(-64) returns null value

log10(1) returns 0

log10(0) returns null value

log10(10) returns 1

Returns the maximum numeric value of its arguments. This function accepts a variable number of arguments; any non-numeric arguments will be ignored. If all the arguments are non-numeric, this function will return null. If columns are specified, the max is determined by row.

max(number1 : numeric) : numeric

max(number1 : numeric, number2 : numeric) : numeric

max(number1 : numeric, number2 : numeric, number3 : numeric, ...) : numeric

For example:

max(5, 17) returns 17

max(12) returns 12

max(5, "foo", "2000") returns 2000

max(-100, 10, 0, 100, 1234) returns 1234

max("bar") returns null

Returns the minimum numeric value of its arguments. This function accepts a variable number of arguments; any non-numeric arguments will be ignored. If all the arguments are non-numeric, this function will return null. If columns are specified, the min is determined by row.

min(number1 : numeric) : numeric

min(number1 : numeric, number2 : numeric) : numeric

min(number1 : numeric, number2 : numeric, number3 : numeric, ...) : numeric

For example:

min(5, 17) returns 5

min(12) returns 12

min(5, "foo", "2000") returns 5

min(-100, 10, 0, 100, 1234) returns -100

min("bar") returns null

Returns the remainder when dividing a numerator by a denominator. Returns zero if they divide evenly. For example, mod(10,3) will return 1 because 10/3=3 with a remainder of 1. Works with integers and decimals.

mod(numerator : numeric, denominator : numeric) : numeric

For example:

mod(100,3) returns 1 (100/3=33, remainder 1)

mod(120,5) returns 0 (120/5=24, no remainder)

mod(25,6) returns 1 (25/6=4, remainder 1)

mod(36.01,12) returns 0.01 (36.01/12=3 remainder .01)

mod(28.55,3) returns 1.55 (28.55/3=9 remainder 1.55)

mod(11.90,6) returns 5.9 (11.90/6=1 remainder 5.9)

mod(6.6,6) returns 0.6 (6.6/6=1 remainder .6)

Returns the product of the multiplication of x and y.

mul(x : numeric, y : numeric) : numeric

For example:

mul(3, 3) returns 9

mul(-3, 3) returns -9

mul(3, 0) returns 0

mul(3, 0.5) returns 1.5

Returns a random number between 0 and 1 with six decimal places.

For example:

rand() returns 0.712581 randbetween(100, 100000) returns 58943

Returns a random integer between lowest and highest, inclusive.

randbetween(lowest : numeric, highest : numeric) : numeric

For example:

randbetween(100, 100000) returns 2868

Rounds the number to the nearest integer or other rounding unit, if specified. If number is halfway between rounding units, it will be rounded away from zero. If specified, the round-unit must be positive.

round(number : numeric) : numeric

round(number : numeric, round-unit : numeric) : numeric

For example:

round(0.2) returns 0

round(0.49) returns 0

round(0.5) returns 1

round(39.7418, 0.01) returns 39.74

round(-0.5) returns -1

round(0.49, 0.1) returns 0.5

round(3.1415926, .000001) returns 3.141593

round(-55, 100) returns -100

round(17, 5) returns 15

round(23.5) returns 24

Returns 1 for positive, -1 for negative, and 0 for zero.

sign(number : numeric) : numeric

For example:

sign(700) returns 1

sign(-100.82) returns -1

sign(0.0) returns 0

Returns standard square root of a number.

sqrt(number : numeric) : numeric

For example:

sqrt(81) returns 9

sqrt(.25) returns .5

sqrt(25) returns 5

sqrt(175) returns 13.2288

sqrt(.45) returns 0.6708

sqrt(-1) returns null value

Returns the difference of y subtracted from x.

sub(x : numeric, y : numeric) : numeric

For example:

sub(4, 1) returns 3

sub(1, 4) returns -3

sub(4, 1.5) returns 2.5

sub(4, -1) returns 5

Returns the expr as an integer, ignoring any non-integer component of the number. Positive numbers are rounded down and negative numbers are rounded up.

trunc(expr : numeric) : numeric

For example:

trunc(23.3) returns 23

trunc(-1.5) returns -1

trunc(Actual Dollars), (where Actual Dollars contains 5.25) returns 5

A pattern consists of a positive pattern and an optional negative pattern (commonly referred to as a subpattern). Each positive pattern and a negative subpattern is separated by a semicolon(;).

positive pattern [';' negative pattern]

Example: #,##0.00;(#,##0.00)

Each subpattern contains an integer and can optionally contain a fraction and also a prefix and/ or 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 will be multiplied by 100 and shown as a percentage. To display a percent sign without performing the multiplication, enclose the sign in single quotes ('%').
• Integers can be:
  • '#'*
  • '0'*
  • '0'
    * indicates one or more instances. When specifying more than one integer, use a comma (',').
• Fractions can be:
  • '0'*
  • '#'*
    * indicates one or more instances.

Keep the following points in mind when using the format_number function:

• A negative number with a currency prefix (for example, $) places the minus sign before the currency indicator. For example:
  -123.45 formatted with "$#,###.##" results in -$123.45
• Any prefix starting with a currency symbol will be treated in the same manner as the item mentioned above. For example:
  $USD appears as -$USD25.35
• If the value has more significant digits than the number of digits in the format fraction, the number will be rounded to the appropriate value in half-even mode; if the number could be rounded to two numbers that are equidistant, the one ending with an even digit is selected.

• For formats that support the rounding up and rounding down of numbers (such as ###), numbers ending with a 5 or greater round up while numbers below 5 round down. For example:

  format_number("234.98", "#0.0") results 235.0

• When rounding by thousands, a "," at the end of a format results in the number being divided by 1000 before display. For example:
  ###,k displays 256,000 as 256k
  #.#,,m displays 1234567 as 1.2m.
• This function provides a 17-digit level of precision. See the examples below.
• If the format is an invalid pattern, format_number will return "********".
• In the U.S., the format is returned with a decimal (.), while in Sweden, it is returned with a comma (,). See examples below.
• Locale is determined from the system settings. It can be changed with a java command line argument. For example:
  java -Duser.language=sv -Duser.country=sv

Examples assuming US locale:

format_number(0, "#") returns 0

format_number(20, "#,##0.##") returns 20

format_number(20,".#") returns 20.0

format_number(20, "#,##0.00") returns 20.00

format_number(20, "0000") returns 0020

format_number(12345.67, "#,###0.##") returns 1,2345.67

format_number(12345.67, "#,###0.#") returns 1,2345.7

format_number(12345.67, "####0.#") returns 12345.7

format_number(-20, "#,##0.##;(#,##0.##)") returns (20)

format_number(12345.67,"$$#,##0.00") returns $12,345.67

format_number(12345.67, "aaaa") returns "********"

Examples showing how locale effects 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)

The following examples demonstrate the 17-digit level of precision when using the format_number function with the 0 and # placeholder characters. The value returned will be precise up to 17 digits. When using the # placeholder, up to 17 digits will be included in the return value; when using the 0 placeholder, the return value will be 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:

This calc contains 2 # characters to the right of the decimal. The return value includes 2 digits to the right of the decimal.

format_number((Bottles/BPC)*DollarCase,"##.##") returns 3.23 

This calc contains 2 zeros to the right of the decimal. The return value includes 2 digits to the right of the decimal.

format_number((Bottles/BPC)*DollarCase,"##.00") returns 3.23 

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,"##.############") returns
3.229166666667 

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,"##.00000000000000000") 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,"##.####################")
returns 3.22916666666666652 

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 3 zeros to the end of the value.

format_number((Bottles/BPC)*DollarCase,"##.00000000000000000000")
returns 3.22916666666666652000 

NOTE: When a Model is built from a script that contains three or more placeholder characters in the format_number function and the Model is then used in another script, the values will display no more than six digits of precision. For example, a calc with a return value of 3.22916666666666652 (17 digits of precision) is part of a Model that is built. If the Model is then used in a new script, the value will revert to 6 digits of precision (in this example, 3.229167).