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

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.

thanks
alexandre

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:

Daina / support@eazybi.com

Thank you Daina,
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