Number of unestimated issues of type story in project in time

jira
calculatedmembers
#1

Hi,

I’m totally new to the topic on EazyBI. I would like to setup a report/chart where I could show on X-time-axis (weekly) the amount of issue (type Story) which do not have Story Points.

The following should have affect on the count:

  • Adding new Story w/o SP --> +1 to the count in the given week
  • Closing a story w/o SP --> -1 to the count in the given week
  • Estimating (setting SP) a non closed story --> -1 to the count in the given week
  • Removing estimation (removing SP) from a non closed story --> +1 to the count in the given week

Can anyone advise on how to achieve this? I was trying to find some guidance in this forum, but w/o luck. ;(

0 Likes

#2

eazyBI has several measures to count story points history over time. However, we do not have a default measure to count issues with no story points over time.

We have default measure (hidden) to count the current amount of issues with story points Issues with Story Points created and you can use subtraction to get a count without Story Points subtracting this measure from all Issues created.

However, you are asking for a historical measure here. You would like to use a calculation on issue level:

Sum(
  Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
 -- filter by open issues in period
    DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Created at'),
        [Time].CurrentHierarchyMember) AND
    DateAfterPeriodEnd(
      [Issue].CurrentMember.get('Resolved at'),
      [Time].CurrentHierarchyMember) AND
-- filter by issue type
    [Measures].[Issue type] = "Story"  
    ), 
    CASE WHEN 
-- filter for issues with no story points in period
      CoalesceEmpty([Measures].[Story Points history],0) = 0
    THEN 
-- count of issues based on all filter criteria
     NonZero(([Measures].[Issues created count],
      [Time].CurrentHierarchy.DefaultMember))
    END
)

Daina / support@eazybi.com

0 Likes

#3

Sorry for a delay in response here… did not have time till recently to follow this topic up.

First of all thank you for your guidance. It helped me understand the physics of EazyBI a bit better.
Also I think that the Resolved at period condition does not take into account the issues which are not yet resolved.

I modified the query a bit and at the moment I’m working with this calculation:

Count(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    -- filter by issues created before period and and not closed before period end
    DateBeforePeriodEnd(
      [Issue].CurrentMember.get('Created at'),
      [Time].CurrentHierarchyMember) AND
    ( IsEmpty([Issue].CurrentMember.get('Resolved at')) OR
      DateAfterPeriodEnd(
        [Issue].CurrentMember.get('Resolved at'),
        [Time].CurrentHierarchyMember)
    ) AND
    -- only issues of type Items with SP (it's an aggregation of several issue types)
    ([Measures].[Issues created],
      [Issue Type].[Items with SP],
      [Time].CurrentHierarchy.DefaultMember) > 0 AND
    -- only the ones which do not have story points
    IsEmpty([Measures].[Story Points history])
  )
)

The issue which I’m facing right now is the performance of this calculation. It times out most of the times. Could you advise how I could make it more efficient?

0 Likes

#4

I can agree. The calculation is quite tough. Therefore I used a bit different approach in my formula I suggested for you. Sorry, I missed the part of unresolved issues.

I will walk you through how I made my formula for maximal performance optimization. The main idea is to split filters, using filtering by issue properties and only after that apply any measure to already filtered issue set.

I used SUM function instead of COUNT for splitting filters into two parts.

I used only issue properties as criteria to Filter initial issue set.
Filter(All issue set, properties as filter criteria only)

Then I used SUM over this filtered issue set:

SUM (filtered issue set, numeric expression)

where numeric expression uses measures Story Points history and Issues created as filters within CASE <> WHEN <> THEN <> END

Measure Story Points history will check if there is no value. Measure Issues created is the main filter for this formula. Measure Issues created works as a counter (numeric expression) for SUM function as well.

You are using a calculated member in Issue type dimension Items with SP in a tuple with measure Issues created. It aggregates several issue types and will multiply any calculation by a number of aggregates issue types. If you are facing performance issues, avoid calculated members if you can. You can use filter by issue property [Meausre].[Issue type] and use MATCHES there to filter by several issue types there.

If the formula still does not work in the account. You can consider minimizing the issue amount per account.

Daina / support@eazybi.com

0 Likes