How to get story points added,removed, completed , not completed in a given sprint

Shraddha_3rzhr3

Hi,

I am trying to get a matrix table with all sprints in a given project that displays sprint name as the primary dimension and Story points added, removed, completed and not completed each as a measure in the matrix table.

How can i achieve that?

See attachment below for the required results

Skyler Ataide

Hi @Shraddha! 

 

Here is an example table chart that I was able to make in Atlassian Analytics similar to the chart that you've shared here. The table chart displays Sprint names, Story points added, removed, completed and not completed: 

The steps to create this chart in Analytics are as follows: 

1. The first query brings in the Sprint ID and Name columns from the Sprint table, the Issue ID and Action columns from the Issue sprint history table, and the Status column from the Issue table. After running the query, reorder the columns so that the Issue ID column is the leftmost column. This will be important for joining the query in a later step.
   
2. Next, add a second query that brings in the Issue ID and Value columns from the Issue field table. Add a filter where the Name column from the Issue field table is equal to 'Story Points'. Again, make sure that the Issue ID column is the leftmost column in the Result table after running the query. 
3. After running Query 2, edit the join to be a Left join on the first 1 column. 
   
   
4. Rename the "Value" column to "Story points" for clarity. Add a filter step to include only rows where "Story points" is not null. 
   
   
5. Next we'll create two custom formula columns for story points added and story points removed. The first custom formula for story points added will use the following SQL:
   

   case when "Action"='added' then "Story points" else null end

   The second custom formula column for story points removed will use the following SQL: 
   

   case when "Action"='removed' then "Story points" else null end

   Rename the new columns to "Story points added" and "Story points removed" for clarity.
   

6. Next, we'll add two more custom formula columns for story points completed and story points not completed. The first custom column for story points completed uses the following SQL:
   

   Case when "Status"='Done' then "Story points added" else null end

   The second custom formula column for story points not completed uses the following SQL: 
   

   Case when "Status"!='Done' then "Story points added" else null end

   
   Rename the new columns to "Completed" and "Not completed" for clarity.
   

7. Next, we can hide the following columns as they are no longer required for the table chart: Issue ID, Sprint ID, Action, Status, and Story points. 
   
8. Next, we're going to apply custom formulas to all four of our story points columns to cast the story points as a real data type, since they are text data type by default. Use the following SQL in the custom formula on each column to change the data types of the columns:
   

   cast("COLUMN_NAME" as decimal)

   
   

9. Now that all the numeric columns have been cast as a real data type, apply a Group & aggregate step to Group by the "Name" column and calculate the total sums for the four numeric story points columns.
   
10. Optionally, rename the columns for clarity. 
    
11. Add a Zero fill step to fill all null values with zeros. 
    
12. Lastly, apply a custom formula to the "Completed" column so that this calculation doesn't include story points that were removed from the sprint. Use the following SQL in the custom formula: 
    

    "Completed"-"Story points removed"

    
    
    
    

Hopefully this chart example helps! If you have any questions on any of the steps above, please let me know!