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
CreateDate.Value.AddHours(48)

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

OperatorMeaningExample
=

==
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 1Value 2Formula
Text / StringText / StringName = "ABC"
NumberNumberTasks.Count = 0
BooleanBooleanIsPrivate = True
Date [without exact time]Date [without exact time]EndDate.Date = PlannedEndDate.Date
Date with exact timeDate with exact timeModifyDate = LastStateChangeDate
AnyEmptyPlannedEndDate = 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()