Table of Contents Previous Next Index

Section 18 Calculation Formulas

Section 18 Calculation Formulas
In LISTSERV Maestro, calculation formulas are available in the context of the special “*Calc” system drop-in (see Section 15.3.1 System Drop-Ins). This section describes the syntax and semantics of these calculation formulas.
A formula is a sequence of expressions that are combined with operators into more complex expressions. Expressions can optionally be nested with parenthesis and make use of a few pre-defined functions.
Examples for formulas are:
15 + 4
27 * Max(17, 4, 24/8) / (19 + 22)
&NAME; + "@lsoft.com"
(ToNum(&AGE;) – 2004) * 10
ToDate(CurrentTimeMillis, "MM/dd/yyyy HH:mm")
The following sub-sections explain all aspects of formulas in detail.
18.1 Expressions
Operands can be of the type “number” or of the type “text”. They can appear in different “shapes”, which are described in the following sub-sections.
For “number” type expressions, the number can be any integer number in the range
-9223372036854775808 to 9223372036854775807
Floating point numbers are not possible in LISTSERV Maestro formulas.
The text can be any character string for “text” type expressions.
18.1.1 Constant Number Expressions
Restriction: None. Constant number expressions can be used in any formula.
Type: Constant number expressions are, as the name implies, always of the type “number”.
Usage: To include a constant number in a formula, simply type the number using only the digits “0­­“-“9” (do not use “,” as a thousands-separator or “.” as a decimal point). Negative numbers must have a leading minus sign “-“.
Examples:
1
0
537
-17
007
18.1.2 Constant Text Literal Expressions
Restriction: None. Constant text literal expressions can be used in any formula.
Type: Constant text literal expressions are, as the name implies, always of the type “text”.
Usage: To include a text literal in a formula, type the characters desired, and then denote the beginning and the end of the text literal by enclosing the whole string in quotation marks <">. There must not be any linebreaks in the text string.
Since the text literal must be quoted, it must also follow the rules for quote escaping. See Section 16 Advanced Use of System Drop-Ins for details.
Examples:
"example"
"this is a text string"
"This string contains ""quotes"" which are therefore escaped"
" this string has three spaces at the beginning and end "
18.1.3 Standard Merge Field Expressions
Restriction: Standard merge field expressions are only allowed if the formula is used in a “*Calc” system drop-in of a job with the recipients type
Target Group (if the target group is based on either of the two above or on a Hosted Recipient List).
If the system drop-in is used in a job with a different recipient type, then the formula must not contain any merge field expressions (if it does, an error message will appear during test delivery).
Type: Standard merge field expressions can either be the number or text type, depending on their context or content (see the explanation for Type Determination for details).
Usage: To include a standard merge field in a formula, type the merge field name enclosed in “&” and “;”. Only merge field names that are actually defined in the recipient list can be used.
Examples:
&NAME;
&STATE;
&ACCOUNT_BALANCE;
Type Determination: Whenever a merge field appears in a formula, it will first be replaced with the field value for the current recipient before the result of the formula is calculated for that recipient. To be able to do this calculation, the expression’s type is determined as follows:
If the formula is used in a “*Calc” system drop-in together with recipients based on a “Hosted Recipient List” target group, then the type of each merge field is already defined by the profile field’s type, which was defined during creation of the associated hosted recipient list:
Profile fields of the Number type have the expression type number.
Profile fields of the Text type have the expression type text.
Profile fields of the Single Selection type have the expression type text.
Profile fields of the Boolean and Multiple Selection types are not allowed to be used in formulas at all.
This determination of the type is already done during parsing of the formula.
If the formula is used in a “*Calc” system drop-in with the recipients types as uploaded CSV text file or LISTSERV Maestro selects from a database, then the type of the field is determined by the field’s content as follows:
If the field’s content can be interpreted as a number, then the number type will be used. If it cannot be interpreted as a number (or is empty), then the text type will be used (empty content will become an empty text string).
Consequently, to use the merge field in a location of the formula where a number type is required, make sure that the field’s content can be interpreted as a number for all recipients. Otherwise, the field will have the text type for some recipients, which would cause the calculation of the formula to fail, so the job itself would fail as well.
This determination of the type is done during delivery, when the merge-values of all recipients are known.
Special Note: If the formula is used in a “*Calc” system drop-in together with recipients based on a “Hosted Recipient List” target group, then only profile fields which are defined in the hosted recipient list as mandatory are allowed as “standard merge field” expressions. Fields that are defined as optional must use the “optional merge field” expression instead. (See next sub-section.)
18.1.4 Optional Merge Field Expressions
Restriction: Optional merge field expressions are only allowed if the formula is used in a “*Calc” system drop-in of a job with the recipients type
Target Group (if the target group is based on either of the two above or on a Hosted Recipient List).
If the system drop-in is used in a job with a different recipient type, the formula must not contain any merge field expressions (if it does, an error message will display during test delivery).
Type: Optional merge field expressions can be of the number or text type, depending on their context and content (see the explanation of Type Determination for details).
Usage: Optional merge fields are fields that may be undefined (empty) for at least some recipients in the recipient list. Recipients with a blank field would not have the result of the calculation formula displayed in their content.
An optional merge field expression is written in the following way:
[&FIELD_NAME; DEFAULT]
The whole expression is enclosed in brackets “[“ and “]”. Between the brackets first type the name of the merge field being addressed, enclosed with “&” and “;”. Next, type the default that will be used for all recipients where the merge field itself is undefined.
The default that is replaced by “DEFAULT” as shown above must be a constant number or a constant text literal.
Examples:
[&NAME; "no name"]
[&NAME; ""]
[&STATE; "n/a"]
[&ACCOUNT_BALANCE; 0]
(The second example defines an empty text as the default for &NAME;.)
Type Determination: Whenever any such merge field appears in a formula, it will first be replaced with the field value for the current recipient, or if that value is undefined or empty, it will be replaced with the given default. Then the result of the formula is calculated for that recipient. To be able to do this calculation, the expression’s type is determined just like for “standard merge field” expressions.
In addition, if the field’s value is empty or undefined, the type will be determined by the type of the given default. Make sure that the type of the default matches the field’s type or is at least convertible into that type (see Automatic Type-Conversion).
18.1.5 Function Expressions
Restriction: Functions can usually be used in any formula. However, there may be individual restrictions that apply only to certain functions. See the function descriptions for details.
Type: The type depends on each function. See the function descriptions for details.
Usage: To include a function in a formula, simply type the function name possibly followed by an argument list in parenthesis. See the function description for details concerning whether arguments are required by a certain function and what they mean.
Note: Function names are case sensitive and any parenthesis enclosing the argument list must immediately follow the function name, without any space in between.
Examples:
Abs(-20)
Max(15, &AGE;)
ToLower("Convert this string to ALL Lowercase")
Min(-10, -11, &SIZE_A;, &SIZE_B;)
Available Functions: The currently available functions are described in Formula Functions.
18.2 Operators
Operators always work on two operands, which may be any valid expressions. Some operators require their operand expressions to be either the number or text type.
18.2.1 Operators for “Number” Operands
The following operators require both operand expressions to be the number type:
+ Addition operator (sum of both operands)
- Subtraction operator (difference of both operands)
* Multiplication operator (product of both operands)
/ Integer-Division operator (integer-quotient of both operands)
% Modulo operator (remainder of integer-division of both operands)
Note: In LISTSERV Maestro, the division is strictly an integer division. Any decimal places in the result are discarded; they are not rounded off, but simply ignored.
18.2.2 Operators for “Text” Operands
The following operators require both operand expressions to be the text type:
+ Concatenation operator (appends both operands)
The concatenation operator takes the two text operands and simply appends the text string on the right-hand to the end of the text string on the left-hand. The result is one string that contains the two strings concatenated together.
The character “+” is used both to denote the addition operator for number operands as well as the concatenation operator for text operands. So if a “+” appears anywhere in the formula, LISTSERV Maestro must first determine if in this context the addition or the concatenation operator is meant. It simply does so by looking at the operands. If both operands are the number type, then the “+” is interpreted as the addition operator and the result will also be the number type.
If at least one of the operands is of the text type, then the “+” is interpreted as the concatenation operator and the result will be of the text type. If the other operand is the number type, it is automatically converted to text first, see Automatic Type-Conversion.
18.2.3 Operator Precedence and Parenthesis
Formulas are processed by LISTSERV Maestro following the usual mathematical conventions:
The multiplication/division-type operators (“*”, “/” and “%”) have higher precedence than the addition/subtraction-type operators (“+” and “”).
Operators with higher precedence are processed first, if several operators with the same precedence level are encountered. They are processed from left to right. Parenthesis can be set freely to influence precedence: Inner parenthesis will be processed before outer parenthesis.
Examples:
15 + 3 * 4 Result: 27
8 * (7 – 3) Result: 32
17 * 22 / 2 % 5 Result: 2
17 * (22 / 2 % 5) Result: 17
17 * (22 / (2 % 5)) Result: 187
18.3 Automatic Type-Conversion
Operators and functions usually require their operands and arguments to be of a certain type. However, under one circumstance it is possible to supply an expression of a different type as an operand or argument, which will then automatically be converted to the required type:
Whenever an operand or argument of the text type is required, an expression of the text or number type can be used. In the latter case, the number will first be converted into a text before the operator or function is applied. For this conversion, the number-value is simply converted into the corresponding string representation, for example the number 157 becomes the text string "157".
18.4 Formula Functions
The following tables describe the functions that are available for use in LISTSERV Maestro formulas.
 
Abs(arg)
argNumber type: The argument whose absolute value is to be determined.
Abs(-10), Abs(&VALUE;), Abs(30 - &ACCOUNT; * 2)
 
Returns the current time, in milliseconds since
“Jan. 1st 1970, 00:00:00 GMT”.
 
 
IndexOf(text, searchtext [, startindex])
Searches for an appearance of the text “searchtext” in the given “text”. Returns the zero-based index of the first appearance of “searchtext”, or “1” if none was found. Optionally, a zero-based “startindex” can be specified with the effect that the search in “text” will not start at the beginning of “text” but at the character position specified by “startindex”.
textText type: The text string to search in.
searchtextText type: The string to search for.
startindexNumber type: The character position at which the search shall be started. If not given, the search will always start at the first character (index “0”).
IndexOf("a longer text", "lo"),
IndexOf("abc def abc def", "abc", 4)
 
Length(text)
textText type: The text whose length shall be determined.
Length("some text"), Length(&FULL_NAME;)
 
Max(arg1, arg2 [, arg3, ... , argN])
arg1Number type: The first argument to find the maximum of.
arg2Number type: The second argument to find the maximum of.
arg3Number type: The third argument to find the maximum of.
argNNumber type: The Nth argument to find the maximum of.
Max(3, -4), Max(17, 22, 4), Max(82, &VALUE; * 7)
 
Min(arg1, arg2 [, arg3, ... , argN])
arg1Number type: The first argument to find the minimum of.
arg2Number type: The second argument to find the minimum of.
arg3Number type: The third argument to find the minimum of.
argNNumber type: The Nth argument to find the minimum of.
Min(13, 2), Min(17, -&VALUE;, 4 * -17)
 
Pow(base, exp)
baseNumber type: The base argument.
expNumber type: The exponent argument.
Pow(2, 8), Pow(&BASE;, &EXPONENT;)
 
Random[(threshold)]
No argument given: The range is from 0 to the largest possible number value.
One argument given: The range is from 0 to the value of the given argument minus one (i.e. “0” to “threshold-1”).
thresholdNumber type: The threshold value for the random number. The random number will range from “0” to “threshold – 1”.
Random, Random(6), Random(&MAX_RANDOM;),
Random(&VALUE; - 17 * &MAX;)
 
Returns the time at which the subscriber subscribed to the hosted recipient list referenced by the target group in whose context the formula is used, represented in milliseconds since “Jan. 1st 1970, 00:00:00 GMT”.
Can only be used in formulas in the condition tree of a Hosted Recipient List target group or in a “*Calc” system drop-in together with recipients based on such a target group. Will not be accepted in formulas for a different recipient type.
 
Substring(originaltext, startindex [, endindex])
The substring begins with the character in “originaltext” at the position specified by “startindex”. If no “endindex” is given, the substring extends to the end of the original string. If “endindex” is given, the substring extends to the character at position “endindex – 1”, i.e. the character at “endindex” is the first character that is not part of the substring.
Note: all indices are zero-based, i.e. the index of the last character in the original string is at the position “length of original string minus 1”.
originaltextText type: The original text string from which the substring will be extracted.
startindexNumber type: The beginning index, inclusive, zero-based.
endindexNumber type: The ending index, exclusive, zero-based.
Substring("original text", 5),
Substring("original text", 3, 3 + &VALUE;)
 
 
 
ToDate(datevalue, formatpattern [,localename | langcode,countrycode])
Returns a formatted representation of a numerical date/time value. The formatted representation is usually returned as text, but may also be returned as a number, if applicable. The format that is used to create the representation is specified with the given format argument.
Note: For formatting purposes, LISTSERV Maestro uses the U.S. locale and the time zone of the server where the LUI component is running by default. Locale text, like weekday names, names of months, etc., will be formatted using the U.S. locale. Similarly, times will be formatted using the server’s time zone.
To specify a different locale, use the optional parameter “localename” to choose a predefined locale or the optional parameters “langcode” and “countrycode” to specify your own locale. See Time and Date Patterns for details.
Usually Text. However, may be Number, if the result can be expressed as a number.
datevalue – Type Number: The date/time value to format. Must contain the desired date/time represented as milliseconds since “Jan. 1st 1970, 00:00:00 GMT” (for example from the output of the functions “CurrentMillis”, “SubscribeTimeMillis” and “ToMillis”).
formatpattern – Type Text: Specifies the format pattern to use to convert the milliseconds value in “datevalue” into the formatted representation.
Or you supply only the following argument:
localenameText type: A name of a predefined locale. See section for a list of available locale names. If specified, any locale specific text in the formatted date/time representation will be given according to this locale.
Or you supply both the following two arguments:
langcodeText type: A lowercase two-letter ISO-639 language code specifying the language for the locale. See for example http://ftp.ics.uci.edu/pub/ietf/http/related/iso639.txt for a list of the ISO-639 language codes.
countrycodeText type: An uppercase two-letter ISO-3166 country code specifying the country for the locale. See for example http://userpage.chemie.fu-berlin.de/diverse/doc/ISO_3166.html for a list of the ISO-3316 country codes.
If “langcode” and “countrycode” are specified, they are used to create a locale for the given language and country, and any locale specific text in the formatted date/time representation will be given according to this locale.
 
textText type: The text string to convert to lowercase.
 
ToNum(text)
Tries to parse a number from the given text string and returns that number. Generates an error if the provided text string does not contain a valid number (for example letters).
Can be used to convert a profile field which is of type text but still only contains numbers to the type number, which can then be used in contexts that require the type number (such as number operators and functions requiring a number argument).
textText type: The text string to convert to a number.
ToNum("12345"), ToNum(&AGE;), ToNum("123" + "456")
Note: The result of the last example will not be the number 579, but will instead be the number 123456, i.e. first the string concatenation operator “+” is applied to the two strings and then the resulting string is converted into a number.
 
textText type: The text string to convert to uppercase.
 
 
 
ToMillis(datetext, formatpattern [, localename | langcode, countrycode])
Returns the numerical value representing the date/time as parsed from a text string argument, where the numerical value specifies the date/time in milliseconds since “Jan. 1st 1970, 00:00:00 GMT” (can then be compared to the output of the functions “CurrentMillis” and “SubscribeTimeMillis” or be used as input to “ToDate”). The format used to parse the given date/time text string is specified with the given format argument.
Note: For parsing purposes, LISTSERV Maestro will by default assume the U.S. locale and the time zone of the server where the LUI component is running, i.e. if locale specific texts, like weekday names, names of months, etc., are contained in the date/time text string, they must appear with the correct names of the U.S. locale. Similarly, times given in the date/time text string will be interpreted as relative to the server’s time zone.
To specify a different locale, use the optional parameter “localename” to choose a predefined locale or with the optional parameters “langcode” and “countrycode” to specify your own locale. See Time and Date Patterns for details.
datetextText type: The date/time text string to parse. Must contain the desired date/time in a textual format which can be parsed by applying the given format pattern.
formatpatternText type: specifies the format pattern to use to parse the “datetext”. See Date and Time Patterns for details.
Or you supply only the following argument:
localenameText type: A name of a predefined locale. See Date and Time Patterns for a list of available locale names. If specified, any locale specific text in “datetext” will be expected according to this locale.
Or you supply both the following two arguments:
langcodeText type: A lowercase two-letter ISO-639 language code specifying the language for the locale. See for example http://ftp.ics.uci.edu/pub/ietf/http/related/iso639.txt for a list of the ISO-639 language codes.
countrycodeText type: An uppercase two-letter ISO-3166 country code specifying the country for the locale. See for example http://userpage.chemie.fu-berlin.de/diverse/doc/ISO_3166.html for a list of the ISO-3316 country codes.
If “langcode” and “countrycode” are specified, they are used to create a locale for the given language and country, and any locale specific text in “datetext” will be expected according to this locale.
(parses the given date using a custom locale for language “de” =German and country “AT” =Austria)
18.4.1 Date and Time Patterns
The format of the date and time patterns must be specified for the functions “ToDate” and “ToMillis” to convert a numerical date/time value (represented in milliseconds since “Jan. 1st 1970, 00:00:00 GMT”) into a formatted output string or vice versa.
The pattern format described here applies to the formatting process, when a numerical date/time value is converted into a formatted text string, and to the parsing process, when a text string is parsed to convert it back into the numerical date/time value.
Important: For date/time formatting and parsing, by default the U.S. locale and the current time zone of the server where the Maestro User Interface (LUI) component is running is used. This means that if locale specific values (names of months, weekdays, era-designators, and the like) are required, they will be given as the U.S. locale values by default. Similarly, if a time is given, it will be formatted (or interpreted) as relative to the time zone of the server (although for parsing a specific time zone can be supplied). If the default U.S. locale is not desired, specify a locale in the “ToDate” or “ToMillis” function.
Date and Time Formats
Date and time formats are specified by pattern strings. Within pattern strings, unquoted letters from ‘A’ to ‘Z’ and from ‘a’ to ‘z’ are interpreted as pattern letters representing the components of a date or time string (see below for details). Text can be quoted using single quotes <'> to avoid interpretation. In a quoted text, the double single quote <''> represents a single quote. All other characters are not interpreted; they are simply copied into the output string during formatting or matched against the input string during parsing.
The following pattern letters are defined (all other characters from ‘A’ to ‘Z’ and from ‘a’ to ‘z’ are reserved). The letters are case-sensitive. The same letter has different meanings in its uppercase or lowercase version. Each pattern letter has a specific “presentation” in the created final string (during formatting) or in the parsed string (during parsing). For example, presentation types may be Text, Number, Year or similar. More details about the presentations and their meanings follow below.
Commonly Used Pattern Letters:
Special Pattern Letters:
Note: The calendar week in year and calendar week in month value depends on the locale that is used. The locale determines the conventions about which weekday is interpreted as the first day of the week (usually “Monday” or “Sunday”) and under which circumstances a week that falls partially into one year (or month) and partially into the next, is interpreted as belonging to the one year (or month) or the other.
Note: The weekday ordinal in month value indicates the ordinal number of the weekday of the given date/time in the given month. For the first Monday in a month, the ordinal is “1”, as it is for the first Tuesday, Wednesday and so on. For the second Monday in a month, the ordinal is “2”, and so on.
Presentation Description
Pattern letters are usually repeated, as their number determines the exact presentation:
Text: For formatting, if the number of pattern letters is four or more, the full form is used; otherwise, a short or abbreviated form is used if available.
For parsing, both forms are accepted, independent of the number of pattern letters.
Number: For formatting, the number of pattern letters is the minimum number of digits, and shorter numbers are zero-padded to this amount.
For parsing, the number of pattern letters is ignored unless it is needed to separate two adjacent fields.
Year: For formatting, if the number of pattern letters is two, the year is truncated to 2 digits; otherwise it is interpreted as a Number (see above).
For parsing, if the number of pattern letters is more than two, the year is interpreted literally, regardless of the number of digits. So using the pattern “MM/dd/yyyy”, the text “01/11/12” parses to Jan. 11, 12 AD.
For parsing with the abbreviated year pattern (“y” or “yy”), LISTSERV Maestro must interpret the abbreviated year relative to some century. It does this by adjusting dates to be within 80 years before and 20 years after the current time. For example, using the pattern “MM/dd/yy” on Jan. 1, 1997, the text “01/11/12” would be interpreted as Jan. 11, 2012, while the text “05/04/64” would be interpreted as May 4, 1964. During parsing, only strings consisting of exactly two digits will be parsed into the default century. Any other numeric string, such as a one digit string, a three or more digit string, or a two digit string that is not all digits (for example “-1”), is interpreted literally. Therefore, “01/02/3” or “01/02/003” are parsed, using the same pattern, as Jan. 2, 3 AD. Likewise, “01/02/-3” is parsed as Jan. 2, 4 BC.
Month: If the number of pattern letters is one or two, the month is interpreted as Number, if it is 3 or more, it is interpreted as Text. Therefore, if the month is interpreted as Number or Text, the applicable Number/ Text interpretation rules apply (see above). For example: 1 letter will be a Number that is not padded, 2 letters will be a Number that is padded, 3 letters will be a Text using the abbreviated form and 4 or more letters will be a Text using the long form.
General time zone: For formatting, the time zone is handled as Text if it has a name. If not, it is given as a GMT offset value in the format “GMT[+|-]HH:MM”, where “HH” is the hours between 0 and 23 (one or two digits, may be zero-padded to the left) and “MM” is the minutes between 00 and 59 (always two digits, zero-padded to the left if necessary). For example, “GMT+8:00”, “GMT+08:00”, “GMT-12:45”.
For parsing, see “Time zone parsing” below.
RFC 822 time zone: For formatting, the RFC 882 4-digit time zone format is used:“
[+|-]HHMM”, where “HH” is the hours as two digits, between 00 and 23 (zero-padded to the left if necessary) and “MM” is the minutes as two digits, between 00 and 59 (zero-padded to the left if necessary).
For parsing, see “Time zone parsing” below.
Time zone parsing: For parsing of a time zone, it does not matter if the format pattern specifies a “General time zone” or a “RFC 822 time zone”: In both cases, all three types of time zone specifications are accepted:
 
Examples
The following examples show how date and time patterns are interpreted in the default U.S. locale with the “U.S. Pacific Time” time zone. The given date and time are “2001-07-04 12:08:56” local time in that time zone.
Predefined Locales
When specifying a locale to be used for date/time formatting in “ToDate” or for parsing in “ToMillis”, you can either specify the specific ISO codes of the desired country and language (see the description of “ToDate” and “ToMillis), or you can simply specify one of the following predefined locale names, which stands as a shortcut for the given language and country combination: