Defining a rolling window of time dimension

Questions & Answers
1

Howdy!

I’m looking for a way of visualizing whether the cleanliness of our submissions is improving over time.

To achieve that, I’m using the following calculated member on a timeline to show the number of bugs created per story that has been tested:

Sum({PreviousPeriods([Time].CurrentHierarchyMember)},([Measures].[Issues created], [Issue type].[Bug]))
/
Sum({PreviousPeriods([Time].CurrentHierarchyMember)},([Measures].[Transitions to status], [Transition Status].[PO Review]))

The issue I’m facing is that these aggregated values overtime will normalize to the point of being useless. For the first week or so, the data is quite volatile, but over time the totals of each building on one another (500:250 bugs to stories tested) will show such nominal gains or losses that it almost flat lines.

Removing PreviousPeriods([Time].CurrentHierarchyMember) just gives me daily snapshots in time which isn’t very useful either.

I’d really like to be able to specify a rolling window in the calculated memory…something like “Total bugs created in the last 4 weeks / total stories sent to test in the last 4 weeks”

Possible?

2

Hi!

Yes, instead of all previous time periods, you may define any time range. Using CurrentDateMember you may refer to the current week and then move in time, for example, using function Lag() .

The following calculation would give the result based on last full 4 weeks (not including the current week):

Sum(
 {[Time.Weekly].[Week].CurrentDateMember.Lag(4):[Time.Weekly].[Week].CurrentDateMember.PrevMember},
 ([Measures].[Issues created],
  [Issue type].[Bug])) /
Sum(
 {[Time.Weekly].[Week].CurrentDateMember.Lag(4):[Time.Weekly].[Week].CurrentDateMember.PrevMember},
 ([Measures].[Transitions to status], 
  [Transition Status].[PO Review]))

If you additionally want to use Time in the report, then use weekly hierarchy.
Ilze, support@eazybi.com