Visual Reports with Timeline Data Aggregation

When you plan your backlog and define planned dates for future work you may be interested in building a timeline-based report for your backlog. The timeline calculation feature makes such reporting possible.

It is possible to build a report showing a count of entities, or numeric custom field values distributed on a timeline with daily, weekly, monthly, or less precise resolution according to the planned dates of the entities.

Count Summary on Timeline

Entity report: for planned dates

This report for a future time period displays daily / weekly totals count of User Stories and Bugs, according to the planned dates of these items or by planned dates of parent Features.

Historical report: for past dates

This report for a past time period displays daily / weekly / montly totals count of work-in-progress items, according to the dates when these items entered and left selected workflow states.

Count of Features in Progress

Data source filter:

?(EndDate is None or EndDate >= 'Date Range Start') and (EntityState.IsFinal is False and EntityState.Name is not 'Concept')

X axis calculation:

MONTH(TIMELINE([Date of Modification],[Modification Valid To],TRUE))

Count of Requests in Progress, grouped by T-Shirt Size

Data source filter:

?(EntityState is 'Ready for Planning' or EntityState is 'Dev Planned' or EntityState is 'In Development') and CurrentRequest.PriorityTier is not 'Emergency' and CurrentRequest.PriorityTier is not 'Workstream'

X axis calculation:

DAY(TIMELINE([Date of Modification],[Modification Valid To]))

Size data field calculation:

CurrentRequest.CustomValues.Text("TShirt Size")

Custom Field Values Summary on a Timeline

This report for a future time period displays daily / weekly / monthly totals of custom field values for Features / Epics.

More information: Custom Field values summary on Timeline

Known Issues

Normalization Problem

A report that shows total Effort of items with TIMELINE grouping has no sense. Instead, for a report with DAY resolution, you should introduce special numeric custom field that would calculate effort required daily - say, by division of total effort by planned duration of the item. Use the normalized value in your charts.

Empty Dates Problem

By default only entities that have both a timeline start date and end date supplied are included to the chart. When at least one date is empty the entity is hidden on the chart and does not participate in the calculation.

In order to include all entities in the chart you have to introduce your own fields with custom calculations. Check that the date is empty and provide a placeholder date instead of blank value where necessary. For example when Planned End Date is not set you may fill it in with the TODAY value or with some Date set far ahead in the future.

Grouping Problem

In reports with the TIMELINE calculation by date range with WEEK, MONTH, QUARTER, YEAR grouping, an entity is included in a sub-group when its end date is later then the end moment of the group. Otherwise the entity is not included. Let's illustrate this principle on an example:

To make your reporting accurate and to be able to discover intersections of date ranges of your data items properly we recommend not using WEEK, MONTH, QUARTER, YEAR grouping in these kinds of reports. Use the DAY grouping mode instead.

Last Day Problem

In reports with the TIMELINE calculation by date range and DAY resolution, the last day of the date range of an entity is not included in the chart. To make it visible on the chart you have to introduce a custom formula with a 1-day date shift. The only known and supported way to perform this task at the moment is to use custom fields and metrics.

Add a Custom Field (type Date, System) for your entity type named Extended End Date. Add a metric for this field with the formula, depending on your context:

EndDate.AddDays(1)
PlannedEndDate.AddDays(1)

Then use this field in the TIMELINE calculation with the DAY grouping:

DAY(TIMELINE([Start Date],[Extended End Date]))
DAY(TIMELINE([Planned Start Date],[Extended End Date]))