Avg Resolution Workdays excluding statuses

Questions & Answers
2

Hi Joe,

There are several ways to create such a report to exclude chosen transition statuses from the total calculation.

One of the easiest and fastest would be subtraction on the level of totals.
We already have a predefined measure ‘Total resolution workdays’, from which we may subtract the exclusions.

First - let us define the statuses for exclusion - we will create a calculated member within the transition status dimension and call it ‘aggregated exclusions’.

Aggregate({[Transition status].[Open],
           [Transition status].[On Hold]})

When we have aggregated the exclusions, we will be able to address them directly in a tuple.
There are two conditions for the time spent in the transition status:

  1. issue has been resolved;
  2. transition status is one of the exclusions.

So the exclusion tuple will consist of the above clauses.

([Resolution].[Done],
 [Transition status].[aggregated exclusions],
 [Measures].[Workdays in transition status])

Now - we can take the expression from the ‘Average resolution workdays’ and adjust the ‘Total resolution workdays’ by the time we want to exclude.
So the final expression is as follows:

CASE WHEN [Measures].[Issues resolved] > 0 THEN
 ([Measures].[Total resolution workdays]
 -
 ([Resolution].[Done],
  [Transition status].[aggregated exclusions],
  [Measures].[Workdays in transition status])
 ) / [Measures].[Issues resolved]
END

You can also find a similar topic in community pages about the average time spent in a transition status -Average workdays in transition status exclusion.
Please do not hesitate to look for inspiration and samples in our demo account - https://eazybi.com/accounts/1000/dashboards/5942-project-overview.

Kind regards,
Oskars / support@eazybi.com