Count of overdue issues in time dimension, split by status at the time they were overdue

[Marius]

Hello

I’m trying to create a count of overdue issues over time, split by their status at the time they were overdue.

I’ve successfully adapted the Overdue Issues in Period measure from the demo Issues Due and Overdue analysis report, but this does not split by status at the time issue was overdue that month.

I’ve thought of a couple of ways to try and do this:

• Narrow down the Overdue Issues measure to look at a specific status only at the time it was overdue (Action Close Out status as an example). I’ve attempted to inject [Measures].[Issue status] = “Action Close Out” at various locations in the Overdue Issues measure code but no success – I’m out of my depth on this one.

• Adding the Transition Status as a column as described here.
  Unfortunately this does not split the Overdue Issues in Period measure up, and all sit under All Transition Statuses.

Based on the advice here to count issues in specific status on specific date, I created the following tuple, but this just lists all items in action close out against all the different statuses:

(
[Measures].[Issues history],
[Transition Status].[Action Close Out]
)

This demo report of unresolved issues by status over time is close to what I want, but need it narrowed down further to overdue items.

Is there a way to combine Overdue Issues In Period and Issues History measures?

I’m out of my depth now, any help would be much appreciated!

[Marius]

Update to the above - based on this topic, I think this can be achieved by injecting the following:

(
([Measures].[Issues history], [Transition Status].[Action Close Out])>0
OR
([Measures].[Transitions from status], [Status].[Action Close Out])>0
)

However, the calculation has become so complicated that it times out and I can’t get it to run.

[zane.baranovska]

Hi @Marius,

The calculation for “Overdue issues in period” is complex as it iterates through all issues and for each checks several conditions. Adding the issue history to it adding a bit more complexity.

Here is one more variation you can try. Take “Overdue issues in period” expression as base and make three adjustments.

• return “Issues history” measure instead of number 1 for overdue issues (one place, at the end of code)
• add “Transition Status” default member to the tuple expressions with measure “Issues created” (two places)

The updated expression might look like this.

-- formula will calculate overdue issues by cumulative time from due date
-- for each period if issue is not resolved comparing due date and end of period
-- on period when issue is resolved comparing due date and resolution days
-- formula calculates overdue time and if it is bigger than 0 then it wil count an issue as overdue in period
CASE WHEN
  DateAfterPeriodEnd(
    'today',
    [Time].CurrentHierarchyMember.PrevMember)
THEN
NonZero(Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    -- filter by issue properties to get only open or resolved issues in period

      --if today's due date is not overdue yet
      DateBeforePeriodEnd(
              [Issue].CurrentMember.get('Due date'),
              [Time].CurrentHierarchyMember.PrevMember)            
     AND( NOT DateBeforePeriodEnd(
              [Issue].CurrentMember.get('Resolved at'),
              [Time].CurrentHierarchyMember)
     or 
     DateInPeriod(
              [Issue].CurrentMember.get('Resolved at'),
              [Time].CurrentHierarchyMember))
  ),
  CASE WHEN
  Cache(
    -- day difference to due date for resolved issues on period when issue was resolved
    CASE WHEN DateInPeriod(
      [Issue].CurrentMember.get('Resolved at'),
      [Time].CurrentHierarchyMember) AND
      ( [Measures].[Issues created], 
        [Transition Status].CurrentHierarchy.DefaultMember,
        [Time].CurrentHierarchy.DefaultMember ) > 0 
    THEN
      DateDiffDays([Issue].CurrentMember.get('Due date'),
        DateWithoutTime(
        [Issue].CurrentMember.get('Resolved at')
        )
        )
    --  day difference to due date for open issues for all periods when isses where open
    WHEN DateBeforePeriodEnd(
      [Issue].CurrentMember.get('Due date'),
      [Time].CurrentHierarchyMember) AND
      NOT DateBeforePeriodEnd(
      [Issue].CurrentMember.get('Resolved at'),
      [Time].CurrentHierarchyMember) AND
      ( [Measures].[Issues created],
        [Transition Status].CurrentHierarchy.DefaultMember,
        [Time].CurrentHierarchy.DefaultMember ) > 0
    THEN
      -- not current time period, till end of period is calculated
      CASE WHEN DateCompare(
          [Time].CurrentHierarchyMember.NextStartDate, Now()) < 0
      THEN
        DateDiffDays(
          [Issue].CurrentMember.get('Due date'),
          [Time].CurrentHierarchyMember.NextStartDate
        )
      ELSE
      -- current time period, till now
        DateDiffDays([Issue].CurrentMember.get('Due date'),
          Now()
        ) 
      END
      -- day difference above 0 indicates how overdue this issue is
    END) > 0 
  THEN [Measures].[Issues history] 
  END
))

END

Then you can use the calculation with the “Transition Status” dimension to see the statuses of overdue issues at the end of period.

Best,
Zane / support

[Marius]

Much appreciated, thank you!