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).

Note: If the number being formatted has more digits than the formatting expression, leading digits will be truncated. Ensure your format pattern has enough digit placeholders to accommodate the largest expected values.

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"

7. Format a number with more digits than expected by the format expression string:

   FormatNumber(12345, "0000")

   Result: "2345"

   The format "0000" has only four digit placeholders, so the leading 1 is truncated.

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

• Conversion Functions - Functions for converting between data types

• String Functions - Functions for text manipulation

• Date Functions - Functions for date and time operations