topic Substracting time (it includes date and time in field) in QlikView

Substracting time (it includes date and time in field)

Mon, 24 Jun 2013 08:47:55 GMT

i need to perform the substraction inorder to know the time consumed. the time format includes both date and time in the field..

field value is in the format ''02.05.2013 08:03:43'' in excel

and other field value is ''02.05.2013 08:25:12''

the result must be below value minus upper value , which is : ''00:21:29''

Re: Substracting time (it includes date and time in field)

Mon, 24 Jun 2013 08:57:18 GMT

try this

=Interval(date#('02.05.2013 08:03:43','DD.MM.YYYY hh:mm:ss')-Date#('02.05.2013 08:25:12','DD.MM.YYYY hh:mm:ss'))

Re: Substracting time (it includes date and time in field)

Sokkorn — Mon, 24 Jun 2013 08:57:31 GMT

Hi,

Have you try this yet

=Interval(

Date(Date#('02-05-2013 08:25:12','dd-MM-yyyy hh:mm:ss'),'dd-MMM-yyyy hh:mm:ss')-Date(Date#('02-05-2013 08:03:43','dd-MM-yyyy hh:mm:ss'),'dd-MMM-yyyy hh:mm:ss')

,'hh:mm:ss')

Regards,

Sokkorn

Re: Substracting time (it includes date and time in field)

Mon, 24 Jun 2013 08:59:13 GMT

=Interval(Date#('02.05.2013 08:25:12','DD.MM.YYYY hh:mm:ss')-date#('02.05.2013 08:03:43','DD.MM.YYYY hh:mm:ss'),'hh:mm:ss')

Re: Substracting time (it includes date and time in field)

Tue, 25 Jun 2013 05:41:32 GMT

Hi qlikviewgeek

if your problem resolved then marked it as either correct or helpful according to your question , and close this thread,so that it might be helpful for other developer.

Regards

vishwaranjan

Re: Substracting time (it includes date and time in field)

Tue, 25 Jun 2013 07:11:40 GMT

probelm is not yet solved. Here date field is dynamic. how to do in that case. and i am using previous function to get the date of the previous date. for ex. interval(date#(pm_date)-date#(previous(pm_date)) as required hours. i need in this format, but unfortunately its not working.

Re: Substracting time (it includes date and time in field)

Tue, 25 Jun 2013 07:37:18 GMT

try this

Time:

LOAD Interval(timestamp#(pm_date,'DD.MM.YYYY hh:mm:ss')-timestamp#(Previous(pm_date),'DD.MM.YYYY hh:mm:ss'),'hh:mm:ss') as required_hours,

* INLINE [

pm_date

02.05.2013 08:03:43

02.05.2013 08:25:12

03.05.2013 09:07:43

03.05.2013 09:15:23

];

Re: Substracting time (it includes date and time in field)

Wed, 26 Jun 2013 14:51:05 GMT

Answer is : Interval(Fieldname-previous(fieldname),hh:mm:ss')