Calculate time spent in transition and eliminate the time in waiting for customer

[anitu]

Hi,

I need to calculate the time spent in transition in the attached picture, where I need to eliminate the time spent in “waiting for customer” only for the period before reaching the needed transition.
your help is much appreciated.

thanks
alexandre

[daina.tupule]

Yes, you are correct. Measure days in transition status in a tuple with time default member will pull in the total time spent on the status Waiting for customer.

You can consider using the function Previousperiods to get time spent on Waiting for customer till the issue was submitted.

Please note. The measure might give you slightly incorrect data if you have additional status move to and from status waiting for custom on the same day when you submitted and issue after the submission. It is not the case for your particular issue, though.

DateDiffDays(
  [Measures].[Issue created date],
  ([Measures].[Transition to status first date],
  [Transition Status].[Resolved],
  [Time].CurrentHierarchy.DefaultMember)
)
-
Sum(PreviousPeriods(
  [Time].CurrentHierarchy.Levels('Day').DateMember(
    ([Measures].[Transition to status first date],
    [Transition Status].[Submitted to R&D],
    [Time].CurrentHierarchy.DefaultMember)
  ).NextMember
),
([Measures].[Days in transition status],
 [Transition Status].[Waiting for customer]))

The formula above might work slow for many issues. You can consider using some JavaScript calculation to get either this date or the days from issue creation till the first submission.

While we do not have an exact example with JavaScript, you can check these two posts:

Defined "closed status" in Jira import options causes logical problems with burndown chart

Hi, I have a problem with so-called “Statuses and history” in the Jira import options. I defined the statuses “Resolved” and “Closed” as closed status in there (for the “Issues Closed” measure). I have also a simple burn-down chart, that shows the open issues (open uses the measure “Issues closed”, which includes “Resolved” and “Closed”). The problem is that past issues that were resolved and getting closed later the burn-down in the past changes. I found out that the “Issues closed” measure…

Issue Resolution histogram excluding time blocked

Hi all, I’m trying to modify an existing bar chart that currently does the following things: Displays a count of all issues resolved in a JIRA project over a set period of time Shows the average resolution time of issues Shows a histogram breaking down how many issues were solved in set periods of time (i.e. less than a day, 1 day to 7 days, 8 to 14 days, etc) Unfortunately due to time that issues have spent being Blocked due to external teams the histogram view doesn’t work well to track ho…

Daina / support@eazybi.com

[anitu]

Thank you Daina,
very much appreciate your help!
I have tested and it looks pretty good, both in terms of results and performances, yet there are some slight differences for some tickets, I mean, the result I get is not always the same with the result for the manual calculation.
However, the margin is less than 1% so I consider it is acceptable for the moment and I’m not going to bother you anymore with.

I would have 2 other questions and if you cannot answer in this thread I can open separate threads for or, please let me know the way I should proceed.

first question is how can I make a test on if the transition is reached:
I have few transitions similar to “Submitted to R&D” and I’d need to know which one is reached, e.g:
CASE WHEN ([Measures].[Transition to status first date],
[Transition Status].[Submitted to R&D]) >0
I’ve tried this one, not always working, not sure is the good one.

and second question:
looking for the “Transition to status date” I’ve noticed that some tickets have this measure empty in the Cube, while the transition in the ticket in Jira is reached and I don’t know why.
Have you ever had such situation?

thank you for you support,
kind regards,
alexandre