How to count aged issues over time? Help please

Questions & Answers
, ,
1

Hello, I’m stuck with one report and I can’t find help on other similar topics here. I need to create a daily report showing:

  • how many all unresolved tickets were at the end of the day (issue history) - I have it done with the formula (name “All open”):

([Measures].[Issues history],
[Transition Status.Category].[In Progress])

  • how many unresolved tickets were at the end of the day more than 2 days (issue history) - Here I have a problem and I do not know how to do it, example: today 02.12 I want to see how many aged tickets (the date of creating the ticket is more than 2 days from today) is unresolved
    Second example: on 22.11 I want to see how many aged tickets were unresolved over 2 days (the date of creating the ticket is over 2 days from 22.11)
  • same as above but unresolved tickets over 8 days

it seems to me that I need Age interval for this but I try in this way and unfortunately it does not work:
([Measures].[All open],
[Age interval].[Aged more than 2 days])

aged more than 2 days looks like this:
[Age interval].[002 - 900]

I don’t know how to go about it, how to start it. Please help

Thank you

My table looks like this (over 2 days does not work properly):

image
image397×787 8.63 KB

2

Hello @Damian

Try creating a calculated Measure using the formulas below. In the formulas, I have also added comments about the functions used, so it is easier to follow each step in the formula.

Unresolved for more than 2 days and less than 8 days:

NonZero(Sum(
Filter(
-- iterate through set of issues
Descendantsset([Issue].CurrentHierarchyMember,[Issue].[Issue]),
-- apply filter criteria to each issue
--filters by issues where Issue Created day compared to selected day in report is over 2 days and less than 8
DateDiffDays(
DateWithoutTime([Issue].CurrentMember.GetDate('Created at')),
[Time].CurrentHierarchyMember.StartDate )>2 AND
DateDiffDays(
DateWithoutTime([Issue].CurrentMember.GetDate('Created at')),
[Time].CurrentHierarchyMember.StartDate )<8 AND
-- filters issues that are not not resolved in the period or any previous period
not DateBeforePeriodEnd([Issue].CurrentMember.GetDate('Resolved at'),[Time].CurrentHierarchyMember)
),
--counts issues
[Measures].[Issues history]))

Unresolved for over 8 days:

NonZero(Sum(
Filter(
-- iterate through set of issues
Descendantsset([Issue].CurrentHierarchyMember,[Issue].[Issue]),
-- apply filter criteria to each issue
--filters by issues where Issue Created day compared to selected day in report is over 8 days
DateDiffDays(
DateWithoutTime([Issue].CurrentMember.GetDate('Created at')),
[Time].CurrentHierarchyMember.StartDate )>8 AND
-- filters issues that are not not resolved in the period or any previous period
not DateBeforePeriodEnd([Issue].CurrentMember.GetDate('Resolved at'),[time].CurrentHierarchyMember)),
--counts issues
[Measures].[Issues history]))

Regards,
Elita from support@eazyBI.com