Hi Community & eazyBI-Team,
couldn’t find an answer on my specific question, so I am trying it this way:
I need to calculate KPIs for several different JIRA projects with different issue types and different workflows.
For my KPIs I need to know - for a certain project, issue type and time range - how much time those issues spent in certain statuses (average values).
For example:
For KPI 1 I want to know the average sum of days issues spent in status “TO DO”, “IN PROGRESS” and “TEST”.
For KPI 2 I want to know the average sum of days issues spent in status “IN PROGRESS” and “TEST”.
I want to show it in a bar chart (one bar for each KPI) with the projects on the x-axis and the average days on the y-axis.
I want to select issue type, project and time.
I already tried something like to define calculated members (my KPIs) in “transition status” like this:
Aggregate({
[Transition Status].[TO DO],
[Transition Status].[IN PROGRESS],
[Transition Status].[TEST]
})
In Measures I defined:
CASE WHEN [Measures].[Transitions from status issues count] > 0 THEN
[Measures].[Days in transition status] /
[Measures].[Transitions from status issues count]
END
But it only shows values for an issue if the specified status was left in that time (“Transition from”). It doesn’t look back on the whole lifecycle of the issues.
I am far away from really understanding MDX and this task is time critical for me :-/
Can you assist here?
Many thanks in advance!
Christian
Hi @chennecke
Welcome to eazyBI community!
You are almost there with the calculated members in the Transition status dimension as well as the logic behind the average calculation.
Now, you may want to iterate through all issues having been in those statuses, calculate the days spent in those statuses for each issue, and then get the average from all those issues. For iterating through issues, you would use Descendants() function, and then use aggregate function Avg() to calculate the average.
Avg(
--set of issues having been in at least one of the selected statuses
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Transitions from status issues count]>0
),
--for each issue, days spent in the selected statuses
[Measures].[Days in transition status]
)
In the report Pages, select one or another calculated Transition status member!
To get the idea why Descendants() function could be needed, you may check out another community thread: What's the difference between Tuple and Descendants
Best,
Ilze, support@eazybi.com
Hi Ilze,
thank you for your welcome and thank you for your reply.
I added your code snippet as a new calculated measure. It took some time to show the values and then I got the same results that I have already, only that they are rounded to non-fractional numbers.
The Transition status members are predefined. I have two sets (my KPIs) that cannot change.
Best,
Christian
Please, can someone assist?
I urgently need to create the reports.
Maybe I try to explain in other words:
For JIRA issues I need to calculate the sum of time they were in certain statuses. I want to have it as the avarage for all issues. I need to have the time dimension included where the point of calculating the time in status should be when the issue is being resolved.
Thanks a lot!
Christian
Hi Ilze,
I tried your solution but I’m having the error: “Query execution timeout of 60 seconds reached. Please try to make query simpler.” I only have those two calculations".
Is there a way I can debug what is causing that error and solve it?
Let me know if you need more info about it and I can send it to the support email, thanks.
Hi @Daniel_Luevano
The calculation could be slow as it goes through all issues and then though their changelogs. It tends to time out if one or the combination of the following is true:
- there are lot of issues imported into the account (several tens of thousands issues)
- the report contains multiple page selections
- there are several slow calculations in the report
This particular calculation, in a general way, can’t be optimised more, but, in a particular report context it could be improved (for instance, added some filters by issue date properties if you are interested only in issues created or resolved in a particular time period).
You may try to optimise the report in general: there are collected guidelines how to create optimal report that would show potentially slow elements: https://docs.eazybi.com/display/EAZYBI/Best+practices+for+report+creation
For more particular guidance with optimisation, please contact support@eazybi.com and send the report definition as well as a brief description what do you want to achieve with this report.
Best,
Ilze / support@eazybi.com
1 Like
Thanks Ilze,
Is there a way to do that with the Javascript method? I implemented it but then when the cube grew it broken again, so I’m looking for a definitive method.
thanks.
Hello there,
I think that I have a similar requirement.
I need to report on the time from one status to another. The report shall show the average of this for all issues. In one case it is from one status to its direct successor, in the other there are some statuses in between.
I tried the approach posted above in the first reply by Ilze and had the same issue as the other user. Time out.
Another approach was to use Transition as a row and the Measure “Average workdays in transition status” as a column. However, this calculates an average of all statuses per issue.
A third approach was to calculate this via the average (avg function) of the subtraction of “Transition to status last date” minus “Transition to status first date”. I could not make this work either.
What I need in plain words:
Time from one status to a later status in the workflow on issue level divided by the number of issues.
Thank you in advance for any suggestion.
Best regards,
André
You can check the report example in this example report in our demo account t with several formulas on how to calculate cycle times. They are a bit more complex and include some general optimization:
https://eazybi.com/accounts/1000/cubes/Issues/reports/499777-average-lead-and-cycle-time
You can check if there measure Average days in cycle from the report could work. It will count total time in a selected set of Transition statues (I used Transition status dimension with calculated member Custom cycle and status Category In Progress) to show total time spend in those statuses for resolved issues on resolution date. A filter by resolution date is the one providing better performance in those reports. The filter also ensures we pick up issues in completed cycles only.
If the formulas still work slow you would like to consider calculated JavaScript calculations for cycle times.
We have a feature request to support cycle time calculations by default in our backlog. I added the community vote to the ticket as well.
Daina / support@eazybi.com
Dear Daina,
thanks for your comprehensive reply!
You assumed that my requirement would be based on status categories. That is because I did not state it precise enough.
Let’s say we have one Open status, one Closed status and various In Progress statuses; some a and x, y.
I need to measure the time from the Open status to status x and y. Each would include the time spend in the statuses a.
Additionally, I need to measure the time from the In Progress statuses x or y to Closed.
This would be something like “Days in transition status” divided by the number of issues - I could not make this work though. However, I think this would only work properly for issues that transitioned the shortest way through the workflow from Opend to Closed.
In the workflow it is possible to move backwards in the cycle. So this needs to be covered as well.
In addition, x and y are alternative statuses before Closed, in case this matters for your upcoming suggestion.
I have added a screenshot for clarification.
Thanks in advance for your help!
Best regards,
André
The measure Average days in cycle calculates a total time spent in any selection of statues. The report has 2 combinations of how it is used:
- with Transition Status category In Progress
- with Transition Status calculated member Custom Cycle.
You can use either of them or define a new calculated member in the Transition status dimension and list the statuses you would like to count in this measure. We have several calculated member examples in the Transition status dimension you can use for inspiration. The calculated measure Average days in cycle will work with any of them.
I would suggest using the measure Average days in cycle as a general calculation for a variety of usage. Define a new calculated measure if you would like to use it in the report with a specified status and would like to avoid using transition status dimension in the report.
([Measures].[Average days in cycle],
[Transition Status].[Custom cycle])
Daina / support@eazybi.com
