Ageing chart of wip items

[Daniel_Sander]

Hey there,

I would like to create a chart like shown in the screenshot which should help my development team to easily understand, if issues, which they currently have in progress, start to run out of the average cycle time of past items.

I’ve tried to draw the idea like this.

• On one axes, the number of days in a transition status “In progress” is shown. (As in our process two transition status potentially relfect the “in progress” status - active time, inactive time), it would be good if that could be flexible enough to add the transition status you need
• In the chart all items are shown which are currently in that status including the number of days they are in the status
• As vertical lines, I would like to visualize percentiles of how long past items took (e.g. 50 % of items took 6 days, 75 % of items took 8 days,…).

My skills of setting up such a report a very limited.

I would be happy to get some support on this issue.

Best regards

Daniel

[ilze.mezite]

Hi, @Daniel_Sander

Welcom to the eazyBI community.

To create such a graph, let’s first start with the table view and define all the necessary measures.

Please select the Issue dimension in the rows to create such a chart.

You will need to create separate calculated measures for each percentile and also for “In progress” age (till now).

To find the age of the issue in the status “In Progress” till now, please create the new calculated measure using this formula (I called mine “Age in In Progress”):

CASE WHEN 
[Measures].[Issue status]="In Progress"
AND
[Measures].[Issues created]>0
THEN
DateDiffDays([Measures].[Issue status updated date],Now())
END

Filter the measure “Age in In Progress” > 0. Please find information here: Create reports

To find the 50th percentile, please create a new measure using Percentile. Please use Filter, to filter out all the relevant Issues.

Percentile(
 Filter(
   [Issue].[Issue].Members,
   [Measures].[Issue status] = "In Progress"
   AND
   [Measures].[Issues created]>0
 ),
 [Measures].[Age in In Progress],
 50)

Please create as many percentiles as possible by changing the 50 to the expected percentile. The report should look something like this:

Now, let’s format the data to look like your print screen. Switch into the Bar chart will give you something like this:

Bare with me:

1. For the Age in In Progress, please format the “Type” → “Scatter.”
   
   
2. For Percentiles, please format the “Type” → “Line” and the “Symbol” → “disable”
   
   

The Bar view, that looks like Scatter.

You can add the data labels and choose different colors. Read more about chart customizations here: Customize chart

Kindly,
Ilze

[Daniel_Sander]

Hi Ilze,

thank you for your warm welcome. Your explanation was awesome, I could easily follow the steps which resulted in the same chart as yours.

I would like to ask you for a small adjustment though. As far as I understand your chart correcty will the percentiles refer only on the issues listed. What I was rather looking for were percentiles referring of the past (already resolved items). I guess it gets a bit tricky here as the measure “Age In Progress” only considers issues in that specific state) and the measure “Percentiles” considers resolved items. Is there a way to adjust the query?

Kind regards,
Daniel

[Daniel_Sander]

Hi @ilze.mezite,
I just wanted to catch up with you one more time to see, if you already had time to review my latest comment or whether you need additional information from me.
Looking forward to your support.
Kind regards,
Daniel

[ilze.mezite]

Hi, @Daniel_Sander

To see the percentages from all the closed issues that were in the status “In Progress,” the formula should go through all the issues in the cube. That won’t be fast, and depending on the size of the cube, it might time out.

To help a bit, please add ConstantColumnValue, read here: ConstantColumnValue

--constantcolumnvalue to only calculate once for all report
ConstantColumnValue(
Percentile(
  Filter(
--all issues in data cube
    [Issue].[Issue].Members,
    ([Transition Status].[In Progress],
     [Transition Field].[Status],
     [Measures].[Transitions from],
--resetting time dimension to take ALL issues regardless of context
     [Time].CurrentHierarchy.DefaultMember)>0
  ),
  ([Measures].[Days in transition status],
   [Transition Status].[In Progress],
--resetting time dimension to take all issues
   [Time].CurrentHierarchy.DefaultMember),
--percentile
   90
))

This example is for the 90th percentile; please adjust.

Kindly,
Ilze

[Daniel_Sander]

Hi @ilze.mezite,
thanks a lot, awesome works as expected. At least for me, I had no time out problems, but thanks for creating awareness upfront.
Kind regards,
Daniel