topic Need to format average time from decimal to hours:seconds in QlikView

Need to format average time from decimal to hours:seconds

Tue, 21 Jun 2011 15:13:11 GMT

Hi All,

I have a calucation below on a dashboard gauge that computes the average time it takes to complete an order. The calculation is done at the database level but returns the hours in franctions, for example 13.85 hours. The users would like QlikView to return the result in the time format HH:SS which would be 13:51 Hrs:Seconds. I've tried a number of attempts with interval, but I was not successful. Any help would be appreciated.

Thank you.

Don

=avg((ORDER_TO_COMPLETE_HRS)

Need to format average time from decimal to hours:seconds

kaushiknsolanki — Tue, 21 Jun 2011 15:27:00 GMT

Hi,

you can use date() function to achieve this.

for example

=date(40715.188020833,'h:mm:ss')

Hope this will help you.

Regards,

Kaushik Solanki

Need to format average time from decimal to hours:seconds

matt_crowther — Tue, 21 Jun 2011 15:31:41 GMT

Don,

This is a complete Pre-Sales / Data Monkey solution but it should work:

Split out the decimal part of the value ('.85') - this in essence is 85% so use this to calculate 85% of 60 which will then give you the minutes; rebuild the value in the script to deliver your '13:51' value. Functions to use would be right(), and '&'.

No reason why this wouldn't work on a line by line basis.

There may well be a neater solution but if Qlikview doesn't recognise the value as a date / time then using num() etc isn't going to work.

Any problems just post some sample data and I'll try and take a look.

Hope that helps,

Matt - Visual Analytics Ltd

Need to format average time from decimal to hours:seconds

Tue, 21 Jun 2011 15:39:19 GMT

Thank you Kaushik and Matt. Kaushik, the date function won't work because the order_to_complete is a number not a date. Matt I will give your solution a try. I wasn't sure that I was missing something obvious. I have to use the database function's return because it is based on company workdays and times so that part is not an easy calculation.

Re: Need to format average time from decimal to hours:seconds

kaushiknsolanki — Tue, 21 Jun 2011 16:52:22 GMT

Hi,

Have a look at the below example.

Hope this will solve your problem

Regards,

Kaushik Solanki

Need to format average time from decimal to hours:seconds

Tue, 21 Jun 2011 17:17:51 GMT

Kaushik, thank you very much for your solution. I will be able to add it to my load script and display the time correctly.