Average days in certain status for resolved issues that month

Questions & Answers
,
1

Hi,
I would like to get the average workdays an issue has spent in a number of statusses combined for the issues that reached completion that month (=transition to certain status).

I sum the “workdays in transition” for the statusses I’m interested in by Name and then I want to calculate the average of that sum for the the issues that reach certain statusses that month by a filter.

But this gives me the average of “1” for each issue. And it gives an average for issues with no time spent in the statusses I filtered on.

Could you please help me out here?
We’re using jira cloud.
Columns in the screenshot: name of status, [test doorlooptijd aansluiting], avg(filter)

[test doorlooptijd aansluiting]
Sum({[Transition Status.By name].[02_MBP_Formulier_ontvangen],
[Transition Status.By name].[03_MBP_Formulier_OK],
[Transition Status.By name].[04_MBP_Wachten_externe_configuratie],
[Transition Status.By name].[05_MBP_Dossier_up_2_date_ext_config],
[Transition Status.By name].[06_MBP_Wachten_test_configuratie],
[Transition Status.By name].[07_MBP_Dossier_up2date_test],
[Transition Status.By name].[09_MBP_Wachten_productie_configuratie],
[Transition Status.By name].[10_MBP_Dossier_up2date_productie]},
[Measures].[Workdays in transition status]
)

AVG(Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
(
[Measures].[Transitions to status],
[Transition Status].[12_MBP_Dossier_afronden]
)>0 OR
(
[Measures].[Transitions to status],
[Transition Status].[13_MBP_In_productie]
)>0
)
),
[Measures].[test doorlooptijd aansluiting])

eazybi
eazybi918×662 61 KB

2

Hi @Henk!

The problem with this formula is that there is one too many paranthases:

Please try the following:

AVG(Filter(
   Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
  (
    [Measures].[Transitions to status],
    [Transition Status].[12_MBP_Dossier_afronden]
  )>0 OR
  (
    [Measures].[Transitions to status],
    [Transition Status].[13_MBP_In_productie]
  )>0
),
[Measures].[test doorlooptijd aansluiting])

Additionally, while your approach is ok, I would suggest to create an aggregate of Statuses in the Transition Status dimension and then use it in the measure formula as follows:

AVG(Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
(
  [Measures].[Transitions to status],
  [Transition Status].[12_MBP_Dossier_afronden]
) > 0 OR
(
  [Measures].[Transitions to status],
  [Transition Status].[13_MBP_In_productie]
) > 0
),
(
  [Measures].[Workdays in transition status],
  [Transition Status].[AggregatedStatuses]
)
)

Lauma / support@eazybi.com