Calculate overdue defects by severity

Hi,

I’m trying to count overdue defects by a customized “severity”, and for each severity level there is different time threshold. I did some research, but it doesn’t work. following is my code, any help will be appreciated. Thanks!

Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
DateInPeriod(
([Measures].[Issue created date]),
[Time].CurrentHierarchyMember)
),
CASE WHEN
DateDiffMinutes(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember
) <
CASE [Issue].CurrentHierarchyMember.GetString(‘Severity’)
WHEN “H” THEN 5
WHEN “M” THEN 7
WHEN “L” THEN 60
ELSE 600
END
THEN [Measures].[Issues created]
END
)

Hi Lee,

Can you provide a visual example of what you try to achieve?
Can you also specify if you target only the overdue defects still open or the report should include the one that were overdue but closed/done in the meantime?

Kr,
Fabrizio

I target for all the overdue non-closed defects.

The settings are as below:
Pages: Project, Status (I choose all the non-closed status from the Status drop-down list )
Rows: severity (Customized)
Columns: Measures (Issues Created, overduebyseverity)

The report is supposed to be like this:

Hi,

A likely reason why the formula does not work is that the report does not use the Time dimension, and the formula cannot calculate the duration between issue creation data and [Time].CurrenthierarchyMember. The formula also is not precisely matching the syntax required for the Sum function.

There can be several ways to define overdue issues, e.g., unresolved issues exceeding the number of days since the creation till now:

NonZero(Sum(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
  IsEmpty([Measures].[Issue resolution date])
  ),
  CASE WHEN
   DateDiffDays([Measures].[Issue created date],Now())>
     CASE [Issue].CurrentMember.GetString('Severity')
       WHEN "H" THEN 5
       WHEN "M" THEN 7
       WHEN "L" THEN 60
       ELSE 600
      END
   THEN
   [Measures].[Issues created]
  END
))

Kindly,
Janis, eazyBI support

1 Like

Thanks for the help! The sample code is so neat! and it works.
I really need spend more time on the documentation, thanks!

1 Like