topic Re: Convert/Format Decimal to Appropriate Time in QlikView

Convert/Format Decimal to Appropriate Time

MalcolmCICWF — Thu, 26 Jan 2017 14:35:25 GMT

So I have found pieces of this discussion, but have not received a solution trying to put them all together.

I have data where the field is represented in minutes and then divided by another field to get an Average Handle Time of a call. I get results where the average is .73 minutes, 10.6 minutes, and 85 minutes as examples. I need these minutes broken out into proper Hours, Minutes, and Seconds.

So far I have tried some Time formatting and taking the results *60, but cannot solve formatting the final results to hh:mm:ss successfully. What am I doing wrong? I am assuming it's easiest to do this right away in the script?

Re: Convert/Format Decimal to Appropriate Time

hector_munoz — Thu, 26 Jan 2017 14:39:20 GMT

Hi Jeremy,

Try the following formula: Timestamp(NUMBER, 'hh:mm:ss').

Regards,
H

Re: Convert/Format Decimal to Appropriate Time

swuehl — Thu, 26 Jan 2017 14:40:41 GMT

Maybe

=Interval( Sum(Time) / Count(Calls) / ( 60 *24), 'hh:mm:ss')

Re: Convert/Format Decimal to Appropriate Time

sunny_talwar — Thu, 26 Jan 2017 14:41:51 GMT

May be this?

Table:

LOAD *,

Interval(KeepChar(Time, '0123456789.')/1440, 'h:mm:ss') as NewTime;

LOAD * Inline [

Time

0.73 minutes

10.6 minutes

85 minutes

];

Re: Convert/Format Decimal to Appropriate Time

MalcolmCICWF — Thu, 26 Jan 2017 17:22:58 GMT

Ok, so I would love to try some of these suggestions, thank all of you, but I am having an issue.

Can anyone tell me why it is not displaying all of my data in a chart, but it will in a table box? I have never had this issue. There is data under each CALL_CENTER and each SKILL, but the data will not display in my chart... I am working with 12k accounts, and I swear everything was displaying at one point, I even went as far as making the report over from scratch in case I altered the settings.

Re: Convert/Format Decimal to Appropriate Time

sunny_talwar — Thu, 26 Jan 2017 19:03:13 GMT

Since you have not used any aggregation (Sum or Avg or Min or Max or something else), your chart doesn't know what to show for places that have multiple values and ends up showing null. Using Sum, shows results

Re: Convert/Format Decimal to Appropriate Time

MalcolmCICWF — Thu, 26 Jan 2017 20:05:46 GMT

I thought there was a way to set it to just display the value instead of using an Aggregation function, but neither the thread nor the place for that discussion

Re: Convert/Format Decimal to Appropriate Time

sunny_talwar — Thu, 26 Jan 2017 20:10:26 GMT

So are your concerns resolved then?

Re: Convert/Format Decimal to Appropriate Time

swuehl — Thu, 26 Jan 2017 20:13:33 GMT

You can use a straight table like a table box by using all fields you want to show as dimensions and disable 'suppress zero values' on presentation tab.

Re: Convert/Format Decimal to Appropriate Time

MarcoWedel — Thu, 26 Jan 2017 21:44:57 GMT

Hi,

maybe you mean to convert your Average_Handle_Time_MIN field in the script already?

Then maybe one solution might be also:

SER_CAMPAIGN:

LOAD

...

IN_CALL+WRAPUP/SUCCESS_OPER_TRANSFERS as Average_Handle_Time_MIN,

Interval((IN_CALL+WRAPUP/SUCCESS_OPER_TRANSFERS)*'00:01') as Average_Handle_Time,

...

Resident SER_Opt;

hope this helps

regards

Marco

Re: Convert/Format Decimal to Appropriate Time

MalcolmCICWF — Fri, 27 Jan 2017 14:43:47 GMT

This worked really well for me, thanks. I did remove the keepchar function though, I'm not sure how it would help me.

Re: Convert/Format Decimal to Appropriate Time

sunny_talwar — Fri, 27 Jan 2017 14:59:22 GMT

I didn't know if your data included minutes or not (0.73 vs 0.73 minutes)