Average of issues created by week

Questions & Answers
1

I am trying to build a report that will show me the average number of issues opened per week over the course of a date range.

For a complete year, I can do total (total issue count / 52 weeks), however life is not that easy. :slight_smile:

I need to do (total issue count / (number of weeks between Oct 8 2018 and Dec 31 2018) which I could figure manually, but my reports tool should be able to generate my report without my outside math. :slight_smile:

And then I also need to do (total issue count / (number of weeks YTD). Since the number of weeks are constantly changing in this one, I cannot hard code a value.

Everything I am finding is showing averages for durations between date fields on an issue, like average age or average time in status, but my divisor in this case is the number of weeks in the range, and I can’t find any examples, posts, or articles that even seem to hint at this, and nothing I can find is producing results that are anywhere near realistic.

2

UPDATE:

Finally got it through a LOT of trial and error lol.
CASE
WHEN
DateBetween([Time].[Year].CurrentHierarchyMember.CurrentMember.Name,‘2018’,‘2018’)
THEN
Avg( LastPeriods(12, [Time.Weekly].[Week].DateMember(‘2018-12-30’)), CoalesceEmpty([Measures].[Issues created],0))
WHEN
DateBetween([Time.Weekly].[Year].CurrentHierarchyMember.Name,‘2018-12-31’,‘2019-12-31’)
THEN
(Avg(LastPeriods(12, [Time.Weekly].[Week].DateMember(Now())), CoalesceEmpty([Measures].[Issues created],0)))
END

3

Hi Jeff,

I’m happy to see that you have figured out a formula that works for you!
I just wanted to add that starting eazyBI version 5.0 and in Cloud there are standard calculations available that can get average dynamically based on e.g. filtered period of time.

Also, starting this version there is the VisibleRowsSet() function which can be used also in other custom calculations.

Let me know if you have further questions regarding this!
Lauma / support@eazybi.com