How to get all time in status summary

Questions & Answers
1

Need to make a report showing how many hours total across all time issues closed during the month spent in various types of statuses.

For example, if 3 issues closed in March, then I want to get the total aggregate time each issue spent in each state. Some issues spend MONTHS in a state and I want all those hours. So if issue 1 spent two months in “waiting” then and issue 2 spent just three days in “waiting” etc. add all that time up.

Here is the chart I’m trying to get to. The problem is the values for the hours in the statuses are way too small to be correct. It appears that the calculated measure is only counting the hours in the given time period, not the grand total.

Screen Shot 2021-09-22 at 12.28.32 PM
Screen Shot 2021-09-22 at 12.28.32 PM1908×1172 247 KB

The hours calculations are based on a custom Aggregate calculated member added to Transition Status.

I’ve put the [Transition Status].[Closed SAs] as a fixed page as that appears to provide the right starting set.

[Measures].[Total Hours by SA Team]  =

(
  [Measures].[Average days in transition status],
  [Issue Type].[Advisement],
  [Transition Status].[Awaiting SA Team]
)

Formatting Custom #,###.## h
2

Hi @lartra!

You are correct; the default transition status calculations are grouped on Time dimension by the date the issues went out of the status. Additionally, you are using Average days in transition, not the total, which is why the number is smaller than expected.

Following is a formula that finds all issues with resolution date during the month (you can change to another date from the issue properties) and sums the days spent in any of the statuses on that period:

Issues_-_eazyBI
Issues_-_eazyBI2514×736 104 KB 

CASE WHEN ([Measures].[Issues resolved], [Transition Status].DefaultMember) > 0 THEN
Sum(  Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod(
      [Issue].CurrentHierarchyMember.get('Resolved at'),
      [Time].CurrentHierarchyMember)
    ),
   ([Measures].[Days in transition status], [Time].CurrentHierarchy.DefaultMember)
  )
END

Note that I have used the Drill into function to drill into Transition status instead of creating custom calculations for each Transition status group. See more here:

Lauma / support@eazybi.com

3

Hi @lauma.cirule,

I’ve tried to figure out how to adjust that CASE statement to return the sum a given set of tickets was in specific status states. But, nothing I do produces anything near correct.

I thought I had it right but turns out I’m getting two sets of data that are not the intersection.

I’m trying to create this dashboard:

Screen Shot 2021-11-01 at 4.32.05 PM
Screen Shot 2021-11-01 at 4.32.05 PM2432×1210 376 KB

I’m getting the number of closed SAs (tickets) using the calculated measure:

(
  [Measures].[Transitions to status issues count],
  [Advisement Type].[Cloud and SDLC],
  [Transition Status].[Closed SAs],
  [Issue Type].[Advisement]
)

Then I’m trying to build the average workdays for the list of closed tickets (and the closed tickets only) for the total time they each spent in various statuses. I’m doing this using another calculated measure

-- Total Workdays for SA Team processing of SAs
(
  [Measures].[Workdays in transition status],
  [Advisement Type].[Cloud and SDLC],
  [Issue Type].[Advisement],
  [Transition Status].[In Progress]
)
+
(
  [Measures].[Workdays in transition status],
  [Advisement Type].[Cloud and SDLC],
  [Issue Type].[Advisement],
  [Transition Status].[Ready for Review]
)

Then I do a displayed calculated measure taking the above two items and dividing to present the average number.

-- Avg Workdays for SA Team Processing
[Measures].[Total Workdays for SA Team processing of SAs]
/
[Measures].[Closed SAs]

Except when I drill into the details the tickets used for the data for the average time are not limited to the tickets in the closed list.

I think the only thing I’m missing is how to limit the tickets used for the Total Working Days part to just those closed in that time period.

I’d appreciate any ideas you have on how to accomplish this.

Thanks!

4

Hi @lartra!

You are using a tuple which is like an address of the measure in the data cube - it gives the measure’s value for specified Advisement Type, Issue Type, and Transition status, but the Time is taken from the table - giving the value of the measure in each specified period.

As written in the measure previously, you need to find the issues for the period and then sum the days in status from all periods. As you are not using the date but the transitions to status, we can use this in the issue filter as well. So the Total Workdays for SA Team processing of SAs measure would be as follows:

Sum(  
  Filter( -- filter issues
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
  (
    [Measures].[Transitions to status issues count],
    [Advisement Type].[Cloud and SDLC],
    [Transition Status].[Closed SAs],
    [Issue Type].[Advisement]
  ) > 0 -- that have the specified transitions in the period
  ), -- define measures to sum
  (
    [Measures].[Workdays in transition status],
    [Advisement Type].[Cloud and SDLC],
    [Issue Type].[Advisement],
    [Transition Status].[In Progress], 
    [Time].CurrentHierarchy.DefaultMember
  )
  +
  (
    [Measures].[Workdays in transition status],
    [Advisement Type].[Cloud and SDLC],
    [Issue Type].[Advisement],
    [Transition Status].[Ready for Review], 
    [Time].CurrentHierarchy.DefaultMember
  )
)

Lauma / support@eazybi.com

1 Like