SPI Calculation with Automation

[Tatiana María Valle Reyes]

Hi Team! I'm Trying to create an automation rule to calculate SPI, with a corporate Jira version without any extra plug-ins, so far, this is my rule:

- When: Sprint Completed

- Then: look up issues project = "ANC" AND Sprint in closedSprints

- And: Add value to the audit log {{lookupIssues.Story Points.sum}}

- And: Add value to the audit log project = "ANC" AND Sprint in closedSprints() AND status = Done

- And: Create variable {{PV}}

- And: look up issues project = "ANC" AND Sprint in closedSprints() AND status = Done

- And: Add value to the audit log {{lookupIssues.Story Points.Done}}

- And: Create variable {{EV}}

- And: Create variable {{SPI}} {{#=}} {{lookupIssues.Story Points.Done}} / {{lookupIssues.Story Points.sum}} {{/}}

- And: Send Mail

[Trudy Claspill · Accepted solution]

Hello @Tatiana María Valle Reyes 

Welcome to the Atlassian community.

What is your definition of the acronym "SPI"?

Can you provide screen images that show the actual rule and the details of each step?

What is the output in the rule audit log when this rule executes? Can you provide screen images that show that also, with every entry within the log for that one execution expanded?

Is this rule giving you the output you expect? If it is not, in what way is it not matching your expectations?

In your text you indicate that you create three separate variables; PV, EV, and SPI. But you don't tell us how you are setting values for those variables.

What value do you expect to get for {{lookupIssues.Story Points.Done}} ? I don't believe that this is going to give you any value as it is not valid syntax.

Are you expecting that your Lookup Issues actions are going to retrieve the issues just for the most recently completed sprint? If so, that will not be the case based on the JQL you have shown.

[Tatiana María Valle Reyes]

Hello @Trudy Claspill

Thanks for replying.

- SPI as Scheduled Performance Index, to compare the "Earned Value" (Done Story Points) versus "Planned Value" (Total Story Points), The formula is: EV/PV

- The automation rule is not giving the expected result.

- With {{lookupIssues.Story Points.Done}} I'm expecting ro retrieve the total of story points that ended as "Done" when the sprint was closed.

- Yes, I'm expecting only to calculate the value with the latest Closed Sprint.

Could you please enlighten me :)?

[Derek Fields _RightStar_]

@Tatiana María Valle Reyes Have you tried creating the SPI variable as 

{{#=}} {{EV}} / {{PV}} {{/}}

BTW - Your calculating for EV is off. It should be {{lookupIssues.Story Points.sum}} - same as you did for PV.

[Trudy Claspill]

Can you provide the following screen images?

1. The details of each step where you create a variable.
2. The details of the rule execution log for when the rule was executed.

If you want to make this calculation only for the sprint that was completed and triggered execution of the rule, one change that you will need to make in your JQL statements is to replace this:

sprint in closedSprints()

...with this...

sprint = {{sprint.id}}

The first version gets you issues from every closed sprint associated with the board you specified, not just the issues from the sprint that was just closed. The second version gets you just the issues in the sprint that was closed and triggered the rule.

- With {{lookupIssues.Story Points.Done}} I'm expecting ro retrieve the total of story points that ended as "Done" when the sprint was closed.

That will not work. It is not a valid smart value.

If you are using that immediately after a Lookup Issues action where you used a JQL to get just the Done issues, then you would use the same smart value you used previously to get the sum of Story Points for issues returned by the Lookup Issue action: {{lookupIssues.Story Points.sum}}

[Tatiana María Valle Reyes]

Hello @Trudy Claspill 

I have incorporated the suggested changes and I'm sharing the EV, PV and SPI value

[Trudy Claspill]

Did those changes get you the results you wanted and expected?

[Bill Sheboy · Accepted solution]

Hi @Tatiana María Valle Reyes -- Welcome to the Atlassian Community!

I see your question is resolved, and I wanted to offer a couple of FYIs...

First, does your team ever do any of the following: add issues after the start of the sprint, remove issues during the sprint, change the sizing of issues during the sprint?  If so, the rule as shown would produce an inaccurate result for "done" / "planned".  The Sprint Report would have the information needed, or an additional rule could store the initial value of the Story Points sum (e.g., in a project entity property) for use when the sprint completes.

Next, the rule shown repeatedly uses the Lookup Issues action to create the variables for use in the calculation.  That could be done in one single step using smart value, list filtering.  For example, assuming the lookup results contain all of the issues in the sprint, regardless of status, the calculation could use iteration and math expressions for the result:

{{#=}}( 0{{#lookupIssues}}{{#if(equals(status.statusCategory.name, "Done"))}} + {{Story points|0}}{{/}}{{/}} ) / {{lookupIssues.Story points.sum|0}}{{/}}

When the precision needs to be adjusted, the ROUND() function could be added.

Kind regards,
Bill

[Trudy Claspill]

Those are good points, @Bill Sheboy !