Issues age at Fixed - per age period

Questions & Answers
1

Hi,

I’m looking to know:
Amongst issues now fixed, how many were less then 30 days at the time they were set to Fixed Resolution

I can calculate the age of an issue at the time it was fixed with this:

    DateDiffdays(
    ([Issue].CurrentHierarchyMember.GetDate('Created at')),
    (CASE WHEN [Resolution].[Resolution].getMemberNameByKey([Issue].CurrentHierarchyMember.get('Resolution ID')) = 'Fixed'
      THEN [Issue].CurrentHierarchyMember.GetDate('Resolved at')
      END))

I then used a CASE WHEN to give a value of 1 to issues that are less then 30 days old and 0 to the rest. The goal being to sum them afterwards:

Case When
--Time between creation and fixed
    DateDiffdays(
    ([Issue].CurrentHierarchyMember.GetDate('Created at')),
    (CASE WHEN [Resolution].[Resolution].getMemberNameByKey([Issue].CurrentHierarchyMember.get('Resolution ID')) = 'Fixed'
      THEN [Issue].CurrentHierarchyMember.GetDate('Resolved at')
      END)
-- time range before fix      
      )<30 
      THEN 1
      else 0
      END

If I use the TOTAL function on measure, it doesn’t sum all the 0 and 1s.

Also, this formula is not functional

Sum({
Case When
--Time between creation and fixed
    DateDiffdays(
    ([Issue].CurrentHierarchyMember.GetDate('Created at')),
    (CASE WHEN [Resolution].[Resolution].getMemberNameByKey([Issue].CurrentHierarchyMember.get('Resolution ID')) = 'Fixed'
      THEN [Issue].CurrentHierarchyMember.GetDate('Resolved at')
      END)
-- time range before fix      
      )<30 
      THEN 1
      else 0
      END
      })

On this example, I would like the 1st row to show the total number of issues that are less then 30 days old at time they were Fixed (=1 in my CASE WHEN)
image

Thanks,

2

Hello,

I see that you were moving in the right direction, and I am glad you reached out for assistance.

The Sum() function does not return the expected result as it has only the numeric expression and is missing the set expression.
You might want to check the correct syntax in our documentation here: Sum.

However, this might not be the best or easiest solution for your needs.

You might use the Resolution interval dimension to save time and computing resources and avoid iterating through the whole list of issues. To proceed this way, you might first need to turn on the import of interval dimensions as described here: Data from Jira.
That will provide you with two new dimensions - Age Interval and Resolution Interval.

Then you will be able to configure the resolution interval as described here: Interval dimensions and filter for required values.

As for the measures, you may use Issues created that will bring in unresolved issues with resolution interval (none) or Issues resolved for a clearer picture.

Screenshot 2021-10-21 at 16.07.40
Screenshot 2021-10-21 at 16.07.402364×1010 268 KB

​​
​Let us know if this works for you!

Oskars / support@eazybi.com

3

Hi @oskars.laganovskis,

Thank you very much for the help. I think this will work very well.

Just to be certain I understand the dimension correctly, looking at your example, would it be accurate to say:
56 Issues of Story Type were resolved in 29 days or less.

Thanks again,

4

Hi @Marilou,

You are getting it right!
56 Issues of Issue type Story were resolved in 29 days or less in the provided example.

Have a nice day!

Oskars / support@eazybi.com

1 Like