Need help on building a report to display Issues aging on timeline

Questions & Answers
, , ,
1

Hi folks,

Need help!!!

I’m working on a formula that aims to plot issue aging on a timeline and encountered an issue where the formula completely ignores the Page Filter unless I explicitly add [Issue Type] and [Project] validations in the filter.

This approach works as long as we select a single Issue Type on Page Filter.

Here’s the formula I’m using:

Avg(
    Cache(
    Filter(
      Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      InStr(Generate(EXISTING [Issue Type].[Issue Type].Members, [Issue Type].CurrentMember.Name, ", "), [Measures].[Issue type]) > 0
      AND
      InStr([Measures].[Issue Project],[Project].CurrentHierarchyMember.Name) > 0
      AND
      DateCompare([Measures].[Issue created date], [Time].CurrentHierarchyMember.StartDate) <= 0
      AND
      (IsEmpty([Measures].[Issue resolution date]) 
      OR
      DateCompare([Measures].[Issue resolution date], [Time].CurrentHierarchyMember.StartDate) >= 0)
    ))
  ,
  DateDiffDays(
    [Measures].[Issue created date], 
    [Time].CurrentHierarchyMember.StartDate
  )
)

In this formula, [Measures].[Issue type] always returns a single value (e.g., ‘Task’). However, [Issue Type].CurrentHierarchyMember.Name may return either a single value or AggregatePageMembers when multiple issue types are selected on the page filter. So I came up with this idea: InStr(Generate(EXISTING [Issue Type].[Issue Type].Members, [Issue Type].CurrentMember.Name, ", "), [Measures].[Issue type]) > 0 in which consists in iterating PageMembers list, checking if each issue type maches the ones I filtered but It fails on multi-selecting.

In addition, I haven’t had the chance to check it yet, but I believe that once an issue from the set is done or cancelled, it might alter the past in the chart which would make past analysis very unaccurate.

I’m hoping you guys have a better logical and performatic approach.

Thank you very much in advance.

2

I tried to debug it by printing the current selected Issue Type Page Members and it returns {}.
SetToStr([Issue Type].CurrentHierarchyMember.Children)

It smells like a defect or something.

3

Hi @stefano.matos

Your formula is missing a numerical measure that would connect the calculations to selected page filters.
Try this formula for your calculation:

Cache(
CASE WHEN
 NOT [Project].CurrentHierarchyMember is [Project].CurrentHierarchy.DefaultMember
 AND
 [Issue].CurrentHierarchyMember is [Issue].CurrentHierarchy.DefaultMember
THEN
--optimized iteration
Avg(
    Filter(
         Generate(
          --set of relevant projects
            Filter(
              [Issue].[Project].Members,
              DefaultContext(([Measures].[Issues created],[Issue].CurrenthierarchyMember,[Project].CurrenthierarchyMember))>0
            ),
          --set of issues in filtered projects
           DescendantsSet(
            [Issue].CurrentHierarchyMember,
            [Issue].[Issue])
           ),
      DateCompare([Measures].[Issue created date], 
      [Time].CurrentHierarchyMember.StartDate) <= 0
      AND
      (IsEmpty([Measures].[Issue resolution date]) 
      OR
      DateCompare([Measures].[Issue resolution date], [Time].CurrentHierarchyMember.StartDate) >= 0
      )
    )
  ,
  CASE WHEN
  (
    [Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember
  )>0
  THEN
  DateDiffDays(
    [Measures].[Issue created date], 
    [Time].CurrentHierarchyMember.StartDate
  )
  END
)


ELSE
--general iteration from issues

Avg(
    Filter(
      Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      DateCompare([Measures].[Issue created date], 
      [Time].CurrentHierarchyMember.StartDate) <= 0
      AND
      (IsEmpty([Measures].[Issue resolution date]) 
      OR
      DateCompare([Measures].[Issue resolution date], [Time].CurrentHierarchyMember.StartDate) >= 0
      )
    )
  ,
  CASE WHEN
  (
    [Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember
  )>0
  THEN
  DateDiffDays(
    [Measures].[Issue created date], 
    [Time].CurrentHierarchyMember.StartDate
  )
  END
)
END
)

Note I’m having two blocks (for optimal calculation steps in scenarios where you select a project in page filters)
And I’m using “Issues created” measure with Time.currenthierarchy.defaultmember to create the link between page filters and calculation steps.
Because of this construction the calculation will listen to page filters (project and issue type)
And don’t forget to select the “decimal” format for this measure. eazyBI will not be able to detect the default format automatically for such complex formula.

Martins / eazyBI

1 Like
4

Thank very much you @martins.vanags !
I knew I could count on you to help me out!
It worked like a charm.