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

[Ripanjot Aneta]

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:

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

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:

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? 

[Katerina Kovriga _Stiltsoft_]

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.

[Katerina Kovriga _Stiltsoft_]

And I think it's a duplicate question for https://community.atlassian.com/t5/Confluence-questions/To-calculate-total-number-of-defects-extracted-using-macro/qaq-p/2029621, not sure which comes first.

[Katerina Kovriga _Stiltsoft_]

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:

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

Set the required value:

Then calculate the number of the remaining issues:

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.

[Ripanjot Aneta]

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?

[Katerina Kovriga _Stiltsoft_]

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.