Need to improve performance of Median calculation

Questions & Answers
1

Hi!

I found on EazyBI doc a calculated member formula for calculating median instead of mean, but the problem is that it is very slow and the more complex the table is, the more time it takes to process, even displaying an time out error in some cases.

Any ideas on how to get a better formula?

Thanks,

Walter

Median(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod([Issue].CurrentHierarchyMember.get('Resolved at'),
      [Time].CurrentHierarchyMember) AND
      ([Measures].[Issues created],
       [Time].CurrentHierarchy.DefaultMember) > 0
  ),
-- resolution (lead time) time in hours (because I've multiplied by 24). You can calculate the same in workdays using DateDiffWorkdays instead of DateDiffDays
 (DateDiffWorkdays(
        [Issue].CurrentHierarchyMember.get('Created at'),
        [Issue].CurrentHierarchyMember.get('Resolved at'))
*24)        
)
2

@walterdp
Try this formula which should work faster:

Median(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod([Issue].CurrentHierarchyMember.get('Resolved at'),
      [Time].CurrentHierarchyMember) 
    AND
    DateInPeriod([Issue].CurrentHierarchyMember.get('Created at'),
      [Time].CurrentHierarchyMember) 
  ),
-- resolution (lead time) time in hours (because I've multiplied by 24). You can calculate the same in workdays using DateDiffWorkdays instead of DateDiffDays
 CASE WHEN
 [Measures].[Issues resolved]>0
 THEN
 (DateDiffWorkdays(
        [Issue].CurrentHierarchyMember.get('Created at'),
        [Issue].CurrentHierarchyMember.get('Resolved at'))
*24)
END        
)

Martins / eazyBI

1 Like
3

Hi, @martins.vanags

Thanks for replying. I’ve tried it, it starts “thinking” but then it does not show anything

Median(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod([Issue].CurrentHierarchyMember.get('Resolved at'),
      [Time].CurrentHierarchyMember) 
    AND
    DateInPeriod([Issue].CurrentHierarchyMember.get('Created at'),
      [Time].CurrentHierarchyMember) 
  ),
-- resolution (lead time) time in hours (because I've multiplied by 24). You can calculate the same in workdays using DateDiffWorkdays instead of DateDiffDays
 CASE WHEN
 [Measures].[Issues resolved]>0
 THEN
 (DateDiffWorkdays(
        [Issue].CurrentHierarchyMember.get('Created at'),
        [Issue].CurrentHierarchyMember.get('Resolved at'))
*24)
END        
)

image
image914Ă—548 39.7 KB

image
image1210Ă—480 10.4 KB

4

@walterdp
Does it show any results if you remove page filters from your report?
For example, if you select “All isue types” instead of “Bug, Incident” filter?

Martins / eazyBI

5

I’ll try and let you know.