Jira adds up logged time and estimates only one level up: time logged on a Sub-task shows in the Story's Σ Time Spent, but the Epic never sees it. A team that logs its work on Sub-tasks therefore gets an Epic that looks almost untouched. With CelesteCS Math for Confluence a Confluence page can show the real totals per Epic, read live from Jira on every page view, without automation rules or extra custom fields.
Works on Confluence Cloud and Data Center. On Cloud insert CelesteCS Numerical Expression Cloud; the formulas are the same.
The idea
Each Story already carries the sum for itself and its Sub-tasks in two aggregate fields: aggregatetimespent and aggregatetimeoriginalestimate (in seconds). So the Epic total is simply the sum of these fields over the Epic's Stories. The JIRAISSUEFIELDVALUESBYFIELDID function reads a field from every issue that matches a JQL query, and SUM adds the values up.
1. One row per Epic
Create a table and put a Numerical Expression macro into each calculated cell:
| Column | Formula |
|---|---|
| Estimate (h) | SUM(JIRAISSUEFIELDVALUESBYFIELDID("parent = APP-100", "aggregatetimeoriginalestimate", 0)) / 3600 |
| Logged (h) | SUM(JIRAISSUEFIELDVALUESBYFIELDID("parent = APP-100", "aggregatetimespent", 0)) / 3600 |
| Progress % | ROUND(SUM(JIRAISSUEFIELDVALUESBYFIELDID("parent = APP-100", "aggregatetimespent", 0)) / SUM(JIRAISSUEFIELDVALUESBYFIELDID("parent = APP-100", "aggregatetimeoriginalestimate", 1)) * 100, 0) |
The third argument is the value used when a field is empty: 0 for the sums, and 1 in the divisor so that an Epic without any estimate does not cause a division by zero.
2. What it looks like
A test Epic with two Stories: Payment form (8 h estimate, 1 h logged on the Story itself, Sub-tasks with 4 h + 3 h estimates and 5 h + 2 h logged) and Order summary API (4 h estimate, one Sub-task with a 6 h estimate and 6.5 h logged). Jira's own Σ Time Spent on the Epic shows only 1 h. The page shows:
| Epic | Estimate (h) | Logged (h) | Progress % |
|---|---|---|---|
| Checkout redesign | 25 | 14.5 | 58 |
| Reporting module | 6 | 2 | 33 |
Variations
- Time logged directly on the Epic is not part of its Stories' fields. Add it with
+ SUM(JIRAISSUEFIELDVALUESBYFIELDID("key = APP-100", "timespent", 0)) / 3600. - One level higher (a Milestone or Initiative above several Epics): list the Epics in the query, for example
parent in (APP-100, APP-120, APP-135). - Data Center with Epic Link: on Jira versions where Stories are attached to Epics with the Epic Link field, use
"Epic Link" = APP-100instead ofparent = APP-100. - Remaining work:
aggregatetimeestimategives the remaining estimate of a Story and its Sub-tasks.
Because the values are read when the page is opened, the table is always current: there is nothing to recalculate when someone logs time. Related: Building a live Jira progress dashboard with CelesteCS Math and Original estimate vs. logged time: a live Confluence report from Jira.
Try it in your own Confluence: CelesteCS Math for Confluence on the Atlassian Marketplace – free trial for Cloud and Data Center.
Math saves you time? A review on the Marketplace helps other teams find it.