Numeric Functions

Numeric functions perform operations on literal or data-driven numbers, returning numeric values (except FormatNumber, which returns a string). These functions are essential for calculations, rounding, formatting numbers for display, and generating random values.

Important: QLingo numeric functions support numbers with a maximum of 14 digits. Using 15 or more digits may produce incorrect results.

ABS function

The ABS function returns the absolute value of a number, which is its distance from zero. The result is always a positive number (or zero).

This function returns a Number data type.

Syntax

ABS(exprAsNumber)

Parameter

Description

exprAsNumber

Required. A numeric expression whose absolute value is to be returned.

Examples

  1. Get the absolute value of positive and negative numbers:

    ABS(5)

    Result: 5

    ABS(-5)

    Result: 5

  2. Calculate the absolute change in customer satisfaction between two surveys:

    ABS(|->[survey 2] - |->[survey 1])

    If survey 1 = 85 and survey 2 = 72:

    Result: 13

  3. Find the difference between two values regardless of which is larger:

    ABS(|->[Actual] - |->[Target])

    If Actual = 150 and Target = 200:

    Result: 50

Ceil function

The Ceil function returns the smallest integer greater than or equal to a number. This function rounds decimal values up to the next whole number.

This function returns a Number data type (whole numbers only).

Syntax

Ceil(exprAsNumber)

Parameter

Description

exprAsNumber

Required. A numeric expression to round up to the nearest integer.

Examples

  1. Round up positive decimal numbers:

    Ceil(3.14159)

    Result: 4

    Ceil(3.0)

    Result: 3

  2. Round up negative decimal numbers (rounds toward zero):

    Ceil(-3.14159)

    Result: -3

    Ceil(-3.9)

    Result: -3

  3. Calculate 10% of an amount and round up to the nearest whole number:

    Ceil(|->[opening amount] / 100 * 10)

    If opening amount = 253:

    Result: 26

  4. Calculate minimum number of boxes needed to ship items:

    Ceil(|->[ItemCount] / 12)

    If ItemCount = 25 (12 items per box):

    Result: 3

Floor function

The Floor function returns the greatest integer equal to or less than a number. This function rounds decimal values down to the next lower whole number.

This function returns a Number data type (whole numbers only).

Syntax

Floor(exprAsNumber)

Parameter

Description

exprAsNumber

Required. A numeric expression to round down to the nearest integer.

Examples

  1. Round down positive decimal numbers:

    Floor(3.14159)

    Result: 3

    Floor(3.9)

    Result: 3

  2. Round down negative decimal numbers (rounds away from zero):

    Floor(-3.14159)

    Result: -4

    Floor(-3.1)

    Result: -4

  3. Calculate age from a birthdate by rounding down the result of the Age function:

    Floor(Age(AsDate(|->[DOB])))

    If Age() returns 34.7 years:

    Result: 34

  4. Calculate complete sets from a total quantity:

    Floor(|->[TotalUnits] / 6)

    If TotalUnits = 20 (6 units per set):

    Result: 3

FormatNumber function

The FormatNumber function formats a number according to a specified pattern. This function is useful for displaying numbers as currency, phone numbers, account numbers with leading zeros, or any other custom format.

This function returns a String data type.

Note: Because FormatNumber returns a string, it should typically be applied as the final function in an expression when further numeric calculations are not needed.

Syntax

FormatNumber(exprAsNumber, formatString [, useCommaDecimalAsBoolean])

Parameter

Description

exprAsNumber

Required. The numeric value to format.

formatString

Required. A pattern string that defines the output format. Use # for optional digits (omitted if zero), 0 for required digits (shown as zero if needed), and . or , for the decimal separator.

useCommaDecimalAsBoolean

Optional. Set to True to use a comma as the decimal separator (European format). Defaults to False (period as decimal separator).

Format Pattern Characters

Character

Description

#

Optional digit placeholder. Displays the digit if present; otherwise, displays nothing.

0

Required digit placeholder. Displays the digit if present; otherwise, displays zero.

.

Decimal separator (or thousands separator when useCommaDecimal is True).

,

Thousands separator (or decimal separator when useCommaDecimal is True).

Examples

  1. Format a number as currency with thousands separators:

    FormatNumber(1234567.89, "$###,###,###.00")

    Result: "$1,234,567.89"

  2. Format a phone number from a numeric value:

    FormatNumber(CleanNumber(|->[phone]), "(###) ###-####")

    If phone = "5551234567":

    Result: "(555) 123-4567"

  3. Pad a number with leading zeros:

    FormatNumber(|->[contactID], "00000")

    If contactID = 1:

    Result: "00001"

    If contactID = 123:

    Result: "00123"

  4. Format a decimal number with fixed decimal places:

    FormatNumber(42.5, "#.00")

    Result: "2.50"

    FormatNumber(42.5, "##.00")

    Result: "42.50"

  5. Format using European decimal notation (comma as decimal separator):

    FormatNumber(1234.56, "###.###,00", True)

    Result: "1.234,56"

  6. Format a price with currency symbol:

    FormatNumber(|->[Price], "$#,##0.00")

    If Price = 99.9:

    Result: "$99.90"

Rand function

The Rand function generates random numbers. When the parameter is greater than 1, it returns a random integer from 0 up to (but not including) the parameter value. When the parameter is 1, it returns a random decimal number between 0 and 1.

This function returns a Number data type.

Syntax

Rand(expression)

Parameter

Description

expression

Required. When greater than 1, specifies the upper bound (exclusive) for random integer generation. When equal to 1, generates a random decimal between 0 and 1.

Behavior

Parameter Value

Result

Rand(n) where n > 1

Returns a random integer from 0 to n-1 (inclusive)

Rand(1)

Returns a random floating-point decimal between 0 and 1

Examples

  1. Generate a random integer from 0 to 4:

    Rand(5)

    Result: 0, 1, 2, 3, or 4 (randomly)

  2. Generate a random integer from 1 to 5 (add 1 to shift the range):

    Rand(5) + 1

    Result: 1, 2, 3, 4, or 5 (randomly)

  3. Generate a random decimal between 0 and 1:

    Rand(1)

    Result: A decimal value like 0.0344, 0.7821, etc.

  4. Randomly select one of several text options using a Switch statement:

    Switch(Rand(3))
    {
    case 0:
    "Thank you for your loyalty!"
    case 1:
    "We appreciate your business!"
    case 2:
    "You're a valued customer!"
    }

    Result: One of the three messages, selected randomly

  5. Generate a random percentage:

    Round(Rand(1) * 100, 0)

    Result: A random integer from 0 to 100

  6. Randomly assign recipients to test groups (A/B testing):

    If(Rand(2) = 0) {"Group A"} else {"Group B"}

    Result: "Group A" or "Group B" (randomly, approximately 50/50)

Notes

  • Random values are generated at composition time. Each recipient in a job will receive their own random value. However, if the same ADOR is evaluated multiple times for a single recipient, it will return the same random value.

  • When generating random values for a large number of recipients with a small range of random values, some recipients will get the same random value.

Round function

The Round function rounds a number to a specified number of decimal places using standard rounding rules (0-4 rounds down, 5-9 rounds up).

This function returns a Number data type.

Syntax

Round(exprAsNumber [, decimalPlaces])

Parameter

Description

exprAsNumber

Required. The numeric value to round.

decimalPlaces

Optional. The number of decimal places to round to. Defaults to 0 (rounds to nearest integer) if omitted.

Examples

  1. Round to the nearest integer (default behavior):

    Round(3.14159)

    Result: 3

    Round(3.5)

    Result: 4

    Round(3.49)

    Result: 3

  2. Round negative numbers:

    Round(-3.14159)

    Result: -3

    Round(-3.5)

    Result: -4

  3. Round to a specific number of decimal places:

    Round(3.14159, 2)

    Result: 3.14

    Round(3.14159, 4)

    Result: 3.1416

    Round(-3.14159, 4)

    Result: -3.1416

  4. Calculate sales tax and round to 2 decimal places:

    Round(|->[last purchase] / 100 * 7, 2)

    If last purchase = 49.99 and tax rate is 7%:

    Result: 3.50

  5. Round a calculated total for display:

    Round(|->[Subtotal] + |->[Tax] + |->[Shipping], 2)

    If Subtotal = 99.99, Tax = 8.25, Shipping = 5.99:

    Result: 114.23

  6. Round to one decimal place for a rating display:

    Round(|->[AverageScore], 1)

    If AverageScore = 4.567:

    Result: 4.6

More topics