Jira dashboards show how many issues match a filter now, but a common need is the trend: how many vulnerabilities, bugs or support tickets were open at the end of each of the last weeks, in one chart. With CelesteCS Math for Confluence this is a small table and one chart macro on a Confluence page, recalculated on every page view.
Works on Confluence Cloud and Data Center. On Cloud insert CelesteCS Numerical Expression Cloud and CelesteCS Chart; the formulas are the same.
1. One row per week
Create a two-column table. The first column is the label, the second contains a Numerical Expression macro with the JIRAISSUECOUNT function and a JQL query for that week:
| Week | Formula in the Numerical Expression macro |
|---|---|
| 5 weeks ago | JIRAISSUECOUNT("project = SEC AND type = Vulnerability AND created <= endOfWeek(-5) AND status WAS NOT IN (Done) ON endOfWeek(-5)") |
| 4 weeks ago | JIRAISSUECOUNT("project = SEC AND type = Vulnerability AND created <= endOfWeek(-4) AND status WAS NOT IN (Done) ON endOfWeek(-4)") |
| ... | JIRAISSUECOUNT("project = SEC AND type = Vulnerability AND created <= endOfWeek(-n) AND status WAS NOT IN (Done) ON endOfWeek(-n)") |
| Last week | JIRAISSUECOUNT("project = SEC AND type = Vulnerability AND created <= endOfWeek(-1) AND status WAS NOT IN (Done) ON endOfWeek(-1)") |
| This week | JIRAISSUECOUNT("project = SEC AND type = Vulnerability AND created <= endOfWeek() AND status WAS NOT IN (Done) ON endOfWeek()") |
status WAS NOT IN (Done) ON endOfWeek(-1) counts what was still open at the end of that week, using Jira's status history, so the numbers stay correct even after the issues are closed later. To count what was created each week instead, use created >= startOfWeek(-1) AND created < startOfWeek().
2. The chart
Add a CelesteCS Chart macro below the table:
- Data range:
Table1.B2:B7 - Labels range:
Table1.A2:A7 - Chart type: Line (or Bar)
- Title: Open vulnerabilities at the end of each week
The chart reads the values the formulas calculate, so it always shows the current state of Jira; no exported CSV and no manual update are needed. More weeks are just more rows. Replace project = SEC AND type = Vulnerability with your own filter, for example filter = 12345.
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.