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

Shraddha
Contributor
August 7, 2023

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

Screen Shot 2023-08-07 at 1.36.55 AM.png

2 answers

0 votes
Valeriia_Havrylenko_SaaSJet
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
November 17, 2023

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. 

SP TV (1).png  

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
Contributor
November 22, 2023

thanks @Valeriia_Havrylenko_SaaSJet 

Happy Thanksgiving

Valeriia_Havrylenko_SaaSJet
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
November 23, 2023

@Shraddha thank you! Happy Thanksgiving too!

0 votes
Skyler Ataide
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
August 10, 2023

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: 

Sprint Story Points Example.png

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.
    Story Points 1.png
  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. 
    Story Points 2.png
  4. Rename the "Value" column to "Story points" for clarity. Add a filter step to include only rows where "Story points" is not null. 
    Story Points 3.png
  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.
    Story Points 4.png

  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.
    Story Points 5.png

  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. 
    Story Points 6.png
  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)


    Story Points 8.png

  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.
    Story Points 9.png
  10. Optionally, rename the columns for clarity. 
    Story Points 10.png
  11. Add a Zero fill step to fill all null values with zeros. 
    Story Points 11.png
  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"


    Story Points 12.png

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

Shraddha
Contributor
August 13, 2023

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

 

Screen Shot 2023-08-13 at 10.53.14 PM.png

Skyler Ataide
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
August 14, 2023

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? 

 

screenshot_CreateAChart_Example.png

Shraddha
Contributor
August 14, 2023

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

Like # people like this
Skyler Ataide
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
August 15, 2023

You're welcome, happy to help! 😃

Shawn Stevens
Contributor
December 27, 2023

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?

Screenshot 2023-12-26 152816.png
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
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
December 27, 2023

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: 
    Screenshot 2023-12-27 at 5.18.20 PM.png
  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: 
    Screenshot 2023-12-27 at 5.22.28 PM.png
  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: 
    Screenshot 2023-12-27 at 5.32.40 PM.png
Shawn Stevens
Contributor
December 28, 2023

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

Screenshot 2023-12-28 103646.pngYou 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
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
December 28, 2023

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
Contributor
January 11, 2024

Issue Field Table.PNG

 

Skyler Ataide
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
January 11, 2024

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! 

Like Lucy Dilts likes this
Lucy Dilts
Contributor
January 11, 2024

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
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
January 12, 2024

@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 🙂

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events