Average age of open issues -Workdays only-

CALMAIRE DOOH (CONT)

I am pretty new to Eazybi and  trying to put on some reports for my team.

I was able to use the formula below to find the average age of open issues, but what i need is the average age of open issues IN WORKDAYS only and this formula below is giving me workdays and weekends.

It is important to note that, my workday settings are Monday through Friday.

I do not know what to do in order to have a result only in workdays.

Please help

This the formula:

CASE WHEN [Measures].[Open issues] > 0 THEN
Avg(
Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Open issues] > 0),
CASE WHEN DateInPeriod(Now(), [Time].CurrentHierarchyMember)
THEN DateDiffDays([Issue].CurrentMember.get('Created at'),
Now())
ELSE DateDiffDays([Issue].CurrentMember.get('Created at'),
[Time].CurrentHierarchyMember.NextStartDate)
END
)
END

Zane eazyBI Support

Hi,

You may slightly modify the formula to apply calculation only for working days according to eazyBI settings excluding weekends and listed holidays. 
Update calculation and use function DateDiffWorkdays() instead of DateDiffDays():

CASE WHEN [Measures].[Open issues] > 0 THEN
  Avg(
    Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
      [Measures].[Open issues] > 0),
    CASE WHEN DateInPeriod(Now(), [Time].CurrentHierarchyMember)
    THEN DateDiffWorkdays([Issue].CurrentMember.get('Created at'),
      Now())
    ELSE DateDiffWorkdays([Issue].CurrentMember.get('Created at'),
      [Time].CurrentHierarchyMember.NextStartDate)
    END
  )
END

 

For more information you may see eazyBI documentation:

• https://docs.eazybi.com/eazybijira/data-import/jira-issues-import#JiraIssuesImport-Timedimension
• https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-members/mdx-function-reference/datediffworkdays

 

Best,
Zane / support@eazyBI.com