Filter report based on Jira Ticket Created Date

Questions & Answers
1

Hi,
I’m trying to create a report to show the average days in Transition status for Production Support tickets. I’ve managed to create the following:

image
image3326×2004 493 KB

The problem I have is that this report is including tickets created from the start of time. I’d like to only include tickets with Jira Ticket Create Date in the month of Feb 2022. When I look at the source, I see tickets created in Jan 2022, for instance.

Adding a JQL filter is not a good option as it would affect other reports. I tried to create a custom measure but not able to figure it out. What is the best way to solve this? Thank you.

2

Hi @Rahul_Aurora,

Yes, the Avg days in transition status is a time-related measure and gives the result of issues that transitioned out of the state in the period, no matter when they were created (Import issue change history).
A calculation I would suggest is to Sum Days in transition status Filtering issues created in the period and divide it with Sum of Transitions from status for the same set of issues:

Sum(
  Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
  DateInPeriod([Issue].CurrentHierarchyMember.get('Created at'), 
    [Time].CurrentHierarchyMember
  ) AND
  ([Measures].[Issues created], 
   [Transition Status].DefaultMember, 
   [Time].CurrentHierarchyMember) > 0
  ),
  ([Measures].[Days in transition status], [Time].CurrentHierarchy.DefaultMember)
) / 
Sum(
  Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
  DateInPeriod([Issue].CurrentHierarchyMember.get('Created at'), 
    [Time].CurrentHierarchyMember
  ) AND
  ([Measures].[Issues created], 
   [Transition Status].DefaultMember, 
   [Time].CurrentHierarchyMember) > 0
  ),
  ([Measures].[Transitions from status], [Time].CurrentHierarchy.DefaultMember)
)

Note that in the calculation, we are ignoring the Time dimension ([Time].CurrentHierarchy.DefaultMember) in the Days in transition status and Transitions from status. In this way, we filter issues created in the period but look at how much time they spent in the statuses on average over all time, not only during the selected period.

Lauma / support@eazybi.com

1 Like
3

@lauma.cirule

This doesn’t seem to work for me, though very likely this has to do with my calculated measures.

Goal: to sort out the filter correctly, I want to do a basic count of issues created after the 13th of October, relying on this filter - when I’m sure the filter works, this calculated measure will be extended with some other calculations (this works already without a filter on creation date).

When using the filter described below, the execution of the calculated measures always returns 0 / Empty:

Script:

NonZero(
Count(
Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateInPeriod([Measures].[After Oct 13 2022],
[Time].CurrentHierarchyMember
)
)))

For which [Measures].[After Oct 13 2022]:
(
[Measures].[Issues created date],
[Time].[After Oct 13 2022] --reference to Time dimension calculated member
)

And [Time].[After Oct 13 2022] is defined as

Aggregate(
[Time].[Day].DateMembersBetween(‘Oct 13 2022’,‘today’)
)

4

Hi @Stijn

I would suggest a more straightforward formula without aggregating Time members but using the DateBetween function directly in the measure.
Please try the following (make sure that formatting is set to Numeric as by default eazyBI might wish to format the result as date):

NonZero(Count(
  Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
  DateBetween(
    [Measures].[Issue created date],
    '13 Oct 2022',
    'today'
  )
)))

Lauma / support@eazybi.com

5

Thanks a lot - this worked for me!

1 Like