Calculated Custom Fields

It is possible to create your own custom calculations with a Custom Field based on a specified formula. To do so, create a Custom Field with a Calculated Field Type and specify Calculation Formula.

The Calculated Custom Field (CCF) can have one of the following type: Number, Text, Date and Check Box. This can be determined as a result of the Calculation Formula.

The CCF can also have Units of Measurements, which would be shown near the calculated value. You can set it up if the result of the Calculation Formula is Number.

You can operate with CCFs the same as with simple Custom Fields. Within Entity details, the CCFs are shown in the Custom Fields section.

You can select them in the Customize Cards section to see them on the View. You can use them in Filters as well.

Calculation Formula

In the Calculation Formula you can use:

• Fields of Entity
• Custom Fields of Entity (except Calculated)
• Fields of Parent Entities (for example, Feature.Effort for User Story CCF)
• Aggregations of Fields in child collections of Entity (for example, Tasks.Sum(TimeSpent) for User Story CCF)

To learn what properties you can use, check this reference.

If you need to use the Drop Down field value in the formula then use IIF operator for that. It takes 3 parameters. If the condition (the first parameter) is matched, then the result is the second parameter, otherwise the third parameter would be returned as a result. You also can use IIF operator as the parameter of another IIF operator.

The Entity view would be shown like this:

Work with dates

If you need to increase/decrease the Entity date field, you should use AddDays method.

If you need to refer a fixed date, you need to use Convert.ToDateTime method.

You can also use DateTime.Today when you need the current date in calculations.

It is possible to subtract a date from another one and get the duration in days and months.

(EndDate-StartDate).TotalDays
(EndDate-CreateDate).TotalMonths
(EndDate-CreateDate).TotalHours
(EndDate-CreateDate).TotalMinutes

Work with numbers

To round up fractional numbers to nearest integer ones use Math.Round, Math.Floor, Math.Ceiling methods from .NET Framework.

Math.Round((PlannedEndDate-PlannedStartDate).TotalMonths)
Math.Ceiling(TimeSpent/Effort)
Math.Floor((Assignments.Count)/2)

Collections in Calculated Custom Fields

For example, you have a User Story with several Bugs and you want to see how much effort is required for the related Bugs. Now you can create “Total Bugs Effort” custom field for User Story and use formula:

Bugs.Sum(Effort)

Or you can calculate effort of all related entities. Let’s say, you have a feature in some portfolio (project) and you link other features from other portfolios (projects) to this feature.

InboundAssignables.Sum(Effort)

With this release all available resource collections are now supported. To learn more about collections, check the resource description.

In order to get totals for numeric Custom Field values inside some collection you may try to use Custom Reports as a workaround. Build a report by child entity type and group entities by parent entity types. You will get totals by each parent entity calculated automatically.

More examples

You can set up a formula which returns True or False. In this case a Calculated Custom Field of the CheckBox type will be created.

Text is another possible type.

One more example:

You can use collections with aggregation functions. Such as MIN, MAX, AVG, SUM, COUNT.

It is possible to build a long string that combines several values together. Say, the following example defines a read-only text field that shows a numeric ID and a Name for a parent Epic of a User Story:

Feature.Epic.ID.ToString() + " " + Feature.Epic.Name

Filtering and Aggregation in collections

Basic syntax is the following:

Collection.where(Filter).Aggregation(Field)

For example, for a Team entity it is possible to sum remaining effort for all assigned user stories that should be done in the next 7 days:

UserStories.where(PlannedEndDate >= DateTime.Today && PlannedEndDate <= DateTime.Today.AddDays(7)).SUM(EffortToDo)

For User entity it is possible to summarize Time spent values with Billable checkbox:

Times.Where(CustomValues["Billable"].ToString=="true").SUM(Spent)