How to measure average days/workdays from transition status A to transition status B


#1

Dear There,

I’m looking for a solution here please kindly help double check

  1. I need to know average days from transition status A to transition status B

  2. If possible I would like also to be able to deduct few transition status between A and B to get a more accurate number regarding the average days I need

This is because in our project, we have status OPEN = created, Submitted = send to engineering, and then Pending Build = a fix has been validated and ready for next release; so we would need measure average days from “Submitted” to “Pending Build” within engineering.

Another thing is that, between “Submitted” and “Pending Build”, users can set issue to “Clarify” status where we would be waiting for other team to respond our queries e.g. a demo fix is waiting for customer validation. Ideally, we would like to be able to exclude the days spent here too as these are not the engineering days.

Any comment please let me know. Thanks very much!

Regards,
Leon Li


#2

Hi,

Please try finding the answer in the following community post:

In other words, you could create a new calculated member in “Transition Status” dimension and then use this filter for the measure “Average days in transition status”

Martins / eazyBI support


#3

Dear Martins,

Thanks for details. What I needed might be something different and I’m running out of clue… could you please kindly have a look?

Say issues go through transition status (1) Open > (2) Analyze > (3) Clarify > (2) Analyze > (4) Coded > (5) Verify > (6) Closed

What I needed would be like

a. The days in transition status (2) and (3) per each issue
b. The days in transition status (2) only, per each issue
c. The issue total number in status (4), (5), (6) per each e.g. “reporter”

Could you provide an example of these calculation? I seem unable to find any guide how to come up with the definition…

Thanks very much!

Regards,
Leon Li


#4

Hi,

In this case, I would first create new calculated member in “Transition Status” dimension (which will be used for requirement a) and one also in “Status” dimension (which will be used for requirement c). Both code examples below.
For “Transition Status” dimension:

Aggregate({
[Transition Status].[Analyze],
[Transition Status].[Clarify]
})

and for “Status” dimension.

Aggregate({
[Status].[Coded],
[Status].[Verify],
[Status].[Closed]
})

For a and b requirement, then I would use the measure “Days in transition status” in columns and “Issue” dimension (at issue level) in rows. And finally drag “Transition Status” dimension to pages, where I would enable the new calculated member

In a similar way, I can filter the same report for requirement b

For the last requirement, I would use “Issues created” in columns, “Reporter” dimension (at the user level) in rows and then select new “Status” dimension calculated member in rows.

See both described reports attached as images below.

Martins / eazyBI support


#5

Thanks very much Martins!

One more question - how can I calculate the average number of (3)?

Now I added users in groups in Row, and have all numbers in Column; next I need to have the average number for each group… not sure how to do the calculation in this scenarios?

I’m guessing something like… [issue numbers] / [number of members in user group]

Regards,
Leon Li


#6

Hi,

In this case, you would need to create a new calculated measure to calculate average results.

Try this code:

NonZero(
Avg(
Filter(
Generate(
    ChildrenSet([Reporter].CurrentHierarchyMember),
    Descendants([Reporter].CurrentHierarchyMember, [Reporter].[User])
  ),
[Measures].[Issues created]>0
),
[Measures].[Issues created]
)
)

Martins / eazyBI support


#7

Thanks very much Martins. It’s very helpful!

Now I got the average number per group. One last thing I would need, is to calculate the issue numbers per time e.g. on monthly basis, how many issues were fixed by each of the group and group average…

For e.g. there are totally 14 FIXED in BJDC CRB group, average 3.5 FIXED per user in group (4 users); these are the data from Feb to March; I want calculate how many FIXED in Feb and how many FIXED in March… would this be doable?

Much appreciated :slight_smile:

Regards,
Leon Li


#8

Hi,
In this case, you could create two new calculated measures (one for each month) using the new “Average issues” measure as a base for the calculation.

Try this code for February

(
[Time].[2019].[Q1 2019].[Feb 2019],
[Measures].[Average issues]
)

And similar one you could use for March.

In this case, you could update the code for “Average Issues” using Cache function since you re-use this measure in new calculations.

Cache(
NonZero(
Avg(
Filter(
Generate(
    ChildrenSet([Reporter].CurrentHierarchyMember),
    Descendants([Reporter].CurrentHierarchyMember, [Reporter].[User])
  ),
[Measures].[Issues created]>0
),
[Measures].[Issues created]
)
)
)

Best regards,