Timeout on Average age of open issues when filtered by issue type

Questions & Answers
1

Hello everyone,

I try to use the Average Age of Open Issues which is defined here:
https://eazybi.com/accounts/1000/cubes/Issues/reports/50084-average-age-till-resolution-report

Here it is for convainance:

CASE WHEN [Measures].[Open issues] > 0 THEN
  Avg(
    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
    -- cumulative age of each issue for any period till end of period or till today
      CASE WHEN DateInPeriod(Now(), [Time].CurrentHierarchyMember)
      THEN DateDiffDays([Issue].CurrentMember.get('Created at'),
        Now())
      ELSE DateDiffDays([Issue].CurrentMember.get('Created at'),
        [Time].CurrentHierarchyMember.NextStartDate)
      END
    END
  )
END

I have timeout problems as soon as I try to limit the computation to a subset of issue type (actually bugs).

So I created a measure “Average Age of Open Bugs” like this:

([Measures].[Average age of Open issues], [Issue Type].[ALL_BUGS])

ALL_BUGS is defined like this:

Aggregate(
  Filter(
    [Issue Type].Members,
    [Issue Type].CurrentMember.Name MATCHES 'Bug.*' OR [Issue Type].CurrentMember.Name MATCHES 'Bogue.*'
  )
)

This is needed because of team managed project which create a lot of different issue type.

When trying to use Average Age of Open Bugs I got timeout errors. And when trying to use Average Age of Open Issues as defined in the example but with a ALL_BUGS issue type filter in Page, I got the same issue.

Any idea how to fix it? I really don’t understand why I don’t have any timeout with less filtering than more (with Pages functionality).

Thank you for your help

2

Hi,

The use of aggregated members has an impact on such complicated formulas as the Average Age of Open Bugs. In this case, we can apply the issue type filtering in the same formula without affecting the performance:

CASE WHEN [Measures].[Open issues] > 0 THEN
 Avg(
   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) AND
       [Measures].[issue type] MATCHES "Bug.*"
     ) 
     ,
   CASE WHEN
   ([Measures].[Issues created],
   [Time].CurrentHierarchy.DefaultMember) > 0
   THEN
   -- cumulative age of each issue for any period till end of period or till today
     CASE WHEN DateInPeriod(Now(), [Time].CurrentHierarchyMember)
     THEN DateDiffDays([Issue].CurrentMember.get('Created at'),
       Now())
     ELSE DateDiffDays([Issue].CurrentMember.get('Created at'),
       [Time].CurrentHierarchyMember.NextStartDate)
     END
   END
 )
END

Kindly,
Janis, eazyBI support

3

Hello Janis,

It works well. I don’t know how I tried to do it before (I tried something similar). Thank you very much!

Have a good day

1 Like