Age report for open tickets spent time in each status ( Including current status)

Questions & Answers
,
1

Hi

I need to create report for issues spent how much time in each status(Including current status) till date .I don’t want to include completed issues in my report. I tried using " days in transition status" measure but its not showing time spent by the issue in current status. I am new to eazyBI.

image
image786×538 40.4 KB

2

Hi @shanmuka,
You can check out our demo account report Issue Days in Backlog, we use measure Days in transition status till now there that you can copy and use in your report.
The formula is this:

-- days in transition status when issue was in this status in previous times
Cache([Measures].[Days in transition status]
+
-- days since last transition to this status
NonZero(SUM(Filter(
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
  IIF([Transition status].CurrentHierarchyMember.Level.Name = "Transition status",
      [Transition status].CurrentHierarchyMember.Name = [Measures].[Issue status], 1)
  ),
  CASE WHEN
  [Measures].[Issues history] > 0
  THEN
  DateDiffDays(
    [Measures].[Issue status updated date],
    Now()
  )
  END
)))

And then you can filter your report by Transition status category (In progress and New) to see only not completed issues.

best,
Gerda // support@eazybi.com

1 Like
3

Thanks For the Reply.

I am using below measure to calculate issue spent time in status. Problem is when ever i created any new sub task/ task, the actual time of the task/epic/initiative changes according to created one. can you please provide any solution.Thanks in advance.

Measure used for calculating Time In Status:

NonZero(

Cache(

-- days in transition status when issue was in this status previously

[Measures].[Days in transition status] +

-- days since last transition to this status

IIF([Measures].[Issues history] > 0,

  DateDiffDays(

    ( [Measures].[Transition to status last date],

      [Time].CurrentHierarchy.DefaultMember ),

    Now()

  ), 0

)

)

)

Screen Shot:

image

4

Hi @shanmuka,
I see that you have changed the formula and it is different from the one that was provided above in my answer.
Could you please tell me what you would like to calculate there so I can understand what is wrong?.

best,
Gerda // support@eazybi.com

5

Hi @gerda.grantina,
I want time spent by each issue/ticket in respective status

I. E backlog - todo - in progress - done etc.

image
image762×185 7.21 KB

In above example : I created initiative(EDM-1) >> epic(EDM-6) >>Story(EDM-22)
Problem with above result(I used above mention code) is, I created new sub task(EDM-62) and the actual time spent by the respective story/epic/initiative changes to created (New sub task) one.

I hope i am clear with my requirement .Thanks in advance.

Shanmuka

6

Hi Gerda,
We have test scenarios in Jira. The workflow includes a Failed status and an In Review status. We would like to look at the total time in both of these statuses together. Is there a way to update the code you provided to calculate the total time in these 2 statuses?
Thanks so much!
Laurie