Issues Created and Issues Resolved Formula

Questions & Answers
1

I have a unique situation where we are storing Actual State Time and Resolution Time of our Jira Incident records in custom fields, so I would like to use these fields in the formula to calcuate when an issue is created or resolved. Would you be able to share the code used to calculate issues created and issues resolved so that I can create custom measures using our custom fields?

2

Hi @psoulier,

eazyBI creates a set of measures for each numeric custom field imported, like “Actual State Time created”, “Actual State Time resolved”, and a few more representing the calculated value.
You might want to use these measures for your report to get aggregated values.
Please see the documentation to get a complete list of measures eazyBI generates for each numeric custom field: Jira custom fields.

If you would like to see the average Actual State Time over several issues, then you can create a new calculated measure using those measures:

CASE WHEN --condition to avoid dividing by zero
  [Measures].[Issues with Actual State Time created] <> 0
THEN --total actual state time divided by issues with actual state time
  [Measures].[Actual State Time created] /
  [Measures].[Issues with Actual State Time created]
END

More details on calculated measures are described here in the documentation: Calculated measures

Best,
Zane / support@eazyBI.com

3

Hi Zane,
Thanks for your response, but I’m not looking for an average start time. I’m calculating an average resolve rate:
Avg(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
Not IsEmpty([Issue].CurrentHierarchyMember.Get(‘Actual Start Time’))
AND
[Measures].[Issues resolved] > 0
),
DateDiffWorkdays(
[Issue].CurrentHierarchyMember.Get(‘Actual Start Time’),
[Issue].CurrentHierarchyMember.Get(‘Change Completion Date’)
)
)
I’m choosing a defined measure named Issues resolved, but that field is not usinge Change Completion Date to determine when the Incident was resolved.

What I’m really looking for is the code for Issues resolved so that I change modify it to my needs.
Thanks,
Pat

4

@psoulier,

Measures “Issues created” and “Issues esolved” are stored during data import. Those measures are not calculated.

The logic for “Issue resolved” is all issues count with the resolution date in the selected period. And the logic for “Issue created” is all issue count with the creation date in the selected period (see also measure description in the documentation: Jira Core measures and dimensions).

If you would like to filter resolved issues, you may check on the issue property Resolution date and see if it has value. Or you can use the measure “Issues resolved,” ignoring the period when an issue is resolved

If you choose to check on the issue resolution date property, make sure to use some other measure, for example, “Issue with Change Completion Date,” to add report context to the calculation.
You can add filter criteria checking if the issue resolution date is in the selected period to cover resolved issues.

Avg(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    Not IsEmpty([Issue].CurrentHierarchyMember.Get('Actual Start Time')) AND
    Not IsEmpty([Issue].CurrentHierarchyMember.Get('Resolved at')) AND
    --issue completion date is in selected period and get report context though measure
    [Measures].[Issues with Change Completion Date] > 0
  ),
  DateDiffWorkdays(
    [Issue].CurrentHierarchyMember.Get('Actual Start Time'),
    [Issue].CurrentHierarchyMember.Get('Change Completion Date')
  )
)

Or you can keep measures “Issues resolved” ignoring the Time and check-in issue change completion date in the selected period.

Avg(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    Not IsEmpty([Issue].CurrentHierarchyMember.Get('Actual Start Time')) AND
    --issue completion date is in selected period
    DateInPeriod(
      [Issue].CurrentHierarchyMember.Get('Change Completion Date'),
      [Time].CurrentHierarchyMember)
    --check that issue is resolved but ignore when, get report context through measure
    ([Measures].[Issues resolved],
    [Time].CurrentHierarchy.DefaultMember) > 0
  ),
  DateDiffWorkdays(
    [Issue].CurrentHierarchyMember.Get('Actual Start Time'),
    [Issue].CurrentHierarchyMember.Get('Change Completion Date')
  )
)

The main principles from those two examples are:

  • Use at least one measure to get the report context
  • Choose how the calculation should be related to the Time dimension (which of the three dates) and then establish this relationship with a measure or with date property and function DateInPeriod().

Best,
Zane / support@eazyBI.com