A Calculation Operators and Expressions

Calculation Operators

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

Avg

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)

Ceiling

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)

Concat

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},"' />")))

Count

Returns a count of all non-null values. Count can accept one field parameter of any type.

Syntax:

Count(Field)

Example:

Count(SaleComplete)

CountDistinct

Returns a count of distinct values in a field. CountDistinct can accept one field parameter of any type.

Syntax:

CountDistinct(Field)

Example:

CountDistinct(Salesperson)

DateAdd

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

DayName

Returns the day name for a date. DayName accepts one field parameter of type DateTime.

Syntax:

DayName(DateTime)

Example:

DayName({Last Modified})

DayOfMonth

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

DayOfWeek

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

DayOfYear

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

Floor

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)

Hour

Returns the hour value in the range 0-23. Hour accepts one field parameter of type DateTime.

Syntax:

Hour(DateTime)

Example:

Hour({Last Modified})

If

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)

IfNull

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)

Length

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

Lower

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

Max

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

Min

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

Minute

Returns the minute value in the range 0-59. Minute accepts one field parameter of type DateTime.

Syntax:

Minute(DateTime)

Example:

Minute({Last Modified})

Month

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

MonthName

Returns the month name for a date. MonthName accepts one field parameter of type DateTime.

Syntax:

MonthName(DateTime)

Example:

MonthName({Last Modified})

Now

Returns the current date and time. Now does not accept any parameters.

Syntax:

Now()

Example:

DateAdd(Now(), 0, 0, 7, 0, 0, 0, 0)

PercentOfTotal

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)

Power

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)

Quarter

Returns the quarter value in the range 1-4. Quarter accepts one field parameter of type DateTime.

Syntax:

Quarter(DateTime)

Example:

Quarter({Last Modified})

Repeat

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)

Replace

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

Round

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.

Second

Returns the second value in the range 0-59. Second accepts one field parameter of type DateTime.

Syntax:

Second(DateTime)

Example:

Second({Last Modified})

Substring

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"

Sum

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)

Switch

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:

TrimEnd

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

TrimStart

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

Upper

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

Week

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

Year

Returns the year value in the range 1000-9999. Year accepts one parameter of type DateTime.

Syntax:

Year(DateTime)

Example:

Year({Last Modified})