This appendix provides the syntax and examples for operators and expressions you can use in a calculation when creating a calculated field in a data object or report view.
It contains the following topics:
Table A-1 Operators Used in Calculated Fields
| Operator | Function |
|---|---|
|
+ (plus sign) |
Add |
|
- (minus sign) |
Subtract |
|
* (asterisk) |
Multiply |
|
/ (slash) |
Divide |
|
% (percent sign) |
Modulus |
|
() (parentheses) |
Parentheses determine the order of operations |
|
&& (double ampersand) |
Logical AND |
|
! (exclamation point) |
Logical NOT |
|
|| (double pipe) |
Logical OR |
|
== (double equal sign) |
Equality |
Field names containing any special characters, such as the operators listed in Table A-1 double quotation marks, or spaces, must be surrounded with curly braces {}. If field names contain only numbers, letters and underscores and begin with a letter or underscore they do not need curly braces. For example, if the field name is Sales, the correct way to enter this in a calculation is Sales. However, if the field name is Sales+Costs, the correct way to enter this in a calculation is {Sales+Costs}.
Double quotes must be escaped with another set of double quotes if used inside double quotes. For example, Length("""Hello World, "" I said").
Returns the average of all values for the given field. Avg can accept one field parameter of type Integer, Float, or Decimal.
Syntax:
Avg(Number)
Example:
Avg(Revenue)
Returns the smallest integer greater than or equal to the specified value. Ceiling(2.9) returns 3 and Ceiling(-2.3) returns -2. Ceiling can accept one field parameter of type Integer, Float, or Decimal or a numeric value may be entered.
Syntax:
Ceiling(Number)
Examples:
Ceiling(Total) Ceiling(3.7)
Concatenates several strings into one. Concat can accept multiple field parameters of type String, or string values may be entered.
Syntax:
Concat(String1,String2, ... ,StringN)
This example uses Concat with If (see If) on strings of HTML to load images in a calculated HTML field (see Using HTML in Calculations for more information):
If ({Excess Capacity} > 16)
Then (Concat ("<img src='../Images/round_green16px.gif' alt='",
{Excess Capacity},"' />"))
Else (If ({Excess Capacity} < 1) Then
(Concat("<img src='../Images/round_red16px.gif' alt='",
{Excess Capacity},"' />"))
Else (Concat("<img src='../Images/round_amber16px.gif' alt='",
{Excess Capacity},"' />")))
Returns a count of all non-null values. Count can accept one field parameter of any type.
Syntax:
Count(Field)
Example:
Count(SaleComplete)
Returns a count of distinct values in a field. CountDistinct can accept one field parameter of any type.
Syntax:
CountDistinct(Field)
Example:
CountDistinct(Salesperson)
Adds an offset to the field value. The first parameter for DateAdd must be a field of type DateTime, and the last seven parameters maybe a field of type Integer or an integer value. Zeros may be used where no offset is needed.
Syntax:
DateAdd(DateTime, Years, Months, Days, Hours, Minutes, Seconds, Milliseconds)
Example:
DateAdd({Last Modified}, 0, 0, 7, 0, 0, 0, 0)
//adds 7 days to the Last Modified value
DateAdd({Last Modified}, 0, 0, DaysToFollowup, 0, 0, 0, 0)
//adds DaysToFolowup number of days to the Last Modified value
Returns the day name for a date. DayName accepts one field parameter of type DateTime.
Syntax:
DayName(DateTime)
Example:
DayName({Last Modified})
Returns the day of the month for a date, in the range 1 to 31. DayOfMonth accepts one field parameter of type DateTime.
Syntax:
DayOfMonth(DateTime)
Example:
DayOfMonth({Last Modified})
Returns the day of the week for a date, in the range 1 to 7. DayOfWeek accepts one field parameter of type DateTime.
Syntax:
DayOfWeek(DateTime)
Example:
DayOfWeek({Last Modified})
Returns the day of the year for a date, in the range 1 to 366. DayOfYear accepts one field parameter of type DateTime.
Syntax:
DayOfYear(DateTime)
Example:
DayOfYear({Last Modified})
Returns the largest integer less than or equal to the value of the specified field. Floor(2.9) returns 2 and Floor(-2.3) returns -3. Floor can accept one field parameter of type Integer, Float, or Decimal or a numeric value may be entered.
Syntax:
Floor(Number)
Examples:
Floor(Sales) Floor(46.75)
Returns the hour value in the range 0-23. Hour accepts one field parameter of type DateTime.
Syntax:
Hour(DateTime)
Example:
Hour({Last Modified})
Creates an If-Then-Else statement. If can accept fields, expressions, and values of any type as parameters.
Syntax:
If(x) Then(y) Else(z)
Example:
If(Sum(Quantity) > Max(Total)) Then(1) Else(2)
Returns a specified value, y, if the test value, x, is null. IfNull accepts two parameters that can be fields of any type or values of any type. However, the two parameters must be of the same data type.
Syntax:
IfNull(x,y)
Example:
IfNull(Quantity, 0)
Returns the length of the string. Length accepts one parameter that can be a field of type String, a string value in quotes, or an expression containing strings or fields of type String.
Syntax:
Length(String)
Example:
Length(Description)
Length("string")
Length(Concat(Description,"Description"))
Converts the string to lowercase letters. Lower accepts one parameter that can be a field of type String, a string value in quotes, or an expression containing strings or fields of type String.
Syntax:
Lower(String)
Example:
Lower(Description)
Lower("Description")
Lower(Concat(Description,"Description"))
Returns the maximum value of the specified field or expression. Max accepts one field parameter of any type, or another valid expression.
Syntax:
Max(x)
Example:
Max(Quantity) Max(Concat(Description," overstock"))
Returns the minimum value of the specified field or expression. Min accepts one field parameter of any type, or another valid expression.
Syntax:
Min(x)
Example:
Min(Quantity) Min(Concat(Description," overstock"))
Returns the minute value in the range 0-59. Minute accepts one field parameter of type DateTime.
Syntax:
Minute(DateTime)
Example:
Minute({Last Modified})
Returns the month value for a date in the range 1-12. Month accepts one field parameter of type DateTime.
Syntax:
Month(DateTime)
Example:
Month({Last Modified})
Returns the month name for a date. MonthName accepts one field parameter of type DateTime.
Syntax:
MonthName(DateTime)
Example:
MonthName({Last Modified})
Returns the current date and time. Now does not accept any parameters.
Syntax:
Now()
Example:
DateAdd(Now(), 0, 0, 7, 0, 0, 0, 0)
Returns the percent the value represents of the total values for the specified field. PercentOfTotal accepts one field parameter of type Integer, Float, or Decimal.
Syntax:
PercentOfTotal(Number)
Example:
PercentOfTotal(Quantity)
Returns one value, x, raised to the power of the second value, y. Power accepts two parameters that can be fields of type Integer, Float, or Decimal, or they can be numeric values.
Syntax:
Power(Number,Number)
Example:
Power(Quantity, 2)
Returns the quarter value in the range 1-4. Quarter accepts one field parameter of type DateTime.
Syntax:
Quarter(DateTime)
Example:
Quarter({Last Modified})
Repeats a string for the specified number of times. Repeat accepts two parameter, the first of which may be a string value or a field of type String, the second of which may be an integer value or a field of type Integer. Either parameter can use an expression that returns a string for the first parameter and an integer for the second value.
Syntax:
Repeat(String,Integer)
Example:
Repeat("string", 5)
Repeat(Description, 2)
Repeat(Description, Quantity)
Repeat(Concat(Description," overstock"),Quantity+2)
Returns a string, x, with all occurrences of the string, y, replaced by the string z. Replace accepts three field parameters of type String, or string values.
Syntax:
Replace(String,String,String)
Example:
Replace(Description, "ing", "tion")
Rounds the specified value in the first parameter to the number of decimal places specified in the second parameter, rounding up if the number in the N+1 decimal place is 5 or greater, and rounding down otherwise. Round accepts two parameters that can be fields of type Integer, Float, or Decimal, or numeric values.
Syntax:
Round(Number,N)
Example:
Round(Sales,2)
In this example, if Sales value is 12.345, it is rounded to 12.35.
Returns the second value in the range 0-59. Second accepts one field parameter of type DateTime.
Syntax:
Second(DateTime)
Example:
Second({Last Modified})
Returns a substring z characters long from string x, starting at position y. Substring requires three parameters, the first of which must be a string value, or a field of type String, and the second and third of which must be an integer or field of type Integer.
Syntax:
Substring("source_string", start_position, substring_length)
Example:
Substring("some string", 6, 3)
// returns "str"
Returns a summation of all values for the specified field. Sum accepts one field parameter of type Integer, Float, or Decimal.
Syntax:
Sum(Number)
Example:
Sum(Total)
Creates a Switch statement. Switch can accept fields, expressions, and values of any type as parameters.
Syntax:
Switch(w) Case(x):(y) Default(z)
Example:
Trims the whitespace characters (space, tab, carriage return, line feed, page feed, form feed, and so on) from the end of the string. TrimEnd accepts one field parameter of type String. You can also enter an expression that returns a string value.
Syntax:
TrimEnd(String)
Example:
TrimEnd(Description) TrimEnd(Concat(Description,Subcategory))
Trims the whitespace characters (space, tab, carriage return, line feed, page feed, form feed, and so on) from the beginning of the string. TrimStart accepts one field parameter of type String. You can also enter an expression that returns a string value.
Syntax:
TrimStart(String)
Example:
TrimStart(Description) TrimStart(Concat(Description,Subcategory))
Converts a string to uppercase letters. Upper accepts one parameter of type String. You can also enter an expression that returns a string value.
Syntax:
Upper(String)
Example:
Upper({License Plate Number})
Returns the week for a DateTime value, in the range 0 to 53, because there might be the beginning of a week 53, where Sunday is the first day of the week. Week 1 is the first week with a Sunday in this year.
For example, in the year 2006, January 1st is a Sunday, so there is no week 0. The year starts with week 1 and continues to week 53. Week 53 of 2006 includes only one day, which is December 31st (also a Sunday). The Monday through Saturday following this (January 1-6 of 2007) are in week 0 of 2007.
Syntax:
Week(DateTime)
Example:
Week({Last Modified})
Returns the year value in the range 1000-9999. Year accepts one parameter of type DateTime.
Syntax:
Year(DateTime)
Example:
Year({Last Modified})