Transition to Status by Week

Questions & Answers
1

Goodmorning everyone,
I created a report that tells me the number of issues, open in a certain time interval, which have passed through certain statuses and created the trend over time.

However, by manually verifying the issues, I do not get the data back. I generally use weeks as a time frame.
By changing the time interval and entering the month as the unit, the counts are correct.

NonZero( 
   Count(
      Filter(
         Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
         Aggregate({
          ([Measures].[Transitions to status], [Transition Status].[StatusName1]),
          ([Measures].[Transitions to status], [Transition Status].[StatusName2]),
          ([Measures].[Transitions to status], [Transition Status].[StatusName3]),
          ([Measures].[Transitions to status], [Transition Status].[StatusName4])
          }) > 0
         AND DateInPeriod(
          [Measures].[Issue created date],
          [Time].CurrentHierarchyMember)
        )
      )
   )
)

It seems the date filter is also applied to the transition date, but that doesn’t interest me.

I would need a report, which shows me how many of the issues opened in the course of a week have passed through at least one of the statuses specified by me. How can I do?

Thanks for the support!

2

You are almost there with this formula. Yes, you are correct. The eazyBI counts transitions by time. This impact results in the formula.

I would suggest ignoring time for counting the transitions for particular statuses. You would like to use some tuple there.

  1. You can add Time default member to the tuple for transition statuses
NonZero( 
   Sum(
      Filter(
         Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
         DateInPeriod(
          [Measures].[Issue created date],
          [Time].CurrentHierarchyMember)
        ),
       CASE WHEN       
          Sum({
          [Transition Status].[StatusName1],
          [Transition Status].[StatusName2],
          [Transition Status].[StatusName3],
          [Transition Status].[StatusName4]},
          ([Measures].[Transitions to status],[Time].CurrentHierarchy.DefaultMember) -- tuple with time default member, any report selection will impact this
          ) > 0
        THEN 
        [Measures].[Issues created]
        END
   )
)
  1. You can use DefaultContext and validate only the transition to specific status for the issue, ignoring any other report selections.
NonZero( 
   Sum(
      Filter(
         Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
         DateInPeriod(
          [Measures].[Issue created date],
          [Time].CurrentHierarchyMember)
        ),
       CASE WHEN       
          Sum({
          [Transition Status].[StatusName1],
          [Transition Status].[StatusName2],
          [Transition Status].[StatusName3],
          [Transition Status].[StatusName4]},
          DefaultContext(([Measures].[Transitions to status], [Issue].CurrentMember, [Transition Status].CurrentMember)) -- tuple in default context will check only isuse and specific transitions
          ) > 0
        THEN         
        [Measures].[Issues created]
        END
      )
   )

Please set the formatting for those measures to an integer.

I used a bit different way on how to count transition changes for the issue using Sum (you can use Aggregate there as well) over a set of statuses. In my opinion, it is easier to update statuses and maintain changes if needed for tuples. But both approaches will work. The main problem was ignoring time for this measure.

Daina / support@eazybi.com