Report which calculates the average of the datediffworkdays between two fields

Questions & Answers
1

I would like to report where the measure I report on is DateDiffWorkdays([Issue].get(‘Issue Development Started’),[Issue].get(‘Issue Development Finished’)). The two fields mentioned are custom fields we created. It seems the only way I can do this is to create an additional custom field “Development Time” where I store that value when the “Issue Development Finished” is recorded.

Is what I want possible without creating the field “Development Time”? Asking because I would like to calculate and report on other date differences.

1 Like
2

You can use two options on how to calculate date difference between two custom fields.

MDX calculations. You would need to use issue level calculation to get a time difference between two dates. Here is an example of issue level calculation for this:

NonZero(Avg(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    -- filter by time when the value for any issue should be counted
    DateInPeriod(
      [Measures].[Issue Start date],
      [Time].CurrentHierarchyMember
    )
  ),
  CASE WHEN 
  -- filter by any other dimension selection on pages
  ([Measures].[Issues created],
  [Time].CurrentHierarchy.DefaultMember) > 0
  THEN
  -- numeric argument for Average - days difference between two dates
  DateDiffDays(
    [Measures].[Issue Start date],
    [Measures].[Issue end date]
  )
  END
))

MDX calculations will be calculated during report execution. Issue level calculations can work slow in accounts with a large number of issues. We suggest using MDX application if they are account specific or serve specific scenarios for some accounts/projects/teams.

JavaScript calculations. You can define a new custom field in eazyBI with a JavaScript calculation.
In the example (second example for interval dimension) above, we are calculating a difference between two dates (issue creation date and some custom field date). The example definitions include import as an interval dimension for this custom field. You can skip dimension = true and interval setup in the definition and define an import option measure = true only for the field.

It seems you are analyzing some cycles for your issues. You can check this dashboard in our demo account for more ideas on how to analyze this. If you are using one status per cycle default measure Average days in transition status could work for you. There are some other reports on more complex cycles as well.

Daina / support@eazybi.com