Reproducing Reported Numbers

"Where does this number come from?" is one of the most common questions asked of the Agility API, and it usually arrives as a discrepancy: a count on a dashboard does not match the count a query returns, and nobody can say which is wrong.

Usually neither is. They are answering slightly different questions.

The Numbers in the Product Are Queries

The columns and roll-ups you see in Agility are defined as attribute-path expressions — the same syntax described in the query grammar, available to you through the API. A "done count" on a board is something close to:

Timebox.Workitems:PrimaryWorkitem[EffectiveAssetState='Closed';Scope=$Scope;Team=$Team].@Count

That is a downcast, an inline filter with three terms, a filter-context variable and an aggregation — nothing you cannot write yourself. So reproducing a reported figure is not guesswork; it is a matter of matching the expression, and the mismatches almost always come down to a small number of recurring causes.

Start with the State Attribute

The most common cause of a mismatch is using the wrong state attribute. There are two, both read-only and both of type State:

Attribute	What it reflects
AssetState	The asset's own lifecycle — active, closed, deleted
EffectiveAssetState	AssetState or the rollup of the item's workflow status

EffectiveAssetState is a calculated attribute, and the rule is a substitution rather than a simple OR:

EffectiveAssetState =
    AssetState = Active  ->  Status#.RollupState, or Future when that is null
    otherwise            ->  AssetState

In words: while an item is Active, its workflow status replaces its state entirely. Once it is anything else — Future, Closed, Dead, Deleted — its own AssetState stands and the status is ignored.

The practical case everyone meets first is a Story sitting in a "Done" or "Accepted" column. Its AssetState is still Active — nothing closed it as an asset — but its status rolls up as closed, so the product counts it as complete. Filter on AssetState='Closed' and you miss every one of them.

warning

An Active item with no status reads as Future, not Active

The null fallback is easy to miss and it is not intuitive. If an item is Active and has no Status assigned, its RollupState is null and EffectiveAssetState becomes Future.

So a freshly created, unstarted work item is effectively Future. A filter of EffectiveAssetState='Active' will not return it, and a count of "not yet started" built on AssetState will disagree with one built on EffectiveAssetState for exactly these items.

danger

The status is read as of now, even inside a history query

The calculation reads Status#.RollupState — note the # now-only operator. It resolves the status's rollup as it is currently, not as it was at the moment being examined.

That matters whenever you filter history on EffectiveAssetState. If a status was later reconfigured to roll up differently, historical rows are evaluated against the new rollup, so a replay of past transitions can shift under you without any work item having changed.

If you need the rollup as it stood at the time, select Status.RollupState without the # and compare it yourself.

Two further consequences worth knowing:

• Status names do not matter; rollups do. Two instances can use identical status names and still report differently. When a count is wrong on one instance only, compare Status.RollupState, not Status.Name.
• The calculation can be switched off. A SuppressEffectiveStateCalculation setting makes EffectiveAssetState return plain AssetState. It is per-instance, so a query that reconciles on one deployment can disagree on another with no change to the data.

The product's own reports use EffectiveAssetState in most places where you would reach for AssetState. If your count is close but consistently low, this is almost certainly why.

warning

!= on a state attribute is not a negation

Both are State-typed, so EffectiveAssetState!='Closed' compiles to < 128 — it excludes Dead and Deleted items as well as Closed ones. It means "before Closed", not "not Closed".

That is usually what a report wants, which is why it is easy to copy without noticing. See State attributes.

"Inactive" and "Closed" Are the Same Thing

The vocabulary is genuinely inconsistent here, and it trips people up in both directions — looking for a Close operation that does not exist, or expecting an Inactive state that does not either.

The operation is almost always Inactivate. The resulting state is Closed.

Counted across a live instance's metadata:

Operation	Asset types	Notes
Inactivate	72	Effectively everything — Story, Defect, Epic, Issue, Goal, and every list type
Close	1	Timebox only
QuickClose	4	Defect, Story, Task, Test — closes without the usual workflow steps
Reactivate	25	The inverse of Inactivate

No asset type offers both Inactivate and Close. So if you are closing a Timebox you invoke Close; for anything else you invoke Inactivate — and in both cases the asset ends up with an AssetState of Closed.

There is no Inactive value in the state model at all. AssetState runs Future, Active, Closed, Dead, Deleted; "inactive" is only ever the name of the verb.

What this means for a query

Filter on the state, not the operation name. AssetState='Closed' and EffectiveAssetState='Closed' are the right predicates regardless of which operation put the asset there.

Where "inactive" does appear as an attribute — IsInactive on some types — treat it as a separate boolean flag and check meta.v1 for what the type actually exposes rather than assuming it mirrors AssetState.

Inactivate results in AssetState of Closed — see operations.

The Date Attributes Are Not Interchangeable

DoneDate, ClosedDate and RetireDate are all read-only dates, and they do not mean the same thing. Filtering on the wrong one gives a plausible number that drifts from the report.

Before assuming which one a figure uses, check the type in meta.v1 and try the alternatives — a discrepancy that changes when you swap the date attribute has found its own cause.

And if the figure is bucketed by sprint, iteration or any other range, check the boundary convention before anything else: EndDate is exclusive, stored as midnight of the day after the range ends, so a filter of EndDate<= the date shown in the UI silently drops a period. See Dates and date ranges.

Worked Example: Velocity by Date

Counting the items completed in each of a series of intervals looks simple:

{
  "from": "PrimaryWorkitem",
  "select": ["Name", "Status.Name", "EffectiveAssetState", "DoneDate"],
  "filter": [
    "AssetState!='Dead';EffectiveAssetState='Closed';Scope[AssetState!='Closed'];Scope.ParentMeAndUp=$ScopeRoot;DoneDate>=$StartDate;DoneDate<=$EndDate"
  ],
  "with": {
    "$ScopeRoot": "Scope:1234",
    "$StartDate": "2020-01-02T00:00:00",
    "$EndDate": "2020-01-09T00:00:00"
  },
  "page": { "size": 1000, "start": 0 }
}

Three things in that filter are worth noticing, because they recur in every report-reproduction query:

• Scope[AssetState!='Closed'] — an inline filter on the relation, excluding items in closed projects. Reports almost always do this; hand-written queries almost always forget it.
• Scope.ParentMeAndUp=$ScopeRoot — matches the whole project subtree, not just direct children.
• A filter-context variable for each bound, so the same query shape can be reissued per interval without rebuilding the string.

Run it once per interval and you have a velocity series.

danger

Items can become un-done, and a DoneDate filter will not tell you

This is the discrepancy that sends people to support.

DoneDate reflects the item's current completion, so a "done in this week" count built on it silently omits an item that was completed that week and later reopened — and it includes an item completed earlier and re-completed since, depending on how the date is maintained.

A report that counts completions as they happened must read the transitions, not the current value.

Worked Example: Effort and Hours Worked

Effort is not an attribute you can select on a workitem directly — it is the sum of the Actual records logged against it. Each time a team member records time, the system creates an Actual carrying Value, Date, Member, Workitem and Scope (required).

Actuals usually go on Tasks. To total a Story's actuals, including its Tasks, select ChildrenMeAndDown.Actuals.Value.@Sum.

Actuals recorded against a single workitem — bounded by the workitem's own key:

{
  "from": "Actual",
  "where": { "Workitem": "Task:1217" },
  "select": ["Date", "Value", "Member.Name"],
  "page": { "size": 100, "start": 0 }
}

Hours worked by member, within a date range — sum Value per Member, using filter-context variables for the bound, the same pattern as the velocity example above:

{
  "from": "Member",
  "select": ["Name", "Actuals[Date>=$StartDate;Date<$EndDate].Value.@Sum"],
  "with": { "$StartDate": "2026-08-01", "$EndDate": "2026-09-01" },
  "page": { "size": 50, "start": 0 }
}

Progress on an Epic — the same downcast idiom from Relations, applied to Estimate instead of a count, gives closed vs. total estimate for everything under an Epic. Bounded by the Epic's own key, so no page is required:

{
  "from": "Epic",
  "where": { "ID": "Epic:1207" },
  "select": [
    "Name",
    "SubsAndDown:PrimaryWorkitem[EffectiveAssetState='Closed'].Estimate.@Sum",
    "SubsAndDown:PrimaryWorkitem[AssetState!='Dead'].Estimate.@Sum"
  ]
}

Divide the two sums for a percent-complete figure.

Counting Transitions Instead of Current State

History is queryable as a relation, and Prior gives you the value immediately before each change. A state transition is therefore expressible directly:

Prior.EffectiveAssetState!='Closed';EffectiveAssetState='Closed'

That is "became done". Reverse it for "became not done":

Prior.EffectiveAssetState='Closed';EffectiveAssetState!='Closed'

Select both sides of every relevant change, ordered, and you can replay an item's completion history rather than inferring it:

{
  "from": "History",
  "select": ["Prior.EffectiveAssetState", "EffectiveAssetState", "ChangeDate"],
  "filter": "ChangeDate<=$AsofDate;((Prior.EffectiveAssetState!='Closed';EffectiveAssetState='Closed')|(Prior.EffectiveAssetState='Closed';EffectiveAssetState!='Closed'))",
  "sort": ["+ChangeDate"]
}

Note the grouping: (A;B)|(C;D) — ; is AND, | is OR, and parentheses group. See boolean operators.

Process the changes in order. An item that was completed, reopened and completed again produces three rows, and only the last one determines whether it counts as done at the end of the period. Taking the first match, or counting rows, gives the wrong answer for exactly the items that caused the question.

Reconstructing an as-of-Date View

The same idea answers "what did this look like on a date in the past" without asof:

History[Scope=$ScopeContext;EffectiveAssetState='Closed';ChangeDate<=$AsofDate;(RetireDate>=$AsofDate|-RetireDate)]

Read that as: a version existed, in this scope, that was Closed, changed on or before the date, and either had not retired by then or never retired at all. The -RetireDate term is a unary existence assertion — "has no RetireDate".

Compare with asof, which asks the server for the state at a moment. asof is simpler; the history filter is more flexible when the condition itself involves change.

A Checklist for Chasing a Discrepancy

When a reported figure and a query disagree, work through these in order:

• Is the report using EffectiveAssetState where you used AssetState?
• Does the report exclude items in closed or dead projects?
• Does it scope to a subtree (ParentMeAndUp) rather than direct children?
• Is it counting current state, or transitions during the period?
• Are you filtering on the same date attribute?
• Is your query paged, and are you reading every page? A count that stops at the first page is a common and silent error.

Related

• Product reports this guide reproduces: the Velocity reports, and the Velocity by Date report — the worked example's product counterpart; Cumulative Flow by Status — the report the transition-counting section approximates.

• Moments and history — the History, Prior and Now relations

• Filter expressions — grouping, unary existence, state comparison

• Query size and performance — paging these queries safely