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]

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}}