Hello,
I am trying to get the average workdays spend on Jira tickets for particular status in workflow.
Eg: Let say I have 5 status in ticket flow, I would like to calculate the number of workdays spend on status 2->status 3 and status4->status5.
What I have done so far:
I have used transitions and added all the respective transition which I want for calculation. On column added the measure to calculate " workdays in transition status" and row as time. I want data for last 6 months so used time dimension as Row.
Issue with the information:
It will show the duration spend in different months separately based on when the transition happened. But I want the collective workdays in the month when the ticket is closed.
Eg : Ticket has status 2 & status 3 in dec but status 4 in feb.
The duration to be calculated for status 2-> status 3 will come in Time dimension January and from status 4 in feb, where as I would like to get all days added and show when ticket closed let’s say feb.
Thank you
Hi @Mehak_Gulati,
It is great to see you join the eazyBI community 
. Welcome!
I recommend exploring the eazyBI issue cycles option. There you can specify several issue statuses for eazyBI to calculate the total duration of the cycle. The functionality provides several practical calculations, including average days and workdays in the cycle. These measures will return the average of the complete cycle in the Time dimension period when issues last moved out of the cycle to any status not defined in the cycle. Please see more details here - Issue cycles.
Suppose you still want to display the average information on the Time dimension period the issues were resolved. In that case, you can copy the formula of the predefined average calculation, adjust it and define a new calculated measure. Please see an example below for the cycle “Progress” and resolved issues:
CASE WHEN [Measures].[Issues with Progress of resolved issues] > 0 THEN
[Measures].[Progress workdays of resolved issues] / [Measures].[Issues with Progress of resolved issues]
END
Update the formula to fit your cycle name and set the formatting to “Decimal”. Please review the eazyBI documentation page for more information on defining calculated measures - Calculated measures and members.
Best,
Roberts // support@eazybi.com
Hello Robert,
Thank you for the valuable input. The issue cycle helped me to get the output same way I was looking for. But there is one more blocker, the status can be used once in any issue cycle. I want to find the duration for overall status as well (excluding few status from the default workflow). Is there any other solution to solve this situation.
Ex: I have used status 1 & status 2 for cycle 1
status 3 & status 4 - cycle 2
status 5 & status 6 - cycle 3
but now I want a cycle X with status 3, status 4 , status 5.
would it be possible ?
I tried aggregating using the measure of Issue cycle but on time dimension it still has the same problem that durations being split in different months.
I hope my concern in clear.
Thank you
Hi @Mehak_Gulati,
Indeed, eazyBI issue cycles allow using a status only in one cycle. To retrieve the average workdays in cycle X, you can define a new calculated measure with the formula below:
Avg(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
-- issues resolved in the particular Time period
DateInPeriod(
[Issue].CurrentHierarchyMember.Get('Resolved at'),
[Time].CurrentHierarchyMember
)
),
CASE WHEN
-- issue transitioned through the desired statuses
Aggregate(
{
[Transition Status].[status 3],
[Transition Status].[status 4],
[Transition Status].[status 5]
},
([Measures].[Transitions from status],
[Time].CurrentHierarchy.DefaultMember)
) > 0
THEN
-- return workdays in those statuses
Aggregate(
{
[Transition Status].[status 3],
[Transition Status].[status 4],
[Transition Status].[status 5]
},
([Measures].[Workdays in transition status],
[Time].CurrentHierarchy.DefaultMember)
)
END
)
Replace status 3, status 4, and status 5 with the respective status names in the formula.
Please look at the eazyBI documentation page for more information on defining calculated measures - Calculated measures and members.
Best,
Roberts // support@eazybi.com
Hello @roberts.cacus,
Thank you for your reply. I would say this code is really helpful. It is giving me results the way I am looking for my reports. For further usability, I would like to understand that can I use updated time in below statement
[Issue].CurrentHierarchyMember.Get(‘Resolved at’)
Replacing “Resolved at” with “Updated on” would make any sense?
Thank you again for the help 
Hi @Mehak_Gulati,
Yes, you can replace the 'Resolved at' with the property returning the issue’s last updated date 'Updated at'.
You can also use the property reference from Measures. For example, you can replace [Issue].CurrentHierarchyMember.Get('Resolved at') with [Measures].[Issue resolution date].
Similarly with [Issue].CurrentHierarchyMember.get('Updated at') and [Measures].[Issue updated date] and other properties.
Best,
Roberts // support@eazybi.com
Hi @roberts.cacus ,
Thank you for the input. I will use it.
I have a one use case. Let say in a dimension there are N possible values but I would like to use few of them only like 5 .
I have tried using the dimension in pages and defined a “new calculated member” by using "aggregate function.
I did similar once for other dimension where it worked but in this case it is not working. Can you suggest any other approach to do it?
Example : dimension - Issue type
values - Bug, task, sub-task, story, epic.
I would like to use only - Bug , story, task
Thanks
Hi @Mehak_Gulati,
Your new question is outside the scope of your original question. In such a case, I recommend creating a new question. Please do so in the future, so other community members with the same question can find it and get to the solution 
.
The aggregation of the said dimension members should do the trick. The example in the eazyBI documentation page illustrates that with the Priority dimension members - Aggregate.
After defining such a calculated member, you can select it in the report and view the desired result. Please share the formula you tried and how you tried to use it in a report. Please see more details about defining calculated members in other dimensions here - Calculated members in other dimensions.
Best,
Roberts // support@eazybi.com
Hello @roberts.cacus ,
Thank you for the quick response and suggestion.
I understand! I will post this question again separately .
As you have asked for the formula, so I am using it like this:
Aggregate({[Issue Type].[Bug], [Issue Type].[Story], [Issue Type].[Task], [Issue Type].[Sub-task], [Issue Type].[Research] })
Thank You
Hi @Mehak_Gulati,
The formula seems correct. Did you select the calculated member to appear in the page selection? Please see an example below of the Priority dimension calculated members “Specific Priorities ordered” chosen for selection in pages.
Best,
Roberts // support@eazybi.com