topic Re: Difference between dates + hr in QlikView

Difference between dates + hr

Thu, 29 Oct 2015 14:41:12 GMT

All,

I have two date fields where I need to return the average close time between the two in D H:M. My two date fields are DateCreated - '8/17/15 12:23:09' and CloseDate - 8/18/15 13:12:37' for example. Right now I am working with this equation:

=interval(avg(time#(DateCreated, 'd hh:mm') - time#(CloseDate, 'd hh:mm')), 'd hh:mm')

but am getting an error, can anyone help out?

Re: Difference between dates + hr

buzzy996 — Thu, 29 Oct 2015 14:46:29 GMT

try smhign like tis,

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

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

Re: Difference between dates + hr

buzzy996 — Thu, 29 Oct 2015 14:49:19 GMT

if tht not helps ,look at this.

Re: Peek / Previous Help!

Re: Difference between dates + hr

tamilarasu — Thu, 29 Oct 2015 14:50:59 GMT

I guess your date format seems wrong in your expression.. Try to change as time#(DateCreated,'m/dd/yy hh:mm:ss') also for closed date as time#(CloseDate,'m/dd/yg hh:mm:ss')..

Re: Difference between dates + hr

swuehl — Thu, 29 Oct 2015 14:52:55 GMT

Your timestamp interpreting needs to use the correct format codes: '8/17/15 12:23:09' is coded 'M/DD/YY hh:mm:ss'

=interval(avg(timestamp#(DateCreated, 'M/DD/YY hh:mm:ss') - timestamp#(CloseDate, 'M/DD/YY hh:mm:ss')), 'D hh:mm')

Re: Difference between dates + hr

Thu, 29 Oct 2015 14:56:52 GMT

Swuehl,

This seems to be the right formula, but it is only returning a static 6 19:24 across every dimension, so it is only taking one date range into place...

Re: Difference between dates + hr

Anonymous — Thu, 29 Oct 2015 15:02:24 GMT

Hi,

Refer to below post. Hope this will answer your question.

Exclude Holidays in Aging

Regards,

Imran K

Re: Difference between dates + hr

swuehl — Thu, 29 Oct 2015 15:05:36 GMT

Can't answer this without knowing what you are using as dimensions and how the fields are related in your data model (and possibly what the values of the fields are).