# Custom Formulas Syntax

Custom formulas used in custom formula metrics and calculated custom fields obtain data from single or multiple sources, optionally perform some action on them and then present the calculated result as a value of a custom field.

# Values and Properties

``````[User Story]
Effort
EntityState.Name
Feature.Priority.Importance
``````

# Basic Calculations

``````[User Story]
TimeSpent + TimeRemain
Effort * Math.Pi

[Feature]
InitialEstimate * 2

[Request]
StartDate - CreateDate
``````

Use round brackets to determine computation order.

# Conditional Statements

Use IIF operator to build a conditional If-Then-Else statement. It takes 3 parameters. The first parameter is the condition. If it is matched (boolean result is True), then the result is provided from the second parameter, otherwise from the third one.

``````[User Story]
IIF(EntityState.IsFinal, Effort, 0)
IIF(TimeSpent > 0, TimeSpent, Effort)
``````

Nested IIFs are supported. Use inner IIF operators to build nested conditions.

``````[User Story]
IIF(EntityState.Name == 'In Process', 10, IIF(EntityState.Name == 'Ready for Testing', 60, IIF(EntityState.Name == 'QA Test', 85, IIF(EntityState.Name == 'QA Complete', 90, IIF(EntityState.Name == 'Ready for Deployment' or EntityState.IsFinal == True, 100, 0)))))
``````

Supported Operators

Operator

Meaning

Example

=

==

equal to

!=

not equal to

<

less than

greater than

>=

greater or equal to

<=

less or equal to

&&
and

and

||
or

or

Use round brackets to determine computation order in complex logical expressions.

Data Comparison

Value 1

Value 2

Formula

Text / String

Text / String

Name = "ABC"

Number

Number

Boolean

Boolean

IsPrivate = True

Date [without exact time]

Date [without exact time]

EndDate.Date = PlannedEndDate.Date

Date with exact time

Date with exact time

ModifyDate = LastStateChangeDate

Any

Empty

PlannedEndDate = null

# Aggregate Calculations

Count

``````[Feature]
UserStories.Count
``````

Conditional Count

``````[Feature]
UserStories.Where(EntityState.IsFinal == False).Count
``````

Aggregations

``````[Feature]
UserStories.Sum(Effort)
``````

Conditional Aggregations

``````[Feature]
UserStories.Where(EntityState.IsFinal == False).Sum(Effort)
``````

# Conversions

To numbers

``````[Feature]
Convert.ToInt32(CustomValues.Text('CustomFieldName'))
Convert.ToDecimal(CustomValues.Text('CustomFieldName'))
Convert.ToDouble(CustomValues.Text('CustomFieldName'))
``````

To Boolean

``````[Feature]
Convert.ToBoolean(CustomValues.Text('CustomFieldName'))
``````

To Text

``````[Feature]
Convert.ToString(12345)
CreateDate.ToString()
``````