How to count the record by sprint?

Looker Q&A Looker Studio
1

Hello,

I have a question because I don’t find the way.

I have fields from JIRA :

  • sprint :
    • unique value sprint1 or sprint2, sprint3
    • several values like sprint1, sprint2 or like sprint2,sprint3
  • Number of return

So, I would like to have a table

column 1 : Sprint Column 2 Number of return

Sprint1 1

Sprint2 2

sprint3 1

so, if th value is sprint1, sprint2, it must appear on sprint 1 and sprint 2

Do you have a way to make that please ?

2

This is hard to do in Looker Studio, and generally we recommend working with the data at your database level. For example, you could use SQL like this to separate out the sprint values:

SELECT
TRIM(t.sprint_value) AS Sprint,
t1.“Number of return”
FROM
your_jira_table t1,
UNNEST(SPLIT(t1.sprint, ‘,’)) AS t(sprint_value)

This would basically take each row with multiple sprints and separate it into one row per sprint. Then you can use Looker Studio’s counts and sums to get the numbers you’re looking for.