How long was a ticket in Status A until it was moved to Status B for the very first time
there is direct transition between the 2 status .
Regards
Aakanksha
Hi @agupta,
For the time from one status to another use this formula, in my case I am checking time from In Progress to Done - Avg from In progress to Done:
Avg(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
-- filter with measure to pick issues based on dimension usage in the report and validation if issue has a transition from status In progress
([Measures].[Transitions from status],
[Transition Status].[In Progress])>0
AND
-- filter by issue transition date from status In progress
DateInPeriod(
([Measures].[Transition from status first date],
[Transition Status].[In Progress],
[Time].CurrentHierarchy.DefaultMember),
[Time].CurrentHierarchyMember
)
),
-- calculated time in days between first transition date to status In progress and first transition to status Done:
DateDiffDays(
([Measures].[Transition to status first date],
[Transition Status].[In Progress],
[Time].CurrentHierarchy.DefaultMember),
([Measures].[Transition to status first date],
[Transition Status].[Done],
[Time].CurrentHierarchy.DefaultMember)
)
)
Also, you can check my colleagues answers to similar questions here:
…and here:
best,
Gerda // support@eazyBI.com
Thank you for the Reply .
With the formula it seems that diff date function is not working as expected as in attachment
the formula shows 1m and the difference is 20 hrs and formatting set is in dd:hr:mm:
Hi @agupta,
this is because the formula uses function DateDiffDays(), if you want to have the result in hours or minutes, you need to use accordingly another function - DateDiffHours() or DateDiffMinutes() - https://docs.eazybi.com/eazybijira/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference
For your example, if you want to use formatting dd## hh## m## then you need to use function DateDiffMinutes() as this formatting expects result in minutes.
best,
Gerda
Thank you for bringing this to my notice . it worked now .
Can you tell me how can i get issues for which amount of time is greater than 0 minute
I tried using filter row option but it returned me with the error as below .
Failed to execute query. Error message:
Expected value of type STRING; got value ‘-130543.78333333334’ (NUMERIC)
Can you suggest me best solution for the same
Hi all, connected to this I’m trying to create a measure for average time in status for a particular point in time. So, what was the average time issues spent “In Progress” in May vs. those in June? I defined it this way, but I don’t think it’s calculating what I want:
[Measures].[Days in transition status]/[Measures].[Issues history]
Can I get guidance on how to do this?
Hello @gerda.grantina
Thank you for the solution it worked . For the above result is it possible to have the avg of result vs time
As when i tried it with time the value get summed instead Avg result per month .
And there are some negative values as well how can we eliminate them for avg from in progress to done .
Regards
Aakanksha
Hello @Matt_Morrill
You can try to use the default measure “Average days in transition status”. It is a calculation based on the total days issues spent in statuses divided by the number of transitions from any status. With the Time dimension “Month” level members on rows and the measure selected, you should see the number of days the issues that transitioned from any status in that month spent in those statuses.
And filter status by in progress .
Regards
Aakanksha
Thanks @agupta. @gerda.grantina, I’m still fairly new to eazyBI, so pardon the basic questions. It seems that using the Time dimension on the rows along with the “Average days in transition status,” I’m not getting all the items I would expect for a given month. For instance, when I click “drill through” on In Progress items in June, it’s only showing ~ 12 items, when I know we had more in progress at some point during that month. Similarly, it’s showing “zero” average days in transition for our “To Do” items in June, even though I know we had a good amount of items in the backlog.
Is there a way my query can say, “For any items that were In Progress (or any other status) during the month, how long were those items in that status prior to transitioning, and then what was the overall average length of time items spent in that status during as of that month”?