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

Saimond · September 11, 2024, 4:07pm

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

Saimond · September 17, 2024, 3:28pm

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

nauris.malitis · September 23, 2024, 10:30am

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

Saimond · September 25, 2024, 1:59pm

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

nauris.malitis · September 26, 2024, 2:28pm

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

Saimond · October 7, 2024, 4:43pm

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

nauris.malitis · October 8, 2024, 7:54am

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

Saimond · October 8, 2024, 8:14am

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

nauris.malitis · October 8, 2024, 10:26am

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
)```

Saimond · October 8, 2024, 1:40pm

Perfect it works like a charm. Thanks

Saimond · October 15, 2024, 3:57pm

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

Elita.Kalane · October 18, 2024, 12:26am

Hi @Saimond,

Nauris has gone on a well-deserved vacation 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

Saimond · March 27, 2025, 10:46am

Hello
I will try to restart the topic

Sum(
    Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
      ([Measures].[Open],
      [Issue Type].[Bug]
    ) > 0),
    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
)

It seems working well, but often I get Error message: “Query execution timeout of 60 seconds reached.
Please try to make query simpler.”

Do you think I can improve in someway?
Thanks

nauris.malitis · April 1, 2025, 7:05am

Hi @Saimond

As the number of imported issues grows, the formula has to iterate through more and more issues each time, so, if you don’t have a JQL query filter that stops old issues from importing, there is a chance that complex formulas that once worked, start to time out with all this additional data

Please try this version, which first reduces the set by the Issue Type, which is a property for the issue and will work the fastest. Only after the initial set is filtered the rest of the measures are applied:

Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    [Measures].[Issue type] = "Bug"
  ),
  CASE WHEN
    [Measures].[Open] > 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
  END
)

Best regards,
Nauris