How to calculate Days in transition status including the time in current status

[martins.vanags]

The measure Days in transition status works for issues that already moved out from the status. Zeros might indicate an issue was moved out of the status shortly after adding to the status (or have transitioned to the same status). It does not include the time since the last status update.

The following MDX formula (use it to define a new calculated member in Measures) will include in the calculation also days in the current status:

-- days in transition status when issue was in this status in previous times
IIF(
  -- if report uses Status dimension instead of Transition status it should work as well:
  [Status].CurrentHierarchyMember.Level.Name = "Status" and Not [Transition Status].CurrentHierarchyMember.Level.name = "Transition Status",
    ([Measures].[Days in transition status],
     [Transition Status].[Transition status].GetMemberByKey(
        [Status].CurrentHierarchyMember.Key
    )), 
    [Measures].[Days in transition status])
+
-- days since last transition to this status
NonZero(SUM(Filter(
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
  -- for unresovled issues only
  IsEmpty([Issue].CurrentHierarchyMember.Get("Resolved at"))
  AND
  IIF([Transition status].CurrentHierarchyMember.Level.Name = "Transition Status",
      [Transition status].CurrentHierarchyMember.Name = [Measures].[Issue status], 1)
  AND
  IIF([Status].CurrentHierarchyMember.Level.Name = "Status",
    [Status].CurrentHierarchyMember.Name = [Measures].[Issue status], 1)
  ),
  CASE WHEN
  [Measures].[Issues history] > 0
  THEN
  DateDiffDays(
    [Measures].[Issue status updated date],
    Now()
  )
  END
))

Please check this example report in our demo account and the measure Days in transition status till now:
https://eazybi.com/accounts/1000/cubes/Issues/reports/61947-issue-days-in-selected-status

[ARIGE]

Hi

can the same formula that can also calculate the average days in transition including the time in current status?

thanks for your help
arige

[martins.vanags]

Hi,

The approach would be the same, just the code would slightly differ.

Try this one (it requires using also “Transition Status” and “Issue” dimensions in the report)

CASE WHEN
 [Measures].[Issue status]=[Transition Status].CurrentMember.Name
THEN
 (DateDiffWorkdays([Measures].[Transition to status last date],
 Now())
 +
 [Measures].[Days in transition status])/
 ([Measures].[Transitions from status]+1)
ELSE
 [Measures].[Days in transition status]/
 [Measures].[Transitions from status]
END

Martins

[Daniel_Luevano]

Hi Martin,
I’m having an issue with this formula, when the current status is Done or any other closing status the formula should now don’t count, otherwise those issues will always show a lot of time since they were closed.
Any suggestion to avoid that behavior?

Daniel

[martins.vanags]

Hi @Daniel_Luevano

Can you export and share the definition of your report?

Martins / eazyBI

[Daniel_Luevano]

Sure, I’ve sent it by email. Thanks, Martins.

[jmarchal]

Hello Martins,
If you want to filter this information by “Time” and “Transition Status”, what is the “date” what is using the report?
And what is the status that is going to set the information in the matrix?

Make sense?
Best regards!

[martins.vanags]

This report would be using dates when these status transitions happened.

Any time and transition status dimension member could make an effect on that report, it would look if there was a transition for the filtered report context.

Martins / eazyBI

[guinevere]

@martins.vanags

CASE WHEN
 [Measures].[Issue status]=[Transition Status].CurrentMember.Name
THEN
 (DateDiffWorkdays([Measures].[Transition to status last date],
 Now())
 +
 [Measures].[Days in transition status])/
 ([Measures].[Transitions from status]+1)
ELSE
 [Measures].[Days in transition status]/
 [Measures].[Transitions from status]
END

There is any way to use this calculated to acquire the average?

[martins.vanags]

@guinevere

please check this thread:

Average days in transition status + average days in current status

Hello, I would be very grateful for some help on the following problem. I would like to implement the following formula ===> (number of days spent in transition status / numbers of distinct issues that transitioned from the status) + (number of days spent in current status / numbers of issues in current status) For example, I want to know the average time issues spent in the transition status Analysis + the average time issues still in the status Analysis have spent in that status, giving me…

Best,

[peter.reiser]

@martins.vanags very cool solution - thanks

We did a minor change to use workdays versus days as this is more accurate for us:

-- -Workdays in transition status when issue was in this status in previous times
IIF(
  -- if report uses Status dimension instead of Transition status it should work as well:
  [Status].CurrentHierarchyMember.Level.Name = "Status" and Not [Transition Status].CurrentHierarchyMember.Level.name = "Transition status",
    ([Measures].[Workdays in transition status],
     [Transition Status].[Transition status].GetMemberByKey(
        [Status].CurrentHierarchyMember.Key
    )), 
    [Measures].[Workdays in transition status])
+
-- days since last transition to this status
NonZero(SUM(Filter(
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
  -- for unresovled issues only
  IsEmpty([Issue].CurrentHierarchyMember.Get("Resolved at"))
  AND
  IIF([Transition status].CurrentHierarchyMember.Level.Name = "Transition status",
      [Transition status].CurrentHierarchyMember.Name = [Measures].[Issue status], 1)
  AND
  IIF([Status].CurrentHierarchyMember.Level.Name = "Status",
    [Status].CurrentHierarchyMember.Name = [Measures].[Issue status], 1)
  ),
  CASE WHEN
  [Measures].[Issues history] > 0
  THEN
  DateDiffWorkdays(
    [Measures].[Issue status updated date],
    Now()
  )
  END
))```

[helga_avery]

Please help me. And how can I change this request to count only in a certain status?

[martins.vanags]

@helga_avery

Please export and share the report definition?
https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Exportandimportreportdefinitions

It is not clear how do you filter the report by status.

Martins / eazyBI

[helga_avery]

My task:

1. Get the working days when the task was in the To-DO status
2. Get the working days when the task was in the Test status
3. Sum them up
4. Select tickets >7 days

Now I’m trying to adapt this query:

-- -Workdays in transition status when issue was in this status in previous times
IIF(
  -- if report uses Status dimension instead of Transition status it should work as well:
  [Status].CurrentHierarchyMember.Level.Name = "Status" and Not [Transition Status].CurrentHierarchyMember.Level.name = "Transition status",
    ([Measures].[Workdays in transition status],
     [Transition Status].[Transition status].GetMemberByKey(
        [Status].CurrentHierarchyMember.Key
    )), 
    [Measures].[Workdays in transition status])
+
-- days since last transition to this status
NonZero(SUM(Filter(
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
  -- for unresovled issues only
  IsEmpty([Issue].CurrentHierarchyMember.Get("Resolved at"))
  AND
  IIF([Transition status].CurrentHierarchyMember.Level.Name = "Transition status",
      [Transition status].CurrentHierarchyMember.Name = [Measures].[Issue status], 1)
  AND
  IIF([Status].CurrentHierarchyMember.Level.Name = "Status",
    [Status].CurrentHierarchyMember.Name = [Measures].[Issue status], 1)
  ),
  CASE WHEN
  [Measures].[Issues history] > 0
  THEN
  DateDiffWorkdays(
    [Measures].[Issue status updated date],
    Now()
  )
  END
))

[martins.vanags]

@helga_avery
Please reach out to support@eazybi.com with your current report definition and more details.
It is not that clear what will be the layout of your report and what other dimensions will be used to filter this report.

Martins / eazyBI support

[MuratOz]

Hi,
What shall i use to see the ticket age.
1.) the period between the resolved time - created time.
2.) the period between the first issue progress time - created time

Thanks

[Deepak]

@martins.vanags
I am using you measure script and seems like its giving wrong result. May by I am doing sometime wrong. could you please help here? All I need to calculate days of an issue been in any status. I have attached below screenshot this may be helpful for analysis and suggest possible solution?

[martins.vanags]

@Deepak

Your report is filtered for last 6 months (nov, oct, sep, aug, jul, jun).
Days in status calculates the time for historical statuses that were changed in the last 6 months.
As you can see in your screenshot 3 marked with green statuses were changed in May 2022, which does not fall in the last 7-month timeframe.

Martins / eazyBI

[Deepak]

@martins.vanags Thank you so much for quick response.
If you look in This example issue transition date fall in 6 month window but stilll columns in dev and QA is not showing correct days.

how can I modify this days in status measure to work with Time dimenstion so that this measure value only display in rows for issues created under last 6 month. currenly its showing in this view. I know I can apply conditional filter in created issue which contains 1 but is there any other way?

[martins.vanags]

@Deepak
Could you confirm that the issue with missing values hasn’t change the issue type in the last 6 month period?
Do you see the correct results for this issue row if you select “All Issue types” member from the “Issue type” page filter?
If you filter the report by “Issue type” the days in transition status is also filtered by the historical issue type of the issue at the time when the status change happened.

You can create a new condition for your “Days in status” measure:

CASE WHEN
[Measures].[Issues created]>0
THEN
<your current formula>
END

Martins / eazyBI