Finding Average time spent on tickets, grouped by priority

Hello,

I’m brand new to eazyBI, and i’ve searched through documentation to try and understand calculated members, but i am falling short.

I have a report in which i’d like to look at the average time spent based on certain priority tickets. I’ve found one query that is atleast returning results, but the results seem inaccurate.

Ideally, I’d like to see the average time spent on a ‘Normal Ticket’. >drill into that average time, and see a list of all the ‘Normal Tickets’ with their ‘time spent’ listed (specific to that ticket alone).

I created two user defined formulas, but when i try to take the average of my ‘Time Spent’ formula, blanks are returned.

“Aggregate(priority.Children,
[Measures].[Time Spent])”

Any suggestions on how i can achieve these results?

Hi Emirio,

I think this should work. First of all the filter gets all issues that are in the priority and the ones that are resolved and then it calculates the average hours from ‘Created at’ to ‘Closed at’.

Avg(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    [Measures].[Issues created] > 0
    AND
    NOT IsEmpty([Issue].[Issue].CurrentHierarchyMember.GetProperty('Closed at'))
  ),
  DateDiffHours(
    [Issue].[Issue].CurrentHierarchyMember.GetProperty('Created at'),
    [Issue].[Issue].CurrentHierarchyMember.GetProperty('Closed at')
  )
)

You can also use DateDiffMinutes() or DateDiffWorkdays() instead of DateDiffHours().

Gvido Neilands,
gvido@flex.bi,
eazyBI service partner

1 Like

@GvidoN thank you! that seems to be working!

If i only want this table to show results for issues that were created in 2018 and onward, how would i go about that?

I brought ‘time’ in as a row, and selected 2018, but it’s pulling in some records that i would not expect to be there. (opened in 2016, finally closed in 2018). I tried to add a calculated member within ‘Time’, but it looks like i’m referencing ‘issue’ incorrectly. I then tried to add an additional calculated member within ‘Measures’ and it didnt change anything in regards to the results table.

I tried to pull in ‘Issue’ - but my table just kept spinning so i never dug around in that realm. Is there a specific section that this kind of logic would be best implemented?

Thanks!

Hey Emirio,

I am pretty sure that if you select the year 2018 it works correctly. When the filter is running and it is checking the “[Measures].[Issues created] > 0” condition it also check if you have something selected in other dimensions like [Time].

Maybe you wanted issues closed in year 2018 instead? Then you can just replace the “[Measures].[Issues created] > 0” with “[Measures].[Issues closed] > 0”. Or if you want to have both then make it “[Measures].[Issues created] > 0 AND [Measures].[Issues closed] > 0”.

Gvido Neilands,
gvido@flex.bi,
eazyBI service partner

@GvidoN Hi, So ‘issues created’ seems to be working. But issues resolved is still showing tickets that were opened in years other than 2018.

The goal would be to have this report only looking at tickets that were opened/ created in 2018 (and beyond) - don’t want to have to update the filter come january 2019.

You can see in the screen shot, there was 1 low priority ticket opened for the first grouping, 3 that were closed, and the average resolution days is near 2 years.

image

@GvidoN uploaded a new photo. I took a screenshot of the wrong calculated member selection

.

With the calculation you suggested, its not calculated an avg time spent (which is good considering there was only 1 low priority ticket opened in 2018 and it is not yet closed.)… But i’m hoping it to display ‘0’ for issues resolved instead of 3.

thanks!
Emily

Hi,

Average time spent on issues that are created and closed in the current time period:

Avg(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    [Measures].[Issues created] > 0
    AND
    [Measures].[Issues resolved] > 0
    AND
    NOT IsEmpty([Issue].[Issue].CurrentHierarchyMember.GetProperty('Resolved at'))
  ),
  DateDiffHours(
    [Issue].[Issue].CurrentHierarchyMember.GetProperty('Created at'),
    [Issue].[Issue].CurrentHierarchyMember.GetProperty('Resolved at')
  )
)

The calculated member [Measures].[Issues resolved] that you have selected in the picture is a regular measure and it is not effected by the “Avg time spent (WorkDays)” calculated member.

If you want to know the number of issues that have been created and also closed by versions and by time periods, then this is something different:

Count(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    [Measures].[Issues created] > 0
    AND
    [Measures].[Issues resolved] > 0
  )
)

Gvido Neilands,
gvido@flex.bi,
eazyBI service partner

1 Like