Reporting on Issue Transition Status - Filter out issues that were in status for > XX days

Questions & Answers
1

Hi - I’m new to EazyBI and trying to report the average number days issues remain in a given status. So far I have been able to create a report very similar to this demo: https://eazybi.com/accounts/1000/cubes/Issues/reports/50046-history-of-unresolved-issue-statuses

However, I am running into an issue where some weeks show that it took an unusually long amount of time for issues to progress through statuses. This bug is due to an edge case in our data where some JIRA tickets go stale and are left in a given status and never closed. Periodically, we go through and clean up JIRA by closing all such issues.

The problem is, my report registers this cleanup as normal JIRA activity, and therefore shows these weeks as having a massive average amount of time spent in some statuses. In order to fix this, when I query issues eazyBI, I want to filter out issues that were in a transition status for more than 50 days.

Is there a way to write such a filter?

Thanks!

2

Hi,

Thank you for joining eazyBI community!

There are two approaches on how to eliminate edge cases.

  1. If you want to exclude those issues with edge cases from other reports as well, you may filter out them during data import and not import into this particular eazyBI account at all.
    For that, use appropriate JQL filter in data import options: https://docs.eazybi.com/display/EAZYBIJIRA/Jira+Issues+Import#JiraIssuesImport-JQLqueryfilter

  2. If you want to exclude those issues only from average days calculation, you may want to create a custom calculated measure (https://docs.eazybi.com/display/EAZYBI/Calculated+members#Calculatedmembers-Definenewcalculatedmeasure) that iterates through all issues, looking only for issues that were spent less than 50 days in this status, and calculates the average days from those issues:

    Avg(
 Filter(
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
  [Measures].[Days in transition status]<50
  ),
 [Measures].[Days in transition status]
)

Note that this calculation could be slower than standard “Average days in transition status” as it iterates through each issue.

Ilze
support@eazybi.com

3

Thank you for responding Ilze.

Unfortunately, I have been unable to solve this issue. I like your first solution as these edge cases should excluded from EazyBi completely. However, because JIRA does not provide information on how long issues were in a status, I can’t create a JQL filter that uses this information.

I also tried applying your second solution, but because I am dealing with a large number of issues, this calculated field is too expensive and fails.

Is there a way I could combine these solutions. For example, could I generate the custom field on import instead of while analyzing issues? The JS calculated custom field seems to be close to what I am looking for, but is it possible to access the “days in transition status” from javascript? That way I could give my custom field a value only when issues have been in a transition status for < 50 days.

Thanks again for your help,
Sam

4

@ilze.leite
I have the same issue. The second solution times out after 60 seconds.
Any idea how to solve it?