Change the time value format

In Data Explorer, time values are often captured in seconds or milliseconds by default. Values such as work time, average handle time, contact duration, and so on are sometimes not easily understood when displayed as seconds in a report. You can change how those time values are shown in your reports by applying the DURATION content attribute to them. When you apply the DURATION attribute, you can specify the time format you want to use.

The time/date format is expressed using Unicode formatting strings. Some common formats are:

  • mm
  • hh:mm:ss
  • h ‘hours’
  • dd ‘days’
  • d ‘days’ hh:mm:ss

Literal strings, such as the words hours and days in the examples above, can be added by enclosing them in single quotes.

Guidelines for Unicode formatting can be found here:

https://unicode.org/reports/tr35/tr35-dates.html#Date_Format_Patterns

Seconds versus milliseconds

The DURATION content attribute converts any numeric value into a time duration format because it assumes the input is in seconds. If the original value is not in seconds, you need to write a formula to convert that value to seconds first. For example, if the field is in milliseconds, you would write a formula in a custom measure to convert it to seconds, like this:

[field in milliseconds]/1000

Where field in milliseconds is the name of the field in the database or your report that you want to convert to seconds.

For more information on custom measures, see Create a custom measure.

Time formats in exported reports

Applying the DURATION content attribute to a field will not change the exported results for that field. If a field is captured in seconds, even if you change the time value format to hh:mm in your report, when you export the report, the field will still be in seconds. This allows the report to be easily imported to other data and reporting products, such as Excel.

If you want your results to be formatted as time durations for export, you must create a custom measure with a formula like this:

format(<DURATION; PATTERN “hh:mm:ss”>,[field name])

where field name is the name of the field in the database or your report that you want to format.

There are many approaches that can be taken to do a milliseconds-to-seconds conversion. In this next example, the formula tests the field called [durationInMilliseonds] to see if contains a null value. If it does, it replaces that null value with a zero and then divides the result by 1,000 to get seconds.

ifNull([durationInMilliseconds],0)/1000

For more information on custom measures, see Create a custom measure.

Ways to format with DURATION

There are 4 ways you can apply the DURATION content attribute using the fields in the bottom panel of the measure’s dialog box.

DURATION attribute only

Enter DURATION in the Content Attribute field, and make sure the Format field is empty or displays <no formatting>. The default time value format associated with the attribute is h:mm:ss.SSS (hours, minutes, seconds, and fractional seconds).

DURATION attribute and format

Enter DURATION in the Content Attribute field and the desired format in the Format field.

DURATION and PATTERN attributes and format

Enter two attributes, DURATION and PATTERN, and specify the desired format in the attribute string.

For more information on how to write a content attribute string, see Configure a measure’s appearance and About content attributes.

Preconfigured content attribute

The drop-down field to the right of the Content Attributes field lets you apply preconfigured content attributes to the measure without having to enter anything in the Content Attributes field itself. There are several preconfigured DURATION attributes you can use. The one selected in this graphic applies the hh:mm:ss format to the time value measure.

NOTE   Not all data libraries have preconfigured content attributes in them. For example, the Calabrio ONE data model has them, but the WFM data model does not. If your data library has them, they appear in this drop-down list.

Example

We have created a simple report that shows the contact duration in seconds by team and then by agent. The measure Contact Duration (Seconds) is grouped by Team and then by Person.

The resulting report looks like this:

We want the Contact Duration value to be displayed in hh:mm:ss format rather than in seconds to be more understandable. To accomplish this, we apply the DURATION content attribute to the measure.

To do this, we open the Contact Duration (Seconds) measure dialog box (click the measure’s name in the Question panel) and add the DURATION content attribute in the lower panel.

  • In the Format field, enter hh:mm:ss
  • In the Content Attributes field, enter DURATION

The fields are highlighted in this graphic:

The report now shows the Contact Duration in hh:mm:ss format: