Results Section: Convert Decimal to Time Format

This will cover converting decimal format to time format in the result section of a Hyperion document. These results can then be used in both the report and pivot sections. There are several options which we will cover.

While decimal format is preferred for end-users who want to export a section to Excel and ‘play’ with the data, the ability to convert decimal to time format is an essential ingredient in producing quality reports.

Time format (I.e. HH:MM:SS) is often requested by end users for ease of interpretation (quickly – how long is 4.61 minutes?). Additionally, it is often viewed as a more professional method of presentation in terms of appearance. This is particularly true in ‘read-only’ reports such as those exported to PDF or HTML, as well as enterprise level reports such as KPIs.

For our tutorial we will use a Symposium based example. Please note that the tutorial can be applied to any database you happen to be querying with Hyperion. The only caveat is that the formulas used below are based on results that are in seconds (as are Symposium Sybase tables). So, if your results appear in some other increment, you can either convert your results to seconds and use the formulas below or simply adjust the formulae to whatever increment you happen to be using.

We will simply be creating a Calculated Item in the result section. For our first example, we will name this calculated column “Agent ACW”.

The simplest method to implement this is when the value you wish to convert a single value. In this case we are looking at total Post Call Processing Time (postcallprocessingtime) from Symposium per Agent.

Our sample uses the dagentbyskillsetstat table from Symposium. Again, this will work equally well with any database you are querying, provided the increments are in seconds or you convert them to seconds prior to executing the calculated item we will be creating with our formulae. For minutes, etc.. adjust accordingly.

Lets begin with the formula itself.

As you can see below, we are simply using the value of Postcallprocessingtime for every break in the Agents name (break value). Please note that we have concatenated the agent’s first and last name into a field “Agent”.

Simply right click on the result section, chose ‘Calculated Item’, give it the name “Agent ACW” (or whatever you want to call it) and paste in the formula below.

Decode ( Length (Floor ( Floor ( (Sum( Postcallprocessingtime, Agent )) / 60) / 60) ) ,1, “0″ + Floor ( Floor ((Sum( Postcallprocessingtime, Agent ))/ 60) / 60), Floor ( Floor ( (Sum( Postcallprocessingtime, Agent )) / 60) / 60)) + “:” + Decode ( Length (Mod ( Floor ((Sum( Postcallprocessingtime, Agent ))/ 60), 60)), 1, “0″ + Mod ( Floor ((Sum( Postcallprocessingtime, Agent ))/ 60), 60), Mod ( Floor ((Sum( Postcallprocessingtime, Agent )) / 60), 60)) + “:” + Decode ( Length ( Mod ( (Sum( Postcallprocessingtime, Agent )), 60 )), 1, “0″ + Mod ( (Sum( Postcallprocessingtime, Agent )), 60 ), Mod ( (Sum( Postcallprocessingtime, Agent )), 60 ))

Despite it’s length and the number of nesting levels, all that is really required to employ this formula is to substitute whatever break value you like in place of (Sum( Postcallprocessingtime, Agent ).

We need to use break values for this we want the result column to return a single value for each agent.

This also highlights a limitation of this method. While the conversion makes for easier interpretation by the report recipient, the calculated column can not be used for subsequent calculations, for those, you will need to refer back to the original decimal fields (I.e. Postcallprocessingtime).


Let’s say we need a complex metric, such as Average Handle Time (AHT). Defining AHT as (Talktime + Postcallprocessingtime)/ ( Calllsanswered ) .

Our first option would be to begin with a calculated item of the break totals such as: ( Sum ( Talktime, Agent ) + Sum ( Postcallprocessingtime, Agent ) ) / Sum ( Calllsanswered, Agent ). We could name this calculated item “AHTCALC” and then simply replace Sum( Postcallprocessingtime, Agent ) in the top formulation with Sum( AHTCALC, Agent ). This would then give us

Decode ( Length (Floor ( Floor ( (Sum( AHTCALC, Agent )) / 60) / 60) ) ,1, “0″ + Floor ( Floor ((Sum( AHTCALC, Agent ))/ 60) / 60), Floor ( Floor ( (Sum( AHTCALC, Agent )) / 60) / 60)) + “:” + Decode ( Length (Mod ( Floor ((Sum( AHTCALC, Agent ))/ 60), 60)), 1, “0″ + Mod ( Floor ((Sum( AHTCALC, Agent ))/ 60), 60), Mod ( Floor ((Sum( AHTCALC, Agent )) / 60), 60)) + “:” + Decode ( Length ( Mod ( (Sum( AHTCALC, Agent )), 60 )), 1, “0″ + Mod ( (Sum( AHTCALC, Agent )), 60 ), Mod ( (Sum( AHTCALC, Agent )), 60 ))

You may find this method easier to track and manage. On larger result sets, it will also process more quickly. However, we could maintain a single calculated item as well by replacing (Sum( AHTCALC, Agent ) with the our formula:( Sum ( Talktime, Agent ) + Sum ( Postcallprocessingtime, Agent ) ) / Sum ( Calllsanswered, Agent ), giving us:

Decode ( Length (Floor ( Floor ( (Sum ( Talktime, Agent ) + Sum(Postcallprocessingtime ,Agent) ) / Sum(Callsanswered, Agent ) / 60) / 60) ) ,1, “0″ + Floor ( Floor ( (Sum ( Talktime, Agent ) + Sum(Postcallprocessingtime ,Agent) ) / Sum(Callsanswered, Agent ) / 60) / 60), Floor ( Floor ( (Sum ( Talktime, Agent ) + Sum(Postcallprocessingtime ,Agent) ) / Sum(Callsanswered, Agent ) / 60) / 60)) + “:” + Decode ( Length (Mod ( Floor ((Sum ( Talktime, Agent ) + Sum(Postcallprocessingtime ,Agent) ) / Sum(Callsanswered, Agent ) / 60), 60)), 1, “0″ + Mod ( Floor ((Sum ( Talktime, Agent ) + Sum(Postcallprocessingtime ,Agent) ) / Sum(Callsanswered, Agent ) / 60), 60), Mod ( Floor ((Sum ( Talktime, Agent ) + Sum(Postcallprocessingtime ,Agent) ) / Sum(Callsanswered, Agent ) / 60), 60)) + “:” + Decode ( Length ( Mod ( (Sum ( Talktime, Agent ) + Sum(Postcallprocessingtime ,Agent) ) / Sum(Callsanswered, Agent ) , 60 )), 1, “0″ + Mod ( (Sum ( Talktime, Agent ) + Sum(Postcallprocessingtime ,Agent) ) / Sum(Callsanswered, Agent ) , 60 ), Mod ( (Sum ( Talktime, Agent ) + Sum(Postcallprocessingtime ,Agent) ) / Sum(Callsanswered, Agent ) , 60 ))

This obviously gives us a cleaner, single computed item.

Finally, we are ready to add the item top our report or pivot section. In doing so, we should select Data Function>Maximum or Data Function>Minimum. We do as all values for each column break will be identical. Using Average or Sum will cause an error as the report or pivot can not sum or average this computed item.

It is important to map out how you want your results to be presented, as this computation is contingent upon break totals, so you must pull your results in such a way, or organize them in such a way, that will allow you to have the required data in such a format that column break totals can be effectively and accurately added.

If the above section were using an interval table for example, such as Symposium’s iagentbyskillsetstat, you would need to arrive at a method of concatenating the agent and interval break totals. This is most simply done by creating a field of the type (Agent+Time) that would provide the needed column in order to implement the above.