Need calculate days issue remain unresolved between days threshold e.g. 30 days, 60 days

Questions & Answers
1

Dear There,

I got a request here and I’m not sure how to calculate. We got a project with all kinds of issues, and they all start from status e.g. OPEN. Next step they are “Submitted”; when they end up in status of “Pending Build”, which means they are “resolved”; after that they will transit to status “Closed”. They can go through “SQA Confirm” to “Closed” as well. Only “Pending Build” and “Closed” are considered “Resolved”.

We now need to calculate, on monthly basis, how many issues remain “unresolved” <= 30 days, and then 30-60 days, 60-90 days, >=90 days

Ideally only to calculate the time from they are “Submitted”.

Any comment please kindly help. Thanks!

metric%233

1 Like
2

Actually… now that I get to know a bit more about eazyBI and MDX, I think the key here is to calculate the days for an issue from status “Submitted” to “Resolution = Yes/Resolved” (or if not resolved, from submitted to Now())

By default there are measure for “time created” – do I need anything special to get the issue “time submitted”?

Also, an issue can get “Resolution = Yes/Resolved” by going thru 2 kinds of transitions (Assigned > Pending Build, and Confirm > Closed)

And we need measure the time per month e.g. per month (by end of past month and/or by latest date of current month), how many issues are submitted and “resolved”, and the days from “submitted” to “resolved” on those issues… in addition, how many issues are submitted but not resolved, and their days from submitted to Now()

Could you help advise the calculation? I tried to use below calculation, but it cannot measure fixes per moth, also it does not measure the time from “submitted”… Please kindly let me know how we can do this. Thanks!

Sum(
 Filter(
	  Descendants([Issue].CurrentMember, [Issue].[Issue]),
([Customer Name].[Key Account]>0 AND
[Measures].[Issues resolved]>0))
  ,
DateDiffDays([Measures].[Issue created date], 
             [Measures].[Issue resolution date])
)

Regards,
Leon Li

3

You can use a date representing when an issue was transited to submitted status:
Issue submitted date:

([Measures].[Transition to status first date],
[Transition Status].[Submitted],
[Time].CurrentHierarchy.DefaultMember)

This calculation (a tuple) above will give you a date when an issue was moved to status Submitted.

You can use this tuple instead of issue created date in the formula above.

Here is one more not I noticed in your formula. You are using
[Customer Name].[Key Account]>0

This will work as a tuple:

([Measures].[Issues created],
[Customer Name].[Key Account]) > 0

It will filter your issues created in the selected period. It could give you issues created and resolved in same period together with the next filter [Measures].[Issues resolved]>0

I would suggest using an issue property filter instead:
[Measure].[Issue Customer Name] = "Key Account"

Daina / support@eazybi.com

4

Thanks Daina.

I now use below calculation, but seems it is not returning expected number. Could you help double check?

Sum(
Filter(

[Measures].[Issues created],

DateDiffDays([Measures].[Date submitted],
Now()) > 30
)
)
)

[measure].[date submitted] =

([Measures].[Transition to status first date],
[Transition Status].[Submitted],
[Time].CurrentHierarchy.DefaultMember)

5

You would like to use a set of issues in the SUM function to apply your calculation. You are using a measure Issues created as a set, it will not work as expected.

You can use the same formula setup you shared with me and then use DateDiffDays with the date submitted. I added other suggestions I named before to the formula as well:

Sum(
 Filter(
	  Descendants([Issue].CurrentMember, [Issue].[Issue]),
         [Measure].[Issue Customer Name] = "Key Account" AND
         [Measures].[Issues resolved]>0))
  ,
DateDiffDays(
      [Measures].[Date submitted],
      Now()
) > 30
)

Please check if this is as you expected. The formula will work for resolved issues only. If you would like to to use it for unresolved issues please use other filters instead of Issues resolved.
You can check this example report on open issues by the last status change in our demo account for similar calculations.

Daina / support@eazybi.com