topic Re: Calculating duration between to date time stamps in QlikView

Calculating duration between to date time stamps

Thu, 28 May 2015 12:34:48 GMT

Hi,

I have read just about all of the discussions on this issue, and am not able to resolve my issue.

I am extracting data from MySQL, for the SugarCRM system that we use. The date/time format looks like this:

how can I go ahead to calculate the exact time it took to close the query, if the 'Last modified' field that I use, saves it in the same format.

I have read the Datefield tech brief document trying to get answers, but none of those work. I have attached the qvw. I need the answer given to display as 'hh:mm:Ss'

Thanks

Re: Calculating duration between to date time stamps

buzzy996 — Thu, 28 May 2015 12:40:53 GMT

try this way,

=Interval(Interval(Sum(ProProductiveTime),'hh:mm:ss') - Interval(Sum(ProNonProductiveTime),'hh:mm:ss'),'hh:mm:ss')

Re: Calculating duration between to date time stamps

Thu, 28 May 2015 12:43:58 GMT

Thanks Shiva,

Do I load this in my load statement or expression tab?

Re: Calculating duration between to date time stamps

buzzy996 — Thu, 28 May 2015 12:46:40 GMT

on expression field,not on edit script.

Re: Calculating duration between to date time stamps

Thu, 28 May 2015 12:48:44 GMT

Thanks. works. if I want to now get max/min/avg? just add the to the front of statement?

Re: Calculating duration between to date time stamps

buzzy996 — Thu, 28 May 2015 12:51:57 GMT

yes,if not works u have little bit twist tht expression.

Re: Calculating duration between to date time stamps

Thu, 28 May 2015 13:00:19 GMT

is there no way that I can do this in the load script to create a new field i.e "Total time"?

Re: Calculating duration between to date time stamps

buzzy996 — Thu, 28 May 2015 13:11:22 GMT

definitely u can do,but i doubt that interval function will work or not though load stmt.

Re: Calculating duration between to date time stamps

Anonymous — Thu, 28 May 2015 13:22:53 GMT

The interval() function works perfectly in the load script. So give it a go and you'll see it works.