How to calculate median of a complex dataset

Questions & Answers
1

Hi there,

I am having a problem calculating median of a complex dataset in my reports. It’s about an eazyBI instance containing Jira data on regular issues. I need to calculate the rolling median of some measures every day based on 20 days data history.

Here are the details. I am trying to display the daily value of a metric called ‘Median Time to Quote’ .
This is composed of:

  • a Start_Time which is an issue property imported in eazyBI under the Measures dimension: [Measures].[Issue Date/Time Submitted]
  • a Quote_Time which is the transition time of an issue to a certain status which is a used defined calculated field: ([Measures].[Transition to status last date] , [Transition Status].[CCTM Customer Quoted])
  • a Time_to_Quote which is another user defined calculated field derived from the previous 2: DateDiffHours([Measures].[Start Time], [Measures].[Quote Time])

Time_to_Quote is calculated correctly, I can see the correct values for each issue.

Now I can have several Time_to_Quote values each day depending on how many quotes were issued in that day. What I need is to calculate the median value of these metric over the last 20 days.

This should be displayed in a report that has time as columns (at the day level). I am trying this…
Median(
LastPeriods(20, [Time].CurrentHierarchyMember),
([Measures].[Time to Quote])
)
and it does not work. I get nothing…

Can someone please help??? Thank you very much

image
image.png1920×1080 220 KB

2

Median calculation should be applied on the most detailed level members of interest. It seems you would like to have this calculation on issues taking into account selected day (and get issues with the last quote date on this day and 20 days back).

Here is an issue level calculation that works on Time Day level only. At the first part of the formula, I am using Issue properties to limit issue set, I am checking if an issue was open for each day. The main filter of quote date within 20 days is applied on filtered issue set.

Historical measure Transition to status last date works as a filter over time and for any other report selections as well:

NonZero(Median(
Filter(
 Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
 -- use some issue properties to minimize issue set. Use dates with Time dimension on Rows
 -- this will filter open issues from start date
     IIF([Time].CurrentHierarchyMember.Level.Name = "Day",
     DateBeforePeriodEnd(
       [Measures].[Issue Date/Time Submitted],
       [Time].CurrentHierarchyMember)
     AND
     ( isEmpty( [Issue].CurrentHierarchyMember.get('Resolved at'))
     OR
     -- issue resolved in the last 20 days or later
       DateAfterPeriodEnd(
       [Issue].CurrentHierarchyMember.Get('Resolved at'),
       [Time].CurrentHierarchyMember.Lag(20))),1)
     ),
 Case when
 -- the last transition date is within 20 days
  DateBetween(
       ([Measures].[Transition to status last date] ,
        [Transition Status].[CCTM Customer Quoted],
        [Time].CurrentHierarchy.DefaultMember),
        [Time].CurrentHierarchyMember.Lag(20).StartDate,
        [Time].CurrentHierarchyMember.NextStartDate)
 Then
 -- duration from started till last quoted
 DateDiffHours(
  -- date when issue transits to this status
        [Measures].[Issue Date/Time Submitted],
        ([Measures].[Transition to status last date] ,
        [Transition Status].[CCTM Customer Quoted],
        [Time].CurrentHierarchy.DefaultMember))
END
))

The formula could work slow in accounts with many issues. You can use some JavaScript calculated custom fields for the latest quote date import and use this imported date in the formula. The imported date would be an issue property. You would need to update the formula taking this into account.

Daina / support@eazybi.com