Numeric Functions

Numeric functions in QLingo perform tasks to literal or data-driven numbers. All of these functions return a numeric data type, except for the FormatNumber function that returns a string data type.

Remember that every scripting and programming language has a specific numeric precision that determines how many digits are used to store or display numbers. Numeric functions in QLingo can handle numbers with a maximum of 14 digits. Using numeric functions or performing math with numbers that have 15 or more digits may cause incorrect results.

Numeric functions in QLingo include:

ABS Function
Ceil Function
Floor Function
FormatNumber Function
Rand Function
Round Function

Note: Click here to download a sample file containing a data set, plan and InDesign template using the example values and functions on this page.

The following table shows values used in the numeric function examples below. You can also download the full sample file for additional context.

ABS function

The ABS function returns the absolute value of a number—that is, its distance from zero on a number line, regardless of direction. In simpler terms, the ABS function ensures the number (passed to the function as a parameter) is a positive value. This function returns a numeric data type.

Syntax

ABS(exprAsNumber)

The parameter for this function needs to be a numeric literal constant or a numeric formula (expression).

Examples

Find the absolute value of a positive or negative number using the ABS function.

Using a positive number: ABS(5)
Using a negative number: ABS(-5)

Result of both expressions: 5

Find the absolute value of a recipient’s schema field called [opening amount].

ABS(|->[opening amount])

Recipient #1

Field: [opening amount] = -19673.72
Result: 19673.72

Find the absolute value of the change in customer satisfaction between survey 1 and survey 2.

Scenario: Customers are surveyed at different times, and marketing needs to know by how much customer satisfaction has changed regardless of positive or negative change. To calculate this value, subtract the [survey 2] field from the [survey 1] field and then get the absolute value.

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

Recipient #1

Fields:
[survey 1] = 80
[survey 2] = 70

Calculation:
70 – 80 = -10
|-10| = 10

Result: 10

Recipient #2

Fields:
[survey 1] = 60
[survey 2] = 60

Calculation:
60 – 60 = 0
|0| = 0

Result: 0

Ceil function

The Ceil function (also known as "ceiling") returns the smallest integer that is greater than or equal to the number used in the expression. In simpler terms, it rounds up a decimal (or floating point) number to the next largest whole number.

The Ceil function only returns whole numbers. To return fractional/decimal values, refer to the Round function described below. This function returns a numeric data type.

Syntax

Ceil(exprAsNumber)

The parameter for this function needs to be a numeric literal constant or a numeric formula (expression).

Examples

Find the ceiling value of a positive or negative number using the Ceil function.

Using a positive number: Ceil(3.14159)
Result: 4

Using a negative number: Ceil(-3.14159)
Result: -3

Find the ceiling value of a recipient’s schema field called [opening amount].

Ceil(|->[opening amount])

Recipient #1

Field: [opening amount] = -19673.72
Result: -19673

Find the ceiling value of a QLingo expression.

Scenario: Your project needs to calculate 10% of the recipient's opening balance and use the Ceil function to round the result to the next highest whole number.

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

Recipient #1

Field: [opening amount] = -19673.72

Calculation:
-19673.72 / 100 = -196.7372
-196.7372 * 10 = -1967.372
Ceil(-1967.372) = -1967

Result: -1967

Recipient #2

Field: [opening amount] = 14726.07

Calculation:
14726.07 / 100 = 147.2607
147.2607 * 10 = 1472.607
Ceil(1472.607) = 1473

Result: 1473

Floor function

The Floor function returns the greatest integer equal to or less than the number used in the expression. In simpler terms, it rounds down to next lower whole number.

The Floor function only returns whole numbers. To return fractional/decimal values, refer to the Round function described below. This function returns a numeric data type.

Syntax

Floor(exprAsNumber)

The parameter for this function needs to be a numeric formula (expression) or numeric literal constant.

Examples

Find the floor value of a positive or negative number using the Floor function.

Using a positive number: Floor(3.14159)
Result: 3

Using a negative number: Floor(-3.14159)
Result: -4

Find the floor value of a recipient’s schema field called [opening amount].

Recipient #1

Field: [opening amount] = -19673.72
Result: -19674

Find the floor value of a nested QLingo expression.

Scenario: You need to calculate the age of recipients from their date of birth. Since date fields in text or CSV files are treated as strings, first convert the string to a date using the AsDate QLingo function. Then use the Age function to calculate the years between the date of birth and the current date. Because Age returns a decimal, use Floor to round down to the nearest whole number

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

Recipient #1

Field: [DOB] = "4/3/1970"

Calculation:
AsDate("4/3/1970") = #4/03/1970#
Age(#4/03/1970#) = 55.5835616438356 (at Oct 3, 2025)
Floor(55.5835616438356) = 55

Result: 55

Recipient #2

Field: [DOB] = "5/4/1968"

Calculation:
AsDate("5/4/1968") = #5/04/1968#
Age(#5/04/1968#) = 57.4958904109589 (at Oct 3, 2025)
Floor(57.4958904109589) = 57

Result: 57

FormatNumber function

The FormatNumber function allows you to display a number with a particular format or pattern, such as currency ($ 1,234.56).

This function returns a string (text) data type. It is usually the final function applied to a number, especially when nesting functions.

Syntax

FormatNumber(exprAsNumber, formatString, useCommaDecimalAsBoolean)

This function requires two parameters: the number to format (exprAsNumber) and the format pattern (formatString). The third parameter useCommaDecimalAsBoolean is optional and used to determine whether the dot or comma is used as the decimal separator. If omitted, the dot will be used as the decimal separator.

The input for expression1 requires a valid literal numeric value, a string of digits, or formula (expression) that results in a numeric value.

The input for expression2 requires a literal string value or formula that results in a string value which defines the pattern for formatting. This parameter uses three special characters that are used in the pattern. Numbers are filled in right-to-left before with or without the decimal point, and left-to-right to the right of a decimal if used.

Option	Description
#	Hash or pound sign ( # ) is used as a blank placeholder. A number is only shown if needed for that position, otherwise it is left empty.
0	The number zero ( 0 ) is used as a zero placeholder. A number is only shown in that position if there is a number to show, otherwise a zero will be placed at that position
. ,	A period/dot ( . ) or comma ( , ) is used to separate the number into integral and fractional/decimal parts. Note: You can only have one decimal separator character in the expression2 parameter. A second decimal separator will cause the data to be dropped (truncated). The use of a comma as the decimal separator is only possible if you set the `useCommaDecimalAsBoolean` parameter to `TRUE`.
Other	Other characters in your pattern, such as the currency signs ($/€/£), parentheses (), or dashes (-) are displayed as entered, when surrounded by digits, and based on the pattern (format specification). In other words, if the formatting includes commas, a comma only shows when there are numbers on both sides of the character.

If provided, the input for the useCommaDecimalAsBoolean parameter should be either TRUE or FALSE. TRUE indicates that the comma should be used for the decimal separator.

Note that FormatNumber does not perform any mathematical functions on the input value. For example, if expression1 has multiple decimal values and you choose to show only some, the result simply truncates the excess decimals – they are not rounded. However, if needed you can nest the Round function as shown in the samples below.

Examples

Format a static number as currency.

FormatNumber(1234567.89, "$###,###,###.00")
Result: "$1,234,567.89"

Format a static number as currency with comma as the decimal separator.

FormatNumber("1234567,89", "$###.###.###,00", TRUE)
Result: "$1.234.567,89"

Format the value of a recipient’s schema field called [opening amount] as currency.

FormatNumber(|->[opening amount], "$#,###,###.00")

Recipient #1

Field: [opening amount] = -19673.72
Result: "$-19,673.72"

Recipient #2

Field: [opening amount] = 14726.07
Result: "$14,726.07"

Format the value of a recipient’s schema field called [phone] in US phone format.

Scenario: Your client has given you a data source where the phone number is not consistently formatted. The first step is to remove any non-digit characters using the CleanNumber function. Then, use the FormatNumber function to apply the required formatting.

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

Recipient #1

Field: [phone] = "261 784-3674"

Calculation:
CleanNumber("261 784-3674") = 2617843674
FormatNumber(2617843674, "(###) ###-####") = "(261) 784-3674"

Result: "(261) 784-3674"

Recipient #2

Field: [phone] = "939 171 7393"

Calculation:
CleanNumber("939 171 7393") = 9391717393
FormatNumber(9391717393, "(###) ###-####") = "(939) 171-7393"

Result: "(939) 171-7393"

Format the value of a recipient’s schema field called [contactID] so it is always 5 digits long by prepadding 0s to the number.

Scenario: You need the contactID as a postal sorting number to display in an address block and it must be a 5-digit number.

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

Recipient #1

Field: [contactID] = 1
Result: "00001"

Recipient #2

Field: [contactID] = 2
Result: "00002"

Perform mathematical calculations together with formatting the display of the resulting number.

Scenario: You need to add a 7% sales tax to the last purchase amount and format the result as currency, rounded to two decimal places.
Copy
```
FormatNumber(
   Round(
      (
         |->[last purchase] / 100 * 7 //calculate 7% tax on the last purchase price
      ) 
      + |->[last purchase]            //add the last purchase price to the tax amount
     , 2                              //Round the result to 2 digits
   )
   , "$###,###.00"                    //how to format the final result
)
```
Recipient #1

Field: [last purchase] = 447.78

Calculation:
447.78 / 100 * 7 = 31.3446
31.3446 + 447.78 = 479.1246
Round(479.1246, 2) = 479.12
FormatNumber(479.1246, "$###,###.00") = $479.78

Result: "$479.12"

Recipient #4

Field: [last purchase] = 70.08

Calculation:
70.08 / 100 * 7 = 4.9056
4.9056 + 70.08 = 74.9856
Round(74.9856, 2) = 74.99
FormatNumber(74.99,"$###,###.00") = $74.99

Result: "$74.99"

Note: If the Round() function was not used in this example, the result for recipient #4 would be $74.98.

Rand function

The Rand function generates a random number based on the input expression. Depending on the value of the expression, the result may be an integer or a floating-point number. The result is a numeric data type.

Syntax

Rand(expression)

The parameter for this function needs to be a numeric literal constant or a numeric formula (expression).

If the parameter is a number greater than 1 (one), the generated value will be an integer or whole number between 0 (zero) and the parameter value minus one (-1).
If the parameter equals to 1 (one), the generated value will be a fractional in a decimal form, between 0 and 1 (also called a floating-point number).

Note that when using the Rand function, the result is not unique and may be the same for some recipients, especially when generating random numbers for a large number of recipients from a small range of random values.

Examples

Return a random number between 0 and 4.

Rand(5)
Result could be any of: 0, 1, 2, 3, or 4
Return a random number between 1 and 5.

Rand(5) + 1
Result could be any of: 1, 2, 3, 4, or 5
Return a random floating point value between 0 and 1.

Rand(1)
Result could be any 4-digit decimal between 0.0001 and 0.9999. For example, 0.0344.
Return a random number less than the recipient's [contactID].

Rand(|->[contactID])
Result could be any integer less than, but not equal to their contactID.

Use the Rand function to select a random text quote.

Copy

switch (Rand(4))
{
    case 0:
        "'A journey of a thousand miles begins with a single step' - Lao Tzu"
    case 1:
        "'The way to get started is to quit talking and begin doing' - Walt Disney"
    case 2:
        "'Life is like riding a bicycle. To keep your balance, you must keep moving' - Albert Einstein"
    default:
        "'It is never too late to be what you might have been' - George Eliot"
}

Use the Rand function to select a random image.

Copy

switch (Rand(4))
{
    case 0:
        "cat.jpg"
    case 1:
        "dog.jpg"
    case 2:
        "bird.jpg"
    default:
        "fish.jpg"
}

Round function

The Round function rounds up or down decimal numbers to the nearest integer or to a given number of decimal places. For example, if the request is 1 digit after the decimal point (tenths), the digit to the right (hundredths) is used for rounding. Numbers 0-4 are rounded down, while numbers 5-9 are rounded up. This function returns a numeric data type.

Syntax

Round(exprAsNumber, exprAsNumber)

This function requires at least one parameter. The first parameter is the input number to be rounded. The second, optional, parameter is the number of decimal places requested in the result. If the second parameter is omitted, 0 is used for decimal places, and a rounded integer or whole number is returned. Both parameters for this function need to be a numeric literal constant or a numeric formula (expression).

Examples

Round a positive floating point number to its nearest integer.

Round(3.14159)
Result: 3
Round a negative floating point number to the nearest 4 decimals.

Round(-3.14159, 4)
Result: -3.1416
Round the [last purchase] price to the nearest dollar.

Round(|->[last purchase])

Recipient #1

Field: [last purchase] = 447.78
Result: 448

Recipient #2

Field: [last purchase] = 384.29
Result: 384
Calculate a 7% sales tax on the [last purchase] and round the result to 2 decimal places.

Round(|->[last purchase], 2)

Recipient #1

Field: [last purchase] = 447.78

Calculation:
447.78 / 100 * 7 = 31.3446
Round(31.3446, 2) = 31.34

Result: 31.34

Recipient #2

Field: [last purchase] = 384.29

Calculation:
384.29 / 100 * 7 = 26.9003
Round(26.9003, 2) = 26.9

Result: 26.9