topic Re: Difference between Dates in QlikView

Difference between Dates

Anonymous — Wed, 07 Oct 2015 15:09:38 GMT

I am pulling in data from a Sharepoint 2010 List

Fields:

Issue Date(8/26/2015 8:00 AM)

Incident Resolution Date(8/26/2015 4:00 PM)

I need to calculate the difference between the Issue Date and Incident Resolution Date in HOURS to audit a <24 hours or >24 hours metric.

On the LOAD, I am formatting these fields as below:

Timestamp#([Issue Date], 'DD/MM/YYYY hh:mm:ss') as [Issue Date],
Timestamp#([Incident Resolution Time], 'DD/MM/YYYY hh:mm:ss') as [Incident Resolution Time],

The Expressions that I am trying are below and are returning these values:

Expression	Value Returned
=interval([Incident Resolution Time] - [Issue Date],'hh:mm')	Blank field
=sum([Incident Resolution Time] - [Issue Date])	0

If I export the Sharepoint List into an Excel spreadsheet and use this as my data source, the INTERVAL expression works perfectly and returns the value as hh:mm(43:00)

I checked the Sharepoint and the Issue Date and Incident Resolution Date fields are formatted as Date & Time

Dan

Re: Difference between Dates

swuehl — Wed, 07 Oct 2015 15:36:20 GMT

Have you checked that the timestamp#() parsing worked as expected by checking the field values?

I think your format code is not matching the input format, I would assume you need

Timestamp#([Issue Date], 'MM/DD/YYYY h:mm tt') as [Issue Date],
Timestamp#([Incident Resolution Time], 'MM/DD/YYYY h:mm tt') as [Incident Resolution Time],

Re: Difference between Dates

Anonymous — Wed, 07 Oct 2015 15:42:30 GMT

That was it! Thank You!!!1

Re: Difference between Dates

Anders_Eriksson — Wed, 07 Oct 2015 15:45:29 GMT

Since the values comes from a SQL Server database and are Date & Time values you should be able to simply subtract the numerical values.

If the difference is >= 1 then you are >= 24h, if difference is < 1 then you are < 24h appart.

If they are stored as text in the database first use the Date# to convert them to numerical values in QlikView and then do the subtraction as above.

Re: Difference between Dates

Anonymous — Wed, 07 Oct 2015 16:16:27 GMT

Swuehl,

Once I updated my script and the INTERVAL expression returned values, I noticed that the time calculation is off:

Issue Date: 09/28/2015 1:00 PM
Inc Resol Date: 9/30/2015 8:00 am

Time to Fix: 19:00 --> Should be 43:00

Seems the periods that span several days are not being calculated properly - spans under 24 hours.

I have updated the following to match in my script:

SET TimeFormat='h:mm tt';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY h:mm tt';

Timestamp#([Issue Date], 'MM/DD/YYYY h:mm tt') as [Issue Date],

Timestamp#([Incident Resolution Time], 'MM/DD/YYYY h:mm tt') as [Incident Resolution Time],

Dan

Re: Difference between Dates

maxgro — Wed, 07 Oct 2015 17:08:54 GMT

try to format your date as number, with some decimal and look at the result (see image)

Using your data it seems to work

Issue Date: 09/28/2015 1:00 PM

Inc Resol Date: 9/30/2015 8:00 am

Re: Difference between Dates

Anonymous — Wed, 07 Oct 2015 17:32:26 GMT

Maxgro,

Can you attach your QVW?

Re: Difference between Dates

maxgro — Wed, 07 Oct 2015 17:38:30 GMT

yes but there are just the expression you can see in the label in my prev post

Re: Difference between Dates

Anonymous — Wed, 07 Oct 2015 18:42:04 GMT

maxgro,

I got it to work using your expressions! Thank You!!

Dan

Re: Difference between Dates

MarcoWedel — Wed, 07 Oct 2015 21:36:38 GMT

please close your thread

thanks

regards

Marco