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
| 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 | Tasks.Count = 0 |
| 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()
Updated about 5 years ago