June 13, 2013

How many working hours since my item was created?

Helix ALM
Issue Management
Tracking the time since an item was created can be important, but how do you take working hours and weekends into account? You need to make sure  you are giving most attention to the items that most need it. Often this means that you want to be able to give particular attention to items that are late or will soon be late. For example a defect that has been open for too long or a help desk issue that has not been addressed within its SLA. Many customers I talk to address this in TestTrack by creating reports, filters, and escalation-type automation rules based on the "Date Created". These provide reports and automated email notification when items have exceeded a certain length of time without the appropriate action being taken. This is great but one issue is how to deal with working hours and weekends. For example, if you want to ensure an item cannot be in an open state for more than 16 hours without being assigned, you might want to be emailed when an item is open for 15 hours. That's fine, but how do you deal with an item raised on Friday afternoon? Come Monday morning you will get an email for every Friday afternoon item even though they are not really late. You have a similar problem for the working day when items are raised close to 5pm. This issue was tricky to deal with in the past but now, using TestTrack's new calculated fields functionality, you can take into account your working hours and weekends.

Calculated Fields Example

This example has two custom fields: "Working hours since creation" and "SLA status".
  • The "working hours since creation" field is the number of hours since the "date created" time but only includes time between 9:00 and 17:00 (the time I have designated working hours) and does not include weekends.
  • The "SLA status" field is either OK (< 60 hours), close to overdue(<240), or Overdue (> 240 hours) depending on how long since an item was created.
To recreate this in TestTrack, do the following: 1. Add two new custom fields to hold the Start and End time. Use the 'required fields and default values' option from the admin menu to set these to decimal values representing time (e.g., 9.30 and 17.00). (It is probably a good idea to make them only visible to admin security group.) 2. Create a new custom field to hold the working hours since creation. Add the appropriate formula using the script language. Here is the script I used: Working hours calculation scriptTIP: You need to set the "UTC offset" parameter depending on your time zone. For example, I'm in the UK, and I am one hour ahead of Coordinated universal time (UTC) so I need this to be set to 1. In east coast of USA (EST) this would need to be "-4".  See here: I'm sure some of you can think of  a better way of doing this. 3. Create the SLA status field as a custom field as shown below: As you can see this references the "working hours since creation" field to calculate the status.


Please feel free to use this example and amend as you will. Be aware that you do so at your own risk as the scripts are provided for example purposes 'as is'. This is probably as complicated as you can get using calculated fields. If you don't want to do this yourself but still want the functionality, then please get in touch with me or our professional services team and we will be happy to give you a quote to build a customised script for you.