Get statistic for JQL: status was "in progress" during ("2024/07/01", "2024/07/05")

Hello! How can I build statistics that will show the number of tickets was in the “In Progress” status broken down by week? Please note that we need statistics not on status transitions, but on being in the status. Example.
There are three tickets:

  • “ticket A” was transferred to the “In Progress” status on 20/06/2024
  • “ticket B” was transferred to the “In Progress” status on 02/07/2024 and at the same day was transferred to “Closed” status
  • “ticket A” was transferred to the “In Progress” status on 25/06/2024 and then to “Closed” on 04/07/2024

In the statistics for the period from July 1 to 5, we should receive all three tickets A, B, C, since they were all in the “In progress” status during the specified period. This statistic matches JQL: “status WAS “in progress” DURING (“2024/07/01”, “2024/07/05”)”

Hi @Vladimir_L ,

​Welcome to the eazyBI community!

​Identification of issues that have been in a specific status at a specific moment requires identification and inspection of each individual issue.

​​The basic condition translates into the following valuable options:
1)issue got into that status earlier and remained in that status throughout the period
​2)issue got into that status during the period and remained in that status throughout the period
​3)issue got into that status during the period and exited the status​

The predefined measure “Issues history” would cover the options 1) and 2), the measure “Transitions from” would cover the option 3)
That measure retrieves from the database and sums transitions of issues over the previous periods.

​The basic expression might be as follows.

​Count(
  Filter(
--set of issues
    DescendantsSet(
      [Issue].CurrentMember,
      [Issue].[Issue]),
--filter condition
   [Measures].[Issues history]>0
   OR
   ([Measures].[Transitions from],
    [Transition Status].[In Progress])>0
  )
)

However, that expression is a serious performance killer as it potentially looks for historical transitions of all issues ​, which involves all transitions of all issues in the data cube for each displayed cell.

​That might succeed on the test dataset but will likely fail on the production instances.

​Therefore, we would like to optimize it as much as possible.

The historical transition measures are slower as they retrieve data from the largest data tables. You might look for the possibility of pre-filtering the issues based on some “absolute” measures to reduce the dataset that calls for historical measures.

​Since Count() only allows one filtering level, you might switch to Sum() and use multi-layered filtering.

​The updated expression might then be as follows.

Sum(
  Filter(
--set of issues
    DescendantsSet(
      [Issue].CurrentMember,
      [Issue].[Issue]),
--pre-filter condition based on absolute dimension context
   ([Measures].[Issues created],
--resetting dynamic dimension context with .DefaultMember
    [Assignee].DefaultMember,
    [Project].DefaultMember)>0
   ),
--numeric value for sum - executed on reduced dataset   
--filter condition
  CASE WHEN
   [Measures].[Issues history]>0
   OR
   ([Measures].[Transitions from],
    [Transition Status].[In Progress])>0
  THEN
   1
  END
)

​When we look from a different perspective - if the issue has entered or exited that status during the period - it already counts.
​So you do not need to look for transitions in previous periods if there are entering or exiting transitions in the current period.

​Therefore, you might update the expression as follows.

Sum(
  Filter(
--set of issues
    DescendantsSet(
      [Issue].CurrentMember,
      [Issue].[Issue]),
--pre-filter condition based on absolute dimension context
   ([Measures].[Issues created],
--resetting dynamic dimension context with .DefaultMember
    [Assignee].DefaultMember,
    [Project].DefaultMember)>0
   ),
--numeric value for sum - executed on reduced dataset   
--filter condition
  CASE WHEN
--there were transitions on current period
    (
      ([Measures].[Transitions from],
       [Transition Status].[In Progress])
       +
      ([Measures].[Transitions to],
       [Transition Status].[In Progress])
     )>0
  THEN
   1
  ELSE
   CASE WHEN
    [Measures].[Issues history]>0
   THEN
    1
   END
  END
)

It is possible to further improve the performance depending on the dataset and reporting requirements. You might watch the presentation by Roberts on eazyBI community days 2024 about iteration through issues.
​You might find the recordings from eazyBI community days here - eazyBI Community Day 2024: Recordings and Presentations.

If all optimization steps still cannot bring the report to work - there is yet another option - JavaScript-calculated customfield measure.
​Still, that option has some limitations:
​1) it will only consider the absolute context - current assignee, current priority, current issue type, etc.
​2) it can only hold a limited number of records per issue - issues in progress for more than 270 days might run into trouble

​Please share the full report context to see the options for optimization or tell me if you are looking towards the JavaScript calculated option.

​Regards,
​Oskars / support@eazyBI.com

Hello, thank you for helping me solve the problem! When I check the correctness of the report, I compare its results with the JQL reference filter in Jira and unfortunately, I see that none of your options give the correct result in the reports.
JQL filter project = Infrastructure and status was in ("In Progress") DURING ("2024/06/03 00:00", "2024/06/08 23:59") shows 118 tickets,
and the formulas you suggested show 101 tickets for the same period.
In addition, I looked at the results in the report and found tickets that should not be there, for example:

  • ticket created on June 7th and moved to closed, bypassing the In progress status
  • ticket was created on June 7th, and moved to the in progress status on June 10th

Could you please check what exactly might be wrong in the formulas?
I would like to add that I also need to show the “Resolved” curve in this report, but there is no problem with that, I just use the predefined standard measure