Calculating Average Weekly WIP

[J.M.]

I'd like to calculate the average number of issues in progress from a week to week bases. 

If this can be done in eazyBI that would be great.

Thanks

[Janis Plume _eazyBI_]

Hi,

a little bit more details in this thread.

EazyBI provides a standard measure "Issues history" designed to capture the status of issues at a historical period. Please, check here for more about the import of issue history in the documentation site.

The number of issues at the end of the period in a specific status can be calculated using the "Transition status" dimension in a tuple. In your case, the calculation showing the historical count of issues in the "In progress" status will be:

([Measures].[Issues history],
 [Transition status].[In Progress])

You can use this measure in the report with the Time dimension to show the history of the issues in the status.

Calculation of the average needs using the "Avg" function. This function needs to have the set over which to do the calculation and a measure from which to make the average. In this case, we can take all the weeks of time dimension as the set and our new measure. The calculation would be following:

Avg(
 [Time.Weekly].[Week].Members,
 ([Measures].[Issues history],
 [Transition status].[In Progress])
)

The average function will use only those members having a non-empty value of the measure. Now our new calculations allow creating a report like this:

Kindly,

Janis, eazyBI support

[J.M.]

Follow this formula I cannot seem to populate the chart you displayed. Would you be able to show me you dimension/table set up?

I set the "Time" and "Transition Status" dimensions to Rows. Using the Week Hierarchy Level for Time and added the Tuple as stated above as a calculated member in Transition status.

I set Measures to Columns and selected "Issue History"