Display "Number of Seconds" as "mm:ss" Using Column Mask

Becky Campelli-Oracle

Organization Name

Canon Canada

Description

This post requests the capability to display of "Number of Seconds" as "mm:ss," while preserving the numerical value. Canon Canada is unable to create key KPI dashboards like the one shown below, since there is currently no way to display a column containing "Number of Seconds" (e.g. 90) in a "mm:ss" format (90 seconds is equivalent to 1:30 = 1 minute, 30 seconds). Please note this request has nothing to do with a DateTime or Timestamp: the source column "Number of Seconds" is simply an integer type.

On March 21-25, 2022, Cloud Engineering hosted a Code Innovate with the customer, discovering this is an essential functionality for the customer to carry forward with OAC. (See Business Need)

Use Case and Business Need

Canon Canada's customer service KPIs include "Time to response," "Time on a service call," and other time-based measurements as currently skillfully summarized within an Excel document, as pictured below. In order for executives and customer service team leads to evaluate their employees' and department's success, these must be displayed in Excel tables as Minutes and Seconds ("mm:ss"), and be aggregated, categorized, and displayed within various charts.

This example is one of 3+ excel dashboards that require the mm:ss data display.

The method of visualization requested requires the preservation of the displayed "mm:ss" as a numerical value (rather than a string), for the following reasons:

• It needs to be AGGREGATED by time or category (this is barely possible using a Calculated string)
• It needs to be displayed as a VALUE within a Pivot Table (this is not possible with a string/Attribute type)
• It needs to be displayed as a VALUE LABEL on/above a Line and Bar Combo Chart

We tried creating a Calculated string (which I have proven out, just ask for the Calc), but is an insufficient "workaround" for the customer, using a visually confusing Bar Chart with value displayed on tooltip only, and a Tree Map instead of a Table. Additionally, the string calc can be aggregated, but only if the calculation exists within the Workbook (cannot be abstracted to Data Prep or Data Flow and still function properly)

Enhancement Request / Service Request

For the above reasons, it appears this functionality should be implemented as a "Display as" mask that simply displays the numerical value "Number of Seconds" as "mm:ss" (similar to the functionality currently is available for Display as Percent, and Display as Currency).

Over time, we may find that this functionality should be expanded to include HOURS ("hh:mm:ss"), DAYS (dd:hh:mm:ss), etc - but it is not currently requested for the above use case.