Difference between Dates

[Anonymous]

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

[swuehl]

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],

[Anonymous]

That was it!  Thank You!!!1

[anderseriksson]

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.

[Anonymous]

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

[maxgro]

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

[Anonymous]

Maxgro,

Can you attach your QVW?

[maxgro]

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

[Anonymous]

maxgro,

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

Dan

[MarcoWedel]

please close your thread

thanks

regards

Marco