Calculating Jira Data in Confluence

Lisa Tritt December 18, 2020

Hello community!

I am severely math challenged and have a large hole in my skull from banging it against the wall.  Is there a way to take data from Jira and calculate it for display in Confluence?

Scenario:  Take the last 7 days ticket Resolved - ticket Created = TAT and then add all tickets from the last 7 days and divide by number of tickets the average.

Thank you very much

2 answers

1 accepted

1 vote
Answer accepted
Katerina Kovriga _Stiltsoft_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 18, 2020

Hi @Lisa Tritt ,

You need to use additional apps to use math formulas in Confluence.

For example, the Table Filter and Charts for Confluence app.

The first step is to insert the Table Toolbox macro on the page and paste the two Jira Issues macro inside its body ("wrap" your Jira Issues macros in the Table Toolbox macro).

Use the following JQL for the Jira Issues macros: 

  • project = project_name and created >= -7d
  • project = project_name and updated >= -7d

Then your issue sets will be always up-to-date.

The second step is to go to the Table Toolbox macro and calculate the required numbers with the help of the Pivot and Table Transformer macros.

Fri 3-1.pngFri 3-2.pngFri 3-2-1.pngSELECT "Number" as 'Jira Issues', T1.'Count' as 'Created for the past 7 days' FROM T*

Here we got the total number of the Created for the past 7 days issues and set an additional column for further purposes.

 

Fri 3-3.pngFri 3-4.pngFri 3-4-1.pngSELECT "Number" as 'Jira Issues', (T1.'Count Done'+T1.'Count Fixed') as 'Resolved for the past 7 days' FROM T*

Here we got the number of the Resolved for the past 7 days issues, in our example this total number consists of the Done and Fixed issues.

 

Fri 3-5.pngFri 3-6.pngSELECT *, (T1.'Created for the past 7 days'-T2.'Resolved for the past 7 days')/5 AS 'New counted value'
FROM T1 OUTER JOIN T* ON T1.'Jira Issues' = T*.'Jira Issues'

Here we merge the two tables and use our final formula to show the New counted value (one value minus another and divided onto some average value).

 

The formulas are used just to show an example of calculations in Confluence tables. You may use any math sequences as you need for the case.

Lisa Tritt December 18, 2020

This is amazing, than you so much.

Katerina Kovriga _Stiltsoft_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 18, 2020

I've just noticed that the number of the fixed issues for the Resolved for the past 7 days issues is fewer than for the created - don't pay attention on this. It's our test demo project, so I've used other JQLs for my Jira macros (not the 7 days ago, but weeks for the created issues) to get more or less corresponding numbers for the example.

Katerina Kovriga _Stiltsoft_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 18, 2020

And here you'll find more examples of calculations and visualisations using the Jira Issues macro https://docs.stiltsoft.com/display/public/TFAC/Using+Table+Filter+and+Charts+in+Combination+with+the+Jira+Issues+Macro

You may just follow these examples step by step, the only difference will be in nesting of the macros through the Table Toolbox macro as I've shown on the screenshots above (the new Cloud editor doesn't support nesting anymore).

Lisa Tritt December 18, 2020

Ok great.  Thank you very much.  I'll be testing this for sure.

Debra Jones February 15, 2021

@Katerina Kovriga _Stiltsoft_ That page link no longer works. Do you have another link for it?

Many thanks

Katerina Kovriga _Stiltsoft_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 15, 2021

Hi @Debra Jones , the link is the same - somehow it doesn't redirect if you click on it, but works perfectly well if to copy it and paste into the browser address page.

Like # people like this
0 votes
Sherwin Soriano
Contributor
January 6, 2025

I would like to inquire if this is the same when I want to calculate specific total from JQuery and then put it in confluence.

For Example, I would like to use a JQuery to know how many tickets we completed in JIRA per sprint per team. From this query, can I calculate how many of the completed tickets were for project A and project B and show it via Confluence?

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 8, 2025

Hi @Sherwin Soriano ,

Not sure what you mean:

  • If you have a JQL inside your Jira Issues macro and choose to show the total count of suitable issues, then you'll get this total number of issues (e.g. 120 issues)
  • If you have a JQL inside your Jira Issues macro and choose to show your data in a tabular way, then you may use our macros on top (for example, the Pivot Table macro mentioned above) and perform live calculations based on this tabular data (for example, create Excel-like aggregated tables)
Sherwin Soriano
Contributor
January 8, 2025

@Stiltsoft support when you say JIRA Issues macro, is this another extension or plugin or is a default feature in JIRA Cloud?

Sherwin Soriano
Contributor
January 8, 2025

 

 

@Stiltsoft support 

We have 2 types of tickets per team, per sprint which are segregated as project work and BAU work.

We need to know the total SP for project work, another one for BAU Work, per sprint per team.

is this possible? and even so after I get the calculate amount, can I append like divide to another field in JIRA?

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 9, 2025

Hi @Sherwin Soriano ,

Here is the issue of our misunderstanding: you are talking about Jira and we are talking about Confluence. The original question was about how to display and calculate Jira data in Confluence - our app is for Confluence as well.

And the mentioned Jira Issues macro (a new one or legacy for Cloud) is a standard native Confluence macro. Our macros can be put on top and amplify it with various calculations.

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
PERMISSIONS LEVEL
Product Admin
TAGS
AUG Leaders

Atlassian Community Events