Handling Jira sprints in Looker

[AmirKatz]

Hello,
I am using Looker (not the Studio) to pull data from a Jira cloud instance that has several scrum projects.
Each story in a project belongs to a sprint.
Each story has Story Points (empty or a number >= 0).

I created a Look that pulls these columns from Jira:
- Project name
- Sprint name
- Issue key
- Story Points

In addition, I created a custom field (measure) called 'Completed Story Points' which evaluates to the Story Points value if the issue status is Done, else it's set to 0.

I then set the sprint name to be a pivot, and in the table visualization I turned on Totals.

So, for each sprint (which is 3 columns in the table) I get, in the column totals, the sum of the column Story Points ('SPs') as 31.6 and the sum of the column Completed Story Points ('CSPs') as 29.1. They are not the same value because some stories in the sprint are not yet Done.

What I'm trying to achieve is to calculate and display the ratio of CSPs/SPs - for this sprint it's 29.1/31.6 -> 92%.

Any idea?

[Lobo-Google]

Hi @AmirKatz , thank you for your post!

It sounds like you can use a shortcut calculation to calculate the percent of previous column. The Using table calculations documentation page (https://cloud.google.com/looker/docs/table-calculations#pivot_calculation_types) includes more information about shortcut calculations. This would create a new column that displays the percent of previous for all of the column and row values, however.

Alternatively, you could create a table calculation for the percent of previous for just the totals values, as outlined in the Calculating percent of previous and percent change with table calculations Best Practices page. The Creating Looker expressions documentation page explains how to identify and use totals values for fields.

I also wanted to provide a few other resources, including things to consider with totals, just in case.

I hope that these resources and ideas are helpful! 

[syenugu]

Hi @AmirKatz  can i know how did you create look from Jira, because i am trying to achieve same

[AmirKatz]

Hello Syenugu,

Our setup is that we have a dedicated Data Analytics team which is responsible for the connection between Jira and the backend data warehouse. So, from my end as a Looker user, I only see the sprints as part of the Jira Explore. I need to connect to our Looker, I will do after the weekend in order to provide more details.