How can I calculate the number of days a Bug type ticket is open?

Questions & Answers
1
  • I want to exclude the closed ones
  • Multiply by a constant based on priority (eg P1 → daysOpen / 7 * 31)

Please see the function below where I’m starting, I have tried with DateDiffDays instead of [Measures].[Open] but doesn’t work:

IIF(
  [Priority].CurrentMember.Level.Name = "Priority",
  CASE
    WHEN [Priority].CurrentMember.Name = "P0" THEN
      Sum([Measures].[Open]) / 2 * 31
    ELSE
      NULL
  END,
  Sum(
    {
      [Priority].[Priority].Members
    },
    CASE
      WHEN [Priority].CurrentMember.Name = "P0" THEN
        Sum([Measures].[Open]) / 2 * 31
      ELSE
        NULL
    END
  )
)

Thanks

2

I have been trying with the following one, but still no results:

CASE WHEN IsEmpty([Measures].[Issues resolved]) THEN
  --[Measures].[Total resolution workdays] / [Measures].[Issues resolved]
  Sum(
  {
  [Priority].[Priority].Members
  },
  DateDiffDays(
    [Measures].[Issue created date],
    CASE WHEN
      DateInPeriod(Now(), [Time].CurrentHierarchyMember)
    THEN
      Now()
    ELSE
      [Time].CurrentHierarchyMember.NextStartDate
    END
  ) ) 
  ELSE 
  NULL
END
3

Hi @Saimond

Thanks for the details!

Please try the following formula:

Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    IsEmpty([Measures].[Issue resolution date])
  ),
  CASE WHEN
    (
      [Measures].[Issues created],
      [Issue Type].[Bug]
    ) > 0
  THEN
    DateDiffDays(
      [Measures].[Issue created date],
      Now()
    )
    /
    CASE 
      [Measures].[Issue priority]
      WHEN "P0" THEN 2
      WHEN "P1" THEN 7
      WHEN "P2" THEN 14
      ELSE 1
    END
    * 31
  END
)

Add additional conditions to the Issue priority to adjust the dividers. Be sure to set the Formatting to Numeric → Decimal for this measure.

This measure, for the selected Time period, will return the Sum of the measure for Bugs created in the selected period that don’t have a resolution date.

​Best regards,
​Nauris

4

Super Thanks! It works
But I would just ask last help…
is there any way to have cumulative figures? Because if I put monthly report I don’t have the backlog of the previous month…
e.g an issue created in June and not yet fixed in September, then is not considered right now. However I would have each month the sum of all the issues still opened in backlog
image

5

Hi @Saimond

In the formula, you can switch the [Measures].[Issues created] to [Measures].[Open issues].

Let me know if this shows the results as expected!

​Best regards,
​Nauris