SEE ALSO:
Brief summary of this article:
In this article we gathered examples of useful Advanced Filters for Views and Visual Reports that help to select, hide or highlight data by dates such as ones when an entity was created, started or completed. These filters are extremely helpful in tracking due dates and highlighing expired entities.
The filters may be applied to the entities that have assigned dates. Examples of such entities are User Stories, Bugs, Tasks, Features, Epics, Requests, and Projects.
Filters specific to Releases, Sprints (Iterations), Team Iterations are described in other article: Filters for Releases, Sprints (Iterations) and Team Iterations.
The following fields of entities are available:
- CreateDate - Date an entity was created
- StartDate - Date an entity was started (for instance, its state was changed to In Progress) - for Assignable entities. Manually set date when the scope is planned for start - for work scopes.
- EndDate - Date an entity was completed (for instance, its state was changed to Done) - for Assignable entities. Manually set date when the scope is planned for end - for work scopes.
- PlannedStartDate - Manually set date when the entity is planned for start
- PlannedEndDate - Manually set due date
- ForecastEndDate - End date automatically predicted on current progress if elapsed time is greater than one week and progress is greater than 10%
- ModifyDate - Last time entity was modified. "Add Comment" action does not change this field. LastCommentDate field is updated instead.
- LastCommentDate - Time last comment was submitted to an entity
- LastStateChangeDate - Last time state of entity was changed
| Entity | Filter | Result |
|---|---|---|
| Assignable | ?CreateDate > '01-Feb-2017' | entities created after February 1, 2017 |
| Assignable | ?EndDate <= '01-Mar-2017' | entities completed before March 1, 2017, inclusively |
| Assignable | ?EndDate is None | incomplete entities |
| Assignable | ?EndDate is not None | completed entities |
| Assignable | ?StartDate is None | entities that are not started yet |
| Assignable | ?StartDate is not None | started entities |
Keywords supported in filters
Today keyword represents current date. You can compare other dates with it in filters. You can construct relatively shifted dates in the past or in future and use them in comparison.
| Entity | Filter | Result |
|---|---|---|
| Assignable | ?CreateDate == TODAY - 1(days) | entities created yesterday |
| Assignable | ?EndDate > TODAY - 7(days) | entities closed within last 7 days |
| Assignable | ?PlannedEndDate >= TODAY and PlannedEndDate <= TODAY + 30(days) | entities planned to be done within next 30 days |
It is not possible to refer to the calendar weeks, months, quarters and years in the filters so far. You can only filter by exact dates (e.g. 01-01-2017) and shift ranges (e.g. last 30 days).
More examples
The filters for dates can be effectively combined with the filters based on current state or progress of your entities. To combine filters, use the AND, or OR operators with brackets.
| Entity | Filter | Result |
|---|---|---|
| Assignable | ?EntityState.IsFinal is False or EndDate >= Today-7(days) | open entities together with recently closed ones |
| Assignable | ?EntityState.IsFinal is False or (EndDate >= Today-7(days) and EntityState is 'Rejected') | all open entities together with recently closed ones in 'Rejected' final state only |
| Assignable | ?Progress < 1 and PlannedEndDate <= Today - 2(days) | entities having Progress percentage less than 100% that should have been done two days ago |
| Assignable | ?EndDate is None and PlannedEndDate >= Today - 7 (days) and PlannedEndDate < Today - 2 (days) | entities that were delayed for 2-7 days |
| Assignable | ?EndDate is None and PlannedEndDate < Today - 7 (days) | entities that were delayed for more than a week |
| Assignable | ?EndDate is None and Iteration.EndDate > Today and Iteration.EndDate <= Today +7(days) | incomplete entities that have to be done within next 7 days because a current sprint (iteration) ends soon |
| Assignable | ?EndDate is None and Release.EndDate > Today and Release.EndDate <= Today +30(days) | incomplete entities that have to be done within next 30 days because a current release ends soon |
| Assignable | ?LastStateChangeDate < Today - 14(days) and EntityState.Name == 'In Dev' | entities which have been stuck in the 'In Dev' state for more than 2 weeks |
Filters for lanes (rows and columns)
When one of the lanes in your view setup represents date axis, it is possible to customize length of displayed range of detailed dates. Apply filter to the axis for this purpose.
Known issues
The filters for exact date match with current day do not work properly due to known bug. For example, ?PlannedStartDate is Today filter is not supported. Use a pair of boundary filters instead: ?PlannedEndDate > Today and PlannedEndDate <= Today + 1(days)
Still have a question?
We're here to help! Just contact our friendly support team.
Find out more about our APIs, Automation Rules, Mashups and custom extensions.