Closed issues without resolution date

Hello,

we have a lot of tickets that are in status closed, but have no resolution date. Therefore when using the measure “Open Issues” there are counted in.

How can I modify the “Open Issues”-Formula to exclude also Issues that have no resolution dates but are actually closed?

Many Thanks in advance!

Hi @AntonBDR,

Solution 1)
you can add a calculated member in the Status Dimension with following:

Aggregate(
  Except(
    [Status].[Status].Members,
    {
      [Status].[Closed]
    }
  )
)

https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/calculated-members-in-other-dimensions

Solution 2)
You can also create a measure and filter the tickets with the corresponding status.

Solution 3)
You can modify the Measure Open Issues so that it works with Issues closed.

CASE WHEN [Issue].CurrentMember.Level.Name <> 'Issue' THEN
  Cache(
    NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
      [Measures].[Issues created]
      - [Measures].[Issues closed]
    ))
    + [Measures].[Issues created]
    - [Measures].[Issues closed]
  )
WHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember
THEN NonZero([Measures].[Issues due])
ELSE
  -- optimized formula for drill through Issue
  NonZero(IIF(
      DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Created at'),
        [Time].CurrentHierarchyMember) AND
      NOT DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Closed at'),
        [Time].CurrentHierarchyMember),
    ([Time].CurrentHierarchy.DefaultMember,
      [Measures].[Issues closed]),
    0
  ))
END

Please also check this:
You can edit the source and make sure that Closed is stored as “Closed statuses”.


https://docs.eazybi.com/eazybi/data-import/data-from-jira

Hint: If I am wrong on one point: No guarantee :smiley:

1 Like

Hi Luke,

thanks for your quick response. Solution nr 1 and 2 is not what I´m looking for, since I want to show in the same graphic the number of created, open and closed issues.

I would need a formula like the one you suggested in solution 3. This one is not working though, since it still counts tickets that are closed but have no resolution date (therefore I guess they are counted as “Issue Due”:
image

I need a formula where issues with status closed + no resolution date are excluded.

Many thanks in advance!

Hi

You are almost there with the formula, you only need to remove this branch with issues due altogether:

CASE WHEN [Issue].CurrentMember.Level.Name <> 'Issue' 
THEN
  Cache(NonZero(Sum(
    PreviousPeriods([Time].CurrentHierarchyMember),
    Cache([Measures].[Issues created] 
      - [Measures].[Issues closed])    
    )) + 
    [Measures].[Issues created] 
    - [Measures].[Issues closed]  
  )
ELSE
-- optimized formula for drill through Issue  
NonZero(IIF(      
  DateBeforePeriodEnd(        
    [Issue].CurrentMember.get('Created at'),        
    [Time].CurrentHierarchyMember) 
  AND      
    NOT DateBeforePeriodEnd(        
      [Issue].CurrentMember.get('Closed at'),        
      [Time].CurrentHierarchyMember),    
    ([Time].CurrentHierarchy.DefaultMember,      
      [Measures].[Issues created]), 0 ))
END

Best,
IlzeLA, support@eazybi.com