Remove small values from report

I am using the “Average Workdays in Transition status” measure to show the average time that a group of issues spent in each status.

PROBLEM:
We have a linear workflow with auto transitions.
The issues checks for a specific criteria when an issues reaches each status inthe linear workflow.
If the criteria is not met for that status, automation then transforms the issue to the next status.

Management does not want statuses that were skipped during the issue workflow process.

The skipped status have a very small time in status value (a few seconds).

The EazyBI report using the “Average Workdays in Transition status” is returning those skipped statuses with those small values and those skipped statuses are used in the aggregation.

This is skewing the report numbers.

Question:

How can I filter out the skipped statuses and not have them count in the aggregation?

Hi @AARONSR22,

eazyBI sees all status transitions from issue history regardless of their length and meaning. And all of the transitions are considered when calculating the “Average workdays in transition status”. The logic for calculation is to get all time in transition statuses and divide it by the transition count.

In your case, you might create a calculated measure to calculate the average workdays in transition status differently. The specifics of the calculation might differ based on the report you are building (what dimensions are on report rows and columns, and how do you plan to filter data) and how many transition statuses you are considering for the report.

  1. One option could be to get all time in transition status (including those few seconds in skipped statuses) and divide it by issue count, not transitions. The expression would be similar to “Average workdays in transition status” but using measure “Transitions from status issues count” to divide total duration:

    CASE WHEN [Measures].[Transitions from status] > 0 THEN
      [Measures].[Workdays in transition status] /
      [Measures].[Transitions from status issues count]
    END
    

    This approach is quite good if you are looking for the average duration in a cycle for several statuses, like all Development process statuses.

  2. Another approach for calcaution might be to iterate through all issues individually and check the time each issue spent in the selected Transition Statuses. If the duration is too little, then ignore it. The structure for calcaution might look like this:

    Avg(
      --set of issues
      Filter(
        --iterate through individual issues
        DescendantsSet([Issue].CurrentMember,[Issue].[Issue]),
        --filter issues with time in specific status more than 1 minute
        ([Measures].[Workdays in transition status],
        [Transition Status].[In Progress]) > 0.000694
      ),
      --time in specific transition status
      ([Measures].[Workdays in transition status],
      [Transition Status].[In Progress])
    )
    

    The second approach will work if you want to check the duration of one specific transition status.
    Note this calcaution might affect report performance as it iterates through all issues in the cube for each report cell.

More details on calculated measures and used functions are described in the documentation: Calculated measures.

Best,
Zane / support@eazyBI.com