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

[Shraddha]

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

[Valeriia_Havrylenko_SaaSJet_AbcSite]

Hi @Shraddha 👋

I wanna suggest you to try a new Sprint Performance Report where we have all the metrics for each sprint.  You can see here added, removed, completed and not completed Story points as well. 

  

I would be happy if you had a look at it! 

Try Time In Status for Jira + Sprint Report for free.

You can also book a live demo - we'll show you the application inside out and answer all your questions.

Have a nice day, and happy sprinting 🥳

[Shraddha]

thanks @Valeriia_Havrylenko_SaaSJet_AbcSite 

Happy Thanksgiving

[Valeriia_Havrylenko_SaaSJet_AbcSite]

@Shraddha thank you! Happy Thanksgiving too!

[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! 

[Shraddha]

Thanks @Skyler Ataide for. the detailed explanation. That was really helpful.

I created the chart as described in the steps as is but somehow i get no data for the sprints. There are no points associated with any sprint as below

[Skyler Ataide]

Hi @Shraddha,

It looks like Query 1 and Query 2 are hitting the row limit, which could be causing problems with the left join. Are you able to increase the row limit in both queries from the default 1,000 rows to 100,000 rows to see if this fixes the issue? 

[Shraddha]

Oh great , increasing the rowlimit helped. thanks @Skyler Ataide 

[Skyler Ataide]

You're welcome, happy to help! 😃

[Shawn Stevens]

Trying to repost my Comment/Questions.  @Skyler Ataide @Shraddha 

Thanks for the great walk thru. I was able to get data but what options do we have to display the information in a chart format. I really like the Great Gadgets information and the data behind the information. 

Is something like this possible?


Can we achieve something like this in Atlassian Analytics. Base on the above walkthru, we have some of this data present. Any suggestions on how to get something similar to this or close to it in Analytics. 

Thanks

[Skyler Ataide]

Hi @Shawn Stevens, it is possible to display the chart in the example as a Bar Line chart type in Atlassian Analytics! Additionally, depending on how your Velocity is calculated, you can use Guided formulas to include this calculation in the final chart. Based on the Great Gadgets chart that you shared, it appears that the Velocity displayed in this chart is an average of completed story points based on the last 3 sprints. Working with the same example in Atlassian Analytics that I shared in the thread above, there are a few steps to include this type of calculation in the chart: 

1. First, include the Sprint "Created at" column in the initial chart query: 
   
2. After running this query, add a Visual SQL step to sort the rows in ascending order by "Day of Created at". After sorting the rows, create a new formula column and use the Moving Average guided formula to calculate the average completed story points over the last 3 sprits: 
   
3. After calculating the moving average, you can optionally rename the column for clarity, and hide the "Day of Created At" column as it is no longer needed. Select the Bar Line chart type from the right sidebar; you can further customize the chart from the Chart Settings if desired: 
   

[Shawn Stevens]

@Skyler Ataide Thanks for the update. I'm struggling with the numbers from what I'm seeing with the sprint reports and burndown information. 

You will notice I'm getting negative numbers in completed. The Completed numbers aren't aligning to what I'm seeing. 

                          2023.GhostRider.24       2023.Scrumbags.24
Committed:               39                                      33
Completed:               17                                       13
Added:                        7                                         5
Removed:                  13                                         9

Carryover:                  17                                       16

I double checked the steps in your walk thru 2 times now and I'm missing something. As you can see the numbers in the Chart aren't in line with this. 

Any suggestions on what I'm missing or doing wrong. I have added a filter on the first query to limit it to a specific project and then the sprints listed above. 

[Skyler Ataide]

Hi @Shawn Stevens! Would it be possible for you to create a support ticket here with this information included, so that our support team can take a closer look into your full query? When creating the ticket, you will want to select Technical issues and bugs and Atlassian Analytics as the product. 🙂

[Lucy Dilts]

[Skyler Ataide]

Hi @Lucy Dilts! You'll want to enable the "Show full schema" toggle shown in your screenshot there in order for "Issue ID" column in the "Issue Field" table to display 🙂Once this toggle is selected, you should be able to see the "Issue ID" column available for selection in this table. Hope this helps! 

[Lucy Dilts]

Fabulous! Did the trick! Thanks so much!!!

Not sure if you are up for 2 for 2 but I am also trying to create a Burndown chart and not having much luck :(

[Skyler Ataide]

@Lucy Dilts Great to hear, happy to help! For recreating the Burndown chart in Atlassian Analytics, I would first suggest checking out our Jira Software - Sprint Overview dashboard template to see if the charts on this dashboard help to provide some direction on querying your sprint data in Atlassian Analytics. If you already have a Burndown chart in Atlassian Analytics that you are currently working on, it would be best if you are able to create a support ticket with a link to your chart. This way our support team will be able to take a closer look into what you have built so far and provide some suggestions around how to best create this chart in your Analytics instance 🙂