Improve the performance of this Filter for Flow Time

Questions & Answers
1

Hi!
we have this report where we are measuring, from the Flow Framework, the Flow Time (from In Progress until Closed).
For that, we are providing the possiblity to select the Flow Items and their final status, either Resolved or Closed.
And for the Time, I created an Aggregation to display the last 2 Halves of the year, depending today.
However, it takes long time to load the results. How can it be improved?
Note: I can’t use the Issue Cycle configuration because I am implementing in template account to be distributed automatically to the rest of accounts.

image
image1690×1158 77 KB

Median Issues Resolved working days:

NonZero(Median(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    DateInPeriod(
      [Measures].[Issue resolution date],
      [Time].CurrentHierarchyMember
    )
    AND
    (
      [Measures].[Issue status] = "Closed" OR 
      [Measures].[Issue status] = "Resolved"
    )
    AND (
    [Measures].[Issue resolution] = "Fixed"
    OR
    [Measures].[Issue resolution] = "Done"
    OR
    [Measures].[Issue resolution] = "Configuration"
    OR
    [Measures].[Issue resolution] = "Ready"
    
    ) 
    AND 
    (
    [Measures].[Issue type] <> "Sub-task"
    OR 
    [Measures].[Issue type] <> "Sub Task Defect"
    OR 
    [Measures].[Issue type] <> "Test"
    OR 
    [Measures].[Issue type] <> "Test Set"
    OR 
    [Measures].[Issue type] <> "Test Execution"
    OR 
    [Measures].[Issue type] <> "Pre-Condition"
    OR 
    [Measures].[Issue type] <> "Epic"
    OR 
    [Measures].[Issue type] <> "Initiative"
    OR 
    [Measures].[Issue type] <> "Milestone"
    
    
    )
    AND
    ([Measures].[Issues resolved count],
     [Time].CurrentHierarchy.DefaultMember,
     [Transition Status].[Flow Time]) > 0),
-- total time per cycle

   ([Measures].[Workdays in transition status],
    [Time].CurrentHierarchy.DefaultMember)
    
  )
)
2

Hi @Nacho

Iterating through “Issue” dimension all imported issues for each month is a complex task for the query.
You might want to try this formula instead:

CASE WHEN
   [Measures].[Issues resolved]>0
THEN
NonZero(Median(
  Filter(
    DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    DateInPeriod(
      [Measures].[Issue resolution date],
      [Time].CurrentHierarchyMember
    )
    AND
    (
      [Measures].[Issue status] = "Closed" OR 
      [Measures].[Issue status] = "Resolved"
    )
    AND (
    [Measures].[Issue resolution] = "Fixed"
    OR
    [Measures].[Issue resolution] = "Done"
    OR
    [Measures].[Issue resolution] = "Configuration"
    OR
    [Measures].[Issue resolution] = "Ready"
    
    ) 
    AND 
    (
    [Measures].[Issue type] <> "Sub-task"
    OR 
    [Measures].[Issue type] <> "Sub Task Defect"
    OR 
    [Measures].[Issue type] <> "Test"
    OR 
    [Measures].[Issue type] <> "Test Set"
    OR 
    [Measures].[Issue type] <> "Test Execution"
    OR 
    [Measures].[Issue type] <> "Pre-Condition"
    OR 
    [Measures].[Issue type] <> "Epic"
    OR 
    [Measures].[Issue type] <> "Initiative"
    OR 
    [Measures].[Issue type] <> "Milestone"
    )
     ),
   ([Measures].[Workdays in transition status],
    [Time].CurrentHierarchy.DefaultMember)    
  )
)
END

Note that you are required to manually select the “Integer” or “Decimal” output format for the measure, as eazyBI might not detect the format automatically for this formula.

Martins / eazyBI

3

ey @martins.vanags thanks for it.
what about the time between In Progress? I see it was removed from the formula. Because now, it is taking the time from Open to closed, isn’t it?
Also, tried adding the “Flow TIme” aggregation we have, and it appears empty.

[Transition Status].[Flow Time]:

  [Transition Status].[In Progress]+	
  [Transition Status].[Integration]+	
  [Transition Status].[Reopened]+	
  [Transition Status].[Ready for Review]+
  [Transition Status].[In Review]+	
  [Transition Status].[Blocked]+		
  [Transition Status].[Ready for Test]+
  [Transition Status].[In Test]+	
  [Transition Status].[Resolved]+
  [Transition Status].[In Verification]+
  [Transition Status].[Deployment]