Problems calculating time differences

Tue, 05 May 2015 14:49:21 GMT

Hi all,

I'm hoping I might be able to find some help with this problem I've been having. I'm trying to calculate the difference between two dates (inc. timestamp), yet it seems no matter how I write the expression, the difference is not being calculated.

The two fields are imported as follows:

LOAD

date#([Date Occurred], 'DD/MM/YYYY hh:mm:ss') as [Incidents Call Creation DateTime],

FROM...

And

LOAD

date([Repair Call Creation Date]+[Repair Call Creation Time], 'DD/MM/YYYY hh:mm:ss') as [Repair Call Creation DateTime],

;

LOAD

date([Call Creation Date], 'DD/MM/YYYY') as [Repair Call Creation Date],
date([Call Creation Time], 'hh:mm:ss') as [Repair Call Creation Time],

FROM...

For the expression, I have tried variations on

[Repair Call Creation DateTime]-[Incidents Call Creation DateTime]

interval([Repair Call Creation DateTime]-[Incidents Call Creation DateTime], 'DD hh:mm')

date([Repair Call Creation DateTime]-[Incidents Call Creation DateTime], 'DD hh:mm')

alongside the interpreted versions of the above formatting functions (just in case) but so far no luck!

I'm wondering if there is something to do with the extract on this data that may be causing the date to be treated as a string rather than a number, however I understand that the date function should be storing the number as a duel regardless. Furthermore, I have experimented with many different variations in the data extract (eg, date(date#([Repair Call Creation DateTime], 'DD/MM/YYYY hh:mm:ss'), 'DD/MM/YYYY hh:mm:ss' and date([Repair Call Creation Date]&' '&[Repair Call Creation Time], 'DD/MM/YYYY hh:mm:ss') as [Repair Call Creation DateTime],) but I have still not had any success.

I'd be grateful to hear any ideas or suggestions. Thanks!

Re: Problems calculating time differences

Gysbert_Wassenaar — Tue, 05 May 2015 16:18:10 GMT

Can you post a qlikview document that demonstrates the problem?

Re: Problems calculating time differences

buzzy996 — Tue, 05 May 2015 16:30:40 GMT

try tis,

=Date#([Repair Call Creation DateTime],'DD/MM/YYYY')-Date#([Incidents Call Creation DateTime],'DD/MM/YYYY')

Re: Problems calculating time differences

Fri, 29 May 2015 11:08:28 GMT

Sorry for late reply to this -- it's been a busy month!

This worked, thanks very much!! I ended up with the following:

=interval(

date#([Repair Call Creation DateTime], 'DD/MM/YYYY hh:mm:ss')

date#([Incidents Call Creation DateTime], 'DD/MM/YYYY hh:mm:ss'))

topic Re: Problems calculating time differences in QlikView

Problems calculating time differences

Re: Problems calculating time differences

Re: Problems calculating time differences

Re: Problems calculating time differences