Get our free guides to start using Jira and Confluence Cloud like a pro.

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Talk discussion started

...


Excerpt
hiddentrue

Calculating Velocity


The Source Table:

The Result:



Macro combination:

  1. Insert the Jira Issues macro.
  2. Wrap the Jira Issues macro in the Table Filter macro.
  3. Wrap the Table Filter macro in the Pivot Table macro.
  4. Wrap the Pivot Table macro and the table containing statuses in the Table Transformer macro.
  5. Wrap the Table Transformer macro in the Chart from Table macro.

Macro configuration:

Table Filter:

Filter ColumnSprint
Filter TypeDropdown
Filter ValuesReversed filter '-empty-'

Pivot Table:

Row LabelsSprint
Column LabelsStatus
Calculated ColumnStory points
Operation TypeSum

Table Transformer:
Talk
idtalk-4455

Use the following SQL query:

Code Block
languagesql
SELECT 'sprint', 
MIN(TT1.'Sum of story points') AS 'Story Points Committed',
MIN(TT1.'Sum of story points Done') AS 'Story Points Completed',
AVG(TT2.'Sum of story points Done') AS 'Velocity'
FROM T1 AS TT1 
JOIN T1 AS TT2 on TT1.'sprint' >= TT2.'sprint'
WHERE TT1.'sprint'<>"Total"
GROUP BY TT1.'sprint'
ORDER BY TT1.'sprint'

Additional parameters:

Chart from Table:

TypeColumn
Labels ColumnSprint
Values ColumnStory Points Committed, Story Points Completed



Handy Carousel




Bonus: how to calculate the number of sprints to complete all the unresolved story points depending on the latest value of the team's Velocity

Macro combination:

  1. Insert a table with two columns and rows. Name the first column 'Velocity' and the second column 'Story points unresolved'.
  2. In the first column:
    1. Insert the Table Transformer macro calculating Velocity (according to the instructions above) with the help of the Table Excerpt and Table Excerpt Include macros).
    2. Wrap the Table Excerpt Include macro in the Table Transformer macro.
  3. In the second column:
    1. Insert the Jira Issues macro.
    2. Wrap the Jira Issues macro in the Table Filter macro.
    3. Wrap the Table Filter macro in the Pivot Table macro.
  4. Wrap the table in the Pivot Table macro.
  5. Wrap the Pivot Table macro in the Table Transformer macro.


Handy Carousel


Macro configuration:

For the first column of the table:

Table Transformer:

Use the following SQL query:

Code Block
languagesql
SELECT TOP 1 'Velocity' FROM T1 ORDER BY 'sprint' DESC

For the second column of the table:

Table Filter:

Filter ColumnStatus
Filter TypeDropdown
Filter ValuesReversed filter 'Done'

Pivot Table:

Row Labels-
Column Labels-
Calculated ColumnStory points
Operation TypeSum



Handy Carousel


For the whole table:

Pivot Table:

Row Labels-
Column Labels-
Calculated ColumnVelocity, Story points unresolved
Operation TypeSum

Table Transformer:

Use the following SQL query:

Code Block
languagesql
SELECT 
('Sum of Story points unresolved' / 'Sum of Velocity') 
AS 'Number of sprints to complete unresolved story points' 
FROM T*

Additional parameters:


Handy Carousel






Excerpt Include
Using Table Filter and Charts in Combination with the Jira Issues Macro
Using Table Filter and Charts in Combination with the Jira Issues Macro
nopaneltrue