# A Calculation Operators and Expressions

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:

## Calculation Operators

Table A-1 Operators Used in Calculated Fields

Operator Function

+ (plus sign)

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

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

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)

```

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