Forum Discussion
Defect age
That sample data appears to leave a lot of things out. For one thing, although there are different codes under the heading "Defect" it's not at all clear whether we're referring in each instance to the same product, or even the same serial number of the same product. So for starters, you'd need to be more specific as to "Defect of which instance of which product".... at least two more columns.
And since the dates in your sample don't relate to the dates in your textual description, that compounds the confusion.
Do you have an actual workbook started? Is it possible for you to provide an actual copy of that actual workbook? You can post it on OneDrive or GoogleDrive with a link pasted here that grants access.
Please see if attached document provides enough detail. Basically, when defect is submitted, it's New, then once defect status changes (column G) to Implementation Review, how many days did it take, and when status changes again, how many days did it take.......Hope that makes sense. thanksmathetes
What's the relationship between "MVP" and "Defect"? I don't see a pattern that I can make sense of, not that I've spent hours trying to figure them out.
How do you propose to deal with something like the first row in that sample? And any others where "date last updated" is present? Does it supersede "Created on Date"? Why don't you create a whole new row for an update? From a design point of view, it doesn't make a lot of sense to create a single row with two transactions in it.
Could you create a newer sample showing the results you expect for a representative few of the rows, explaining those results?
mathetes Hi, The MVP column represents the "phase" of the project and really has no relevance to this example. I inadvertently included it so I removed. I've been tracking the Created on Date which represents the date defect was opened. The Date Last updated reflects the date any comments, status, etc. changes. This allows me to filter on the most recent updates (deltas) without having to scroll through the hundreds of defects. There are multiple people updating this tracker so sometimes "Date last updated" is missing. As a result, are there additional details i should be capturing to make tracking the age simpler? Ultimately trying to create a mechanism to determine from the time the defect is opened--how long it takes Implementation Team to review the defect? How long does it take development to solution? How long is it taking the client to retest?, etc. I've provided a newer sample with expected results so hope this is clearer. Thanks for all of your help!
Well, actually, what I now see has me more confused than ever. How can one possibly know how long Defect #2499 is in Status "New" when Defect 2499 has only one entry. Yes, it shows a "last updated" date that differs from the "Created on" date, but that's most pointedly NOT a change in status.
[I'm accustomed to tables like this being accounts of the history of various things--people, orders, inventory movements, whatever--where there's a new row for each transaction. {Specifically, I was the director of an HR database system for a major corporation. We recorded the history of events regarding an employee's tenure with the company: hire, location, promotions, marital status changes, etc. etc. Each row had a date and each row identified the employee. It was very easy to calculate--to analogize to your situation--how many days/months/years a person worked at location X or in job Y. You'd find the row where one of those things changed for employee SSN 111-22-3333 and calculate the days between dates.}]
You (to my outsider's eyes) don't have any history for Defect 2499, beyond being "created" and "updated," yet you somehow come up with
So where do you get the counts of days in yellow from??!!
Can you back up and tell me what this is about in the first place. It would appear to be some kind of product or project design and development. Perhaps a software product. Whatever. You did have phase there, yet now are saying it had "no relevance," which I'd question. It might not have immediate bearing on the history of a given defect, but surely it has relevance to the history of the project.
But most important for this tracking of defect aging, you need to be collecting explicit data on the history of Defect 2499, Defect 2500, Defect 2501, etc. And history would take the form of another row in your database for each given defect, the date it reached that new status, the category or description of that status.
To do this you'd have to have separate entries, separate dated rows, for Defect ID 2499 (and each of the others) where status becomes
- Implementation Review
- Development
- Pending Code Development
- Pending retest
- Pending Certification
- Close
And, if I were doing this, I'd eliminate the "Date last updated" column and make it a row of its own. It may be a continuation of status "New" but it is additional data in history of some kind. After all, you want the history to be accurate, which means complete, reproducible. You don't want to have an update take the form of writing over some text in the "Description" column, thereby obliterating part of the corporate memory.