Need to format average time from decimal to hours:seconds

[#1]

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)

[kaushiknsolanki]

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

[matt_crowther]

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

[#4]

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. 

[kaushiknsolanki · Accepted solution]

Hi,

    Have a look at the below example.

    Hope this will solve your problem

Regards,

Kaushik Solanki

[#6]

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