Average resolution workdays excluding time spent in some transitions

Questions & Answers
1

Hi,

I need calculate in a report, average resolution workdays time by month exluding in de calculation time spent in two transition (XXX,YYY) keeping in mind that you can go through those transition more than once

Can you help me ?

Thank you very much and sorry ny my bad English

2

Hi,
I think I have found the solution:

NonZero(Avg(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateInPeriod(
[Issue].CurrentHierarchyMember.get(‘Resolved at’),
[Time].CurrentHierarchyMember
)
AND
DateCompare(
[Measures].[Issue resolution date],
‘2022-01-01’
)>0
),
[Measures].[Total resolution workdays]

Sum(
{[Transition Status].[XXX]},
{[Transition Status].[YYY]},
([Measures].[workdays in transition status],
[Time].CurrentHierarchy.DefaultMember)
)
))

It is right ?

3

Hi @JTejada

Yes, you are quite there with the idea. Just to ensure that your workflow does not allow to set statuses XXX and YYY to the resolved issues and vice versa (by moving issues to those statuses, the resolution date must be removed, and issues should be moved to other statuses by their resolution). It also must be true for other resolutions (Won’t fix, Duplicate, Cancelled, etc).

There were used too many curly brackets in the last Sum(), otherwise, the formula seems correct:

NonZero(
 Avg(
  Filter(
   Descendants([Issue].CurrentMember, [Issue].[Issue]),
   DateInPeriod(
    [Issue].CurrentHierarchyMember.get("Resolved at"),
    [Time].CurrentHierarchyMember
   )
   AND
   DateCompare(
    [Measures].[Issue resolution date],"2022-01-01")>0
 ),
  [Measures].[Total resolution workdays]
  -
  Sum(
  {[Transition Status].[XXX],
   [Transition Status].[YYY]},
  ([Measures].[Workdays in transition status],
   [Time].CurrentHierarchy.DefaultMember)
  )
))

Take into account that this calculation iterates through issues and might be slow.
If the report times out or you use this calculated measure in several reports, consider inventing a new JavaScript calculated custom field where you calculate your resolved workdays during data import, and import it as a measure.

Best,
Ilze, support@eazybi.com

4

Hi @ilze.leite,
Thank you very much. It works !

1 Like