Running totals weekly distribution for issues

Questions & Answers
#1

Hi,

I have a “glidepath” table report which shows the Created / Resolved / Open issue weekly numbers, currently as a discrete values.
Created / Resolved / Open issue columns can be extended by Component, Severity and Status.
In rows there is the Time dimension by Week, and there is a timeframe from a specific start date to “Today”.

What I would like to get is

  • Instead of discrete values I would like to see the running totals (cumulative sum) for the rows
  • Aggregation of numbers must start from the given “Start” time
  • Open issues (Created - Resolved) also needs to count within the given timeframe (currently counts everything)

The current Open issues member is

CASE WHEN [Issue].CurrentMember.Level.Name <> 'Issue' THEN
  Cache(
    NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
      Cache([Measures].[Issues created]
          - [Measures].[Issues resolved])
    ))
    + [Measures].[Issues created]
    - [Measures].[Issues resolved]
  )
WHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember
THEN NonZero([Measures].[Issues due])
ELSE
  -- optimized formula for drill through Issue
  NonZero(IIF(
      DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Created at'),
        [Time].CurrentHierarchyMember) AND
      NOT DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Resolved at'),
        [Time].CurrentHierarchyMember),
    ([Time].CurrentHierarchy.DefaultMember,
      [Measures].[Issues created]),
    0
  ))
END

report
report1874×562 306 KB

Your help on how is it made would be much appreciated.

Thanks,
t

#2

Hi @tamas,

I apologize for the late reply.

To get the cumulative “Issues created” and “Issues resolved”, you can try to use the Standard calculation feature introduced in eazyBI version 5.0.0. It will calculate the cumulative sum only for the row members currently visible on rows. Please read more about it on our documentation page - https://docs.eazybi.com/eazybijira/analyze-and-visualize/create-reports#Createreports-Addstandardcalculationsbasedonaselectedmeasure.

Please have a look at the picture below:

Screen Shot 2020-03-03 at 15.51.17
Screen Shot 2020-03-03 at 15.51.172110×1274 393 KB

There you can see that the Standard calculation measure “Cumulative issues created” sums up only values that are currently visible on the rows. Opposed to that, the calculated measure “Cumulative Issues created calc” retrieves values from all previous periods.

If you are looking for the number of issues that have been resolved that are created after the specific start date, then a new calculated measure is necessary. Please have a look at the formula below:

-- Issues created and resolved
Sum(
  Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    DateInPeriod(
      [Issue].CurrentHierarchyMember.get('Resolved at'),
      [Time].CurrentHierarchyMember
    )
    AND
    DateCompare(
      [Issue].CurrentHierarchyMember.get('Created at'),
      'Mar 3 2019'
    ) >= 0
  ), [Measures].[Issues resolved]
)

If you are looking to show the number of open issues that are created and resolved within the start date and today, you can create a new calculated measure that subtracts the resolved issues from the created ones. Please have a look at the formula below:

[Measures].[Issues created] - [Measures].[Issues resolved]

Or use the calculated measure “Issues created and resolved” and then, using the Standard calculations, create a cumulative sum of these still open issues.

Best,
Roberts // support@eazybi.com