Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,300,104
Community Members
 
Community Events
165
Community Groups

To calculate the total number of defects extracted using macro - Insert JIRA issue,table transformer

Hi Everyone,

 

I need help with the below task.

 

I am extracting the defects from JIRA to confluence page using Macro - Table transformer and inside that another macro - Insert JIRA Link.

I am calculating the number of days taken to resolve and issue, for that I am using the  SQL Query:

SQL.PNG

Attaching the heading of the table of how it looks on publishing for reference:

sql2.PNG

Here my ask is:

1. How can i add a column to calculate the total number of defects. I am not sure how add that column in the existing table. My table looks like below:

sql1.PNG

2. Once i have calculated the total number of defects, what query can i use to calculate the average of it?

3. Is there a query i can use to calculate from the column "time to resolution"(this columns would have the number of days in it) - what defects are older that 14 days or any number of days, if that can be filtered? 

 

1 answer

Hi @Ripanjot Aneta ,

Please clarify what do you mean by the total and average number of defects?

Is total just the number of all the issues? But then I'm not sure what the average term means.

For example, if we talk about the Time to Resolution field, then it's more clear for me: for example, we have two issues with 6 and 4 hours/days/etc. The total time spent is 6+4=10, the average is (6+4)/2=5.

What concerns the third question, I'll get back here tomorrow - I'm out of the office due to the time zones difference.

To count the number of tickets with the Time to Resolution more than 14 days, you can with the help of the Table Filter macro:

Tue 7-1.png

Go to the Table Filter macro settings and set a range filter for the Time of Resolution column:

Tue 7-2.png

Set the required value:

Tue 7-3.png

Then calculate the number of the remaining issues:

Tue 7-4.png

Tue 7-5.png

The filtration panel can be hidden if necessary.

Note that if you have Confluence Cloud and need to wrap several macros one into another (table <- Table Transformer to count Resolution Time <- Table Filter to filter and count totals), you should use the Table Toolbox macro. This macro is a shell for macro nesting in Cloud.

For Server/Data Center macros can be wrapped directly one into another.

Hope this helps.

Like # people like this

Thank you @Katerina Kovriga _Stiltsoft_ , i have used the same, it helped here. 

And was able to calculate the average as well using table filter. 1 and 2 are resolved.

 

For number three, i can use the filter and that helps, but my end goal here is to create a graph from the table for the issues which took more than 14 or 21 days to resolve.

 

This filter value would not help in such scenario. Is there a way or any SQL query with which i can calculate the defects which took more than 14 days to resolve and turn them into graph like below(at this point i am doing it in excel):

The scenario is i have 5 application teams, defects are assigned to these teams. To keep a track of how many days every team takes to resolve the defect is calculated by using SQL query mentioned in the first picture. on the basis of that, i want calculate how many defects took longer than 14 or 21 days to resolve and get it in the graphical form. Is it possible?

 

d4.PNG

I can suggest the following workaround for the third scenario: reproduce all the steps where you count your Time to Resolution.

Then instead of using the Table Filter macro use another Table Transformer macro. Use CASE WHEN SQL query to set labels (for example, short, medium, long...) based on your conditions. As a result, you'll get an additional column with statuses. Here is an example from our documentation.

Then wrap your second Table Transformer macro in the Table Filter macro if required - you'll be able to filter the table and leave specific statuses (not to show all of them on the graph).

Then use the Pivot Table macro to group teams and count the number of each status.

The last step will be to wrap the Pivot Table macro in the Chart from Table macro and visualize the result.

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

An update on Confluence Cloud customer feedback – June 2022

Hi everyone, We’re always looking at how to improve Confluence and customer feedback plays an important role in making sure we're investing in the areas that will bring the most value to the most c...

349 views 2 9
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you