How to modify "Created vs resolved issues over time" to include an "open issues" in status open for more than 15 days

Questions & Answers
1

How to modify “Created vs resolved issues over time” to include an “open issues” in status “open” for more than 15 days

This sample Created vs resolved issues over time - Issues - Jira Demo - eazyBI

is a great example that i want to use for my project.
It shows data for:

I want to customize this 3rd line (orange line in the example), by filtering only the open issues that have remained exclusively in status “open” for more than X days.

Can you help me create this new “user defined” measure? Thanks

2

Hi @aacebal,

You could modify the Average age of Open issues formula from this demo report: Average age till resolution report - Issues - Jira Demo - eazyBI.
Instead of calculating the average age by the end of the period, we can sum issues that are older than 15 days by the end of the period:

CASE WHEN [Measures].[Open issues] > 0 THEN
  Sum( -- sum issues that are older than 15 days by the end of period
    Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
      -- filter open issues in period using issue properties Created date and Resolution date only
      DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Created at'),
        [Time].CurrentHierarchyMember) AND
      NOT DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Resolved at'),
        [Time].CurrentHierarchyMember)
      ),
    CASE WHEN
    ([Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember) > 0
    THEN
    -- find if cumulative age of each issue by end of period or till today is more than 15 days
      CASE WHEN DateInPeriod(Now(), [Time].CurrentHierarchyMember)
      THEN 
        CASE WHEN 
          DateDiffDays([Issue].CurrentMember.get('Created at'),
            Now()) > 15
        THEN 
          Val(([Time].CurrentHierarchy.DefaultMember,
          [Measures].[Issues created]))
        END
      ELSE 
        CASE WHEN 
          DateDiffDays([Issue].CurrentMember.get('Created at'),
          [Time].CurrentHierarchyMember.NextStartDate) > 15
        THEN 
          Val(([Time].CurrentHierarchy.DefaultMember,
          [Measures].[Issues created]))
        END
      END
    END
  )
END

Lauma / support@eazybi.com

1 Like
3

Thanks [lauma.cirule]! :raised_hands:

1 Like