Epic History table not retrieving exact number of expected results in comparison to Epic table

[Rizwan Ahmed Khan]

I am using atlassian analytics to create dashboard for a list of Epic based on the program increment they are part of. I am also filtering by applying WHERE clause to get specific amount of results. 

But no matter how much I try, the results that I want should be 22 but getting only 18 out of those. 

I am using below query :- 

SELECT [Epic].[Epic State] AS [Epic State], [Epic History].[Tags] AS [Tags], COUNT(DISTINCT [Epic].[Epic ID]) AS [Count of unique Epic ID] FROM [current_dw].[Epic] AS [Epic] LEFT OUTER JOIN [current_dw].[Epic History] AS [Epic History] ON [Epic History].[FK Epic ID] = [Epic].[Epic ID] WHERE [Epic History].[Tags] IN ('Block Funded 2024') AND ([Epic].[FK Portfolio ID] = '5') GROUP BY [Epic].[Epic State], [Epic History].[Tags] ORDER BY [Epic State] ASC, [Tags] ASC;

[Foster Cryer]

Hello @Rizwan Ahmed Khan

Based on your question, I wanted to provide an example query and clarify the use of the [Epic History] table. For the [Epic History] table, this would primarily be used to see the changes made to Epics over time, but for the query parameters you mentioned, I don't think we need to use this table. 

I understand that you're interested in generating a list of Epics, with the ability to limit based on the Program Increment (PI), Portfolio, and associated tag/tag list. I've provided an example query/screenshots in this answer that will help in displaying this type of information. Feel free to respond with any questions you have about the query or with other questions that this may bring up.

Below is a screenshot of the query from within Atlassian Analytics, along with the SQL generated:

• By clicking on the "Join Path" section, you can specify what path you want to use to join the [Epic] table with other tables. You may need to add a filter condition to force a join with the [Map Epic to Program Increment] table by have a filter where [Map Epic to Program Increment].[FK Epic ID] != 0.

---

SQL:

SELECT TOP 100000 [Epic].[Epic ID] AS [Epic ID],
[Epic].[Epic Name] AS [Epic Name],
[Epic].[Epic State] AS [Epic State],
[Tag List].[Tag List] AS [Tag List],
[Portfolio].[Portfolio Name] AS [Portfolio Name],
[Program Increment].[PI Name] AS [PI Name]
FROM [current_dw].[Epic] AS [Epic]
LEFT OUTER JOIN [current_dw].[Tag List] AS [Tag List] ON [Epic].[FK Tag List ID] = [Tag List].[FK Tag List ID]
INNER JOIN [current_dw].[Portfolio] AS [Portfolio] ON [Epic].[FK Portfolio ID] = [Portfolio].[Portfolio ID]
LEFT OUTER JOIN [current_dw].[MAP Epic to Program Increment] AS [MAP Epic To Program Increment] ON [MAP Epic To Program Increment].[FK Epic ID] = [Epic].[Epic ID]
LEFT OUTER JOIN [current_dw].[Program Increment] AS [Program Increment] ON [MAP Epic To Program Increment].[FK Program Increment ID] = [Program Increment].[Program Increment ID]
WHERE [Epic].[Epic ID] != 0
AND [Tag List].[Tag List] LIKE '%Analytics 2.0%'
AND ([Portfolio].[Portfolio Name] = 'Digital Services')
AND [Program Increment].[PI Name] IN ('QI-5','QI-6')
GROUP BY [Epic].[Epic ID],
[Epic].[Epic Name],
[Epic].[Epic State],
[Tag List].[Tag List],
[Portfolio].[Portfolio Name],
[Program Increment].[PI Name]
ORDER BY [Epic Name] ASC,
[PI Name] ASC;

---

You can update the query for your variables, which would be centered on these three lines from within the WHERE clause (you would replace the bolded text with criteria that lines up with the [Tag List], [Portfolio Name], and [PI Name] conditions for your query):

• AND [Tag List].[Tag List] LIKE '%Analytics 2.0%'
  AND ([Portfolio].[Portfolio Name] = 'Digital Services')
  AND [Program Increment].[PI Name] IN ('QI-5','QI-6')

---

Below are a couple more screenshots that show how you can use the Group & Aggregate button/function to ensure that each Epic only shows up once in the table instead of one row for every PI that is associated with the Epic.

The formula that I applied to the "GROUP_CONCAT(PI Name)" column/field is the following, which adds a space after each comma, making the display of the concatenated PIs a little easier to read:

• replace("GROUP_CONCAT(PI Name)",',',', ')

Hopefully this helps you get the list of results you were expecting to see! Thanks, and please let me know if you have any questions.