EazyBi formula to get # of issues moved to a particular status in last 14 days

Questions & Answers
1

Hi Team,

I am trying to get the # of issues that are moved to a particular(custom) status in last 14 days. I am able to achieve this using different dimensions, but I am looking for a single user-defined formula to achieve the same. Could you please help me with that?

Thank you!
Keerthana B

2

Hi @Keerthana_Bhuthala,

The best way to get the issue count that transitioned to a specific status would be by creating a tuple with all the measures:

1 - Create the Last 14 days calculated member in the Time dimension (similar as default “Last 30 days” calculation):

Aggregate(
  [Time].[Day].DateMembersBetween('14 days ago', 'today')
)

2 - Add a measure where you specify which measure, Transition status (I used “In Progress” as example), and the Time period you are looking for:

(
    [Measures].[Transitions to status issues count],
    [Transition Status].[In Progress],
    [Time].[Last 14 days]
)

Lauma / support@eazybi.com

3

How could this be modified to show only the latest transition to status date value.

For example - using the code above and looking a the current week if an issue has a transition status of Fail and in the same week there is a transition status of Pass it will be counted twice.

Looking to use only the latest transition date so, in the example above, we would only see that issue counted in the number transitioned to Pass for the week and not the Fail.

If it went into Fail in a previous week that should show on that week in the fail count since the latest transition date within that week would be of a status change to fail.

Thank you.

4

Yes, you are correct. Transition status dimension with several statuses Fail and Pass in the report eazyBI will count issues for each status independently. Therefore, the issue might appear twice if it had status changes to both statuses.

You can use measure Issues history to see the status at the period end. It will include issues with activities in previous periods as well, though.

If you would like to count unique issues in either Fail or Pass in the period, you would like to use a more complex formula to detect the latest status change. Here is an example for this:

NonZero(SUM(
  Filter(
   Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    -- filter issues in active period - it can include issues in several periods from isuse creation till the latest status change - if there were any status change
       IIF([Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember,
       1,
       DateBeforePeriodEnd(
         [Issue].CurrentHierarchyMember.Get('Created at'),
         [Time].CurrentHierarchyMember)
       AND DateAfterPeriodEnd(
         [Issue].CurrentHierarchyMember.Get('Status updated at'),
         [Time].CurrentHierarchyMember.PrevMember)       
       )
   ),
   CASE WHEN 
   -- compare the latest change to any status in period to the latest change to the status used in the report
   ([Measures].[Transition to last timestamp],
    [Transition Field].[Status],
    [Transition Status].CurrentHierarchy.DefaultMember)
    = 
   ([Measures].[Transition to last timestamp],
    [Transition Field].[Status])
   THEN 1 END
))

Daina / support@eazybi.com

5

Hi @daina.tupule

I have a query, I want to be able to visualize the subtasks that finished (I don’t care about the day) independent of the time filter that I have in the pages that filters me by the time in which the stories finished

6

It seems you are looking for a different scenario than described in the topic. Can I assume, you would like to count stories and sub-tasks as finished if they are resolved?

Here is a formula to count resolved sub-tasks based on the parent issue type - Story and parent issue resolution date.

Sum(Filter(
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
 -- apply the formula for sub-tasks only
  IIF (not IsEmpty( [Measures].[Issue parent key] ),
  -- check the parent issue type = story
    [Issue Type].[Issue Type].GetMemberNameByKey(
      [Issue].GetLinkedMember('Parent issue key').Get('Issue type ID')
    ) = "Story"
    AND
    DateInPeriod(
    -- filter by parent issue - resolution date
     [Issue].GetLinkedMember('Parent issue key').GetDate('Resolved at'),
     [Time].CurrentHierarchyMember
  ), 0)),
  -- sub-tasks resolved
  ([Measures].[Issues resolved], [Time].CurrentHierarchy.DefaultMember
))

Daina / support@eazybi.com