Open Issues by week with bar chart showing breakdown by custom field

[Broussard_ Anna]

I can get a chart to calculate open issues by week, using created and resolved totals by week. But I would like to get a similar chart but instead of a count of open issues by week, I also want that broken out by one of the custom fields. For example, if the custom field is 'project priority' then each bar should have a breakdown of all open issues that week with project priority broken out in the stacked bar. Is there a straightforward way to do this using atlassian analytics (no apps)?

[Mohsin Shaikh]

Hello Anna,

To create a stacked bar chart in Atlassian Analytics that shows open issues by week, broken down by a custom field such as 'project priority', you can follow these detailed steps:

Step-by-Step Guide

Open the Chart Builder:

• Start by creating a new chart or editing an existing one in Atlassian Analytics.

Use Visual SQL:

• In the Visual SQL interface, expand the "Issue custom fields" table. If your custom field isn't immediately visible, use the search bar to find it by name. Add this custom field (e.g., 'project priority') to your query.

Add Necessary Columns:

• Include the Created at column (aggregated by week) and the Issue key column from the "Issue" table. This will allow you to count the number of issues.

Apply Filters:

• Add a filter to ensure you're only counting open issues. You can do this by filtering on the issue status or resolution fields.

Run the Query:

• Execute the query to get the data you need.

Create the Stacked Bar Chart:

• Once you have the data, configure your chart to be a stacked bar chart. Use the custom field (e.g., 'project priority') as the stack dimension. This will break down each bar by the values of the custom field.

Customize the Chart:

• Adjust the chart settings to customize colors, labels, and other visual elements to suit your needs.

Example

Suppose you want to create a chart that shows the number of open issues each week, broken down by 'project priority'. Here's how you can do it:

Step 1: Create a query to get the number of open issues by week:

• From the Jira issue table, add the Created at and Issue key columns to the “Columns” section.

• Change the aggregation of the “Created at” column to Week.

• Add the Created at column to the “Filters” section of the query and filter for open issues.

• Add the custom field 'project priority' to the query.

Step 2: Apply the Zero Fill step to fill in any missing weeks with zeros. This ensures that even if no issues were created in a particular week, it will still appear in your chart with a count of zero.

Step 3: Configure the chart to be a stacked bar chart, using 'project priority' as the stack dimension.

For more detailed guidance on querying custom fields and creating charts in Atlassian Analytics, you can refer to these resources:

• Query Jira custom fields.

• Create a multi-color bar chart.