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

6

Hi @nauris.malitis

First of all thankyou for your help.

It works, however I still have a problem…because some workflow are not aligned and then I include some closed issue (but unresolved and resolution date is empty) with the formula above.

I modified in this way:

Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    --IsEmpty([Measures].[Issue resolution date]) 
    [Measures].[Issue type] MATCHES '(?i)^(bug)$'
    AND
    [Measures].[Issue status]  MATCHES '(?i)^(closed|done)$'
  ),....

but nothing changed

7

Hi @Saimond

Could you please share the full formula that you are using?

Also, what does the report show now and what would be the expected values?

Thanks!
Nauris

8

Sure

Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    --IsEmpty([Measures].[Issue resolution date]) 
     (IsEmpty([Measures].[Issue closed date])
    )
  ),
  CASE WHEN
    (
      [Measures].[Open issues],
      [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 31
      WHEN "P3" THEN 90
      ELSE 1
    END
    --* 31
  END
)

I expect to report the issues opened in backlog. Checking the results

9

If you are using the “Issues closed” measure in the formula, then you’ll need a new measure to use instead of the standard “Open issues” measure (as this one relies on resolutions).

Create a new measure “Open issues (not closed)” with a formula like this:

CASE WHEN [Issue].CurrentMember.Level.Name <> 'Issue' THEN
  Cache(
    NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
      [Measures].[Issues created]
      - [Measures].[Issues closed]
    ))
    + [Measures].[Issues created]
    - [Measures].[Issues closed]
  )
WHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember
THEN NonZero([Measures].[Issues due])
ELSE
  -- optimized formula for drill through Issue
  NonZero(IIF(
      DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Created at'),
        [Time].CurrentHierarchyMember) AND
      NOT DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Closed at'),
        [Time].CurrentHierarchyMember),
    ([Time].CurrentHierarchy.DefaultMember,
      [Measures].[Issues created]),
    0
  ))
END

Save this measure and then try the following formula:

Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    --IsEmpty([Measures].[Issue resolution date]) 
     (IsEmpty([Measures].[Issue closed date])
    )
  ),
  CASE WHEN
    (
      [Measures].[Open issues (not closed)],
      [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 31
      WHEN "P3" THEN 90
      ELSE 1
    END
    --* 31
  END
)```
10

Perfect it works like a charm. Thanks

1 Like
11

Hi @nauris.malitis I noticed that issues closed disappeared from the table, do you think any chance to keep the closed ones tracked in the report?
Thanks

12

Hi @Saimond,

Nauris has gone on a well-deserved vacation :palm_tree: and I’ll take over your use case.

When reading through the discussion you’ve had, your initial requirement was to check the open issues. Could you please clarify where do you want to see the closed issues? Should that be in a separate Column in the report?

Best wishes,

Elita from support@eazybi.com

1 Like