My Filter in my Query does seem to work

[Don DMack]

I have created a chart to show a count of specific components for all teams (that have  those components) in a specific date range by month. 

It seems my query is giving all the issues for each team in the date range.  It's not making use of the component filter. The result selects the correct Date Range, Issue Status, Status Category but doesn't filter on the Components.  

Are you able to see what the problem is?  Below is my query:

1.SELECT DATE_FORMAT (‘Issue cycle time’.’work ended at’,’yyyy-mm’) AS ‘Month of Work ended at’,

2.              ‘Project’.’name’ AS Project Name’,

3.              COUNT(‘Project component’.’name’) AS ‘ Count of all Name’

4.FROM ‘jira_issue_cycle_time’ AS ‘Issue cycle time’

5.INNER JOIN ‘jira_issue’ AS “Issue’ ON “Issue cycle time’.’issue_id’ = ‘Issue’.’issue_id’

6.INNER JOIN ‘jira_issue_status_history’ AS ‘Issue status history’ ON Issue status history’ ’.’issue_id’ = ‘Issue’.’issue_id’

7.INNER JOIN ‘jira_issue’ AS “Issue’ ON “Issue cycle time’.’issue_id’ = ‘Issue’.’issue_id’

8.INNER JOIN ‘jira_project_component’ AS ‘Project component’ ON ‘Project component’.’project_id’ = ‘Project’.’project_id’

9.WHERE ‘Issue status history’.’ended_at’ >= TIMESTAMP(‘2023-11-01’)

10.       AND ‘Issue status history’.’ended_at’ < TIMESTAMP({TODAY}) + INTERVAL 1 DAY)

11.       AND  (‘Issue’.’status_category’ =  ‘Done’)

12.       AND ‘Issue’.’status’ IN (‘Done’,’TEAM DONE’)

13.       AND ‘Project component’.’name’ IN (‘TDEBT’,’Rfactor’)

14.GROUP BY DATE_FORMAT(‘Issue cycle time’.’work_ended_at’,’yyyy-MM’) , ‘Project’.’name’

15.ORDER BY COUNT(‘Project component’.’name’) ASC,  DATE_FORMAT (‘Issue cycle time’.’work_ended_at’,’yyyy-MM’) ASC, Project’.’name’ ASC

16.LIMIT 100000;

[Tracy Chow]

Hi Don,

Your query will need to go through the jira_issue_component_mapping table to properly filter on issues associated with a component. 

To do this, open the Join path panel to change the join to go through the mapping table. If the mapping table isn't an available option, you can add a column from the mapping table to your query, select the desired join path, and then hide the column after the query.

If you run into any trouble, feel free to open a support ticket like Tejaswi mentioned, and we would be happy to help.

[Tejaswi G]

Hi @Don DMack

This is Tejaswi from the Analytics Support Team and here to help.

From the shared query, The Join and the filters seems to correct. Can you please log a ticket with us so that we can have look and the query results and debug the issue further? 

You can log a ticket with us at https://support.atlassian.com/contact.

Kind Regards,
Tejaswi