topic Re: Unable to find difference between two dates in chart in QlikView

Unable to find difference between two dates in chart

Wed, 02 Sep 2015 09:36:09 GMT

Hi,

I have two datetime fields in a chart, one is EXIT_DATETIME and the other is ENTRY_DATETIME. Both are formatted as YYYY-MM-DD hh:mm:ss, eg. 2015-08-25 17:03:00. I want to subtract one from the other but I am having trouble with the Interval function. If I create an expression (or calculated dimension, it doesn't matter which) in the chart that reads: =Interval(EXIT_DATETIME - ENTRY_DATETIME,'D hh:mm') , QV returns a '-' in that column indicating that it cannot calculate. However, if I create an expression that reads: =Interval('2015-08-25 08:09:55' - '2015-08-25 07:58:55','D hh:mm') , I get the correct answer of 0 00:11 in that field. Why does it work when I pass the function specific dates as strings, but fails when I pass it the column names of the date columns want to calculate the actual differences for?

Thanks for the help!

Re: Unable to find difference between two dates in chart

jonathandienst — Wed, 02 Sep 2015 09:40:13 GMT

QV implicitly converts literal strings to date/time values when dat/time values are expected, but it does not do so with fields. I expect your fields are loaded as strings. Either convert the values to date/times during the initial load (preferred) or explicitly convert them in the chart.

During LOAD (Best option):

LOAD ...

EXIT_DATETIME As sEXIT_DATETIME,

Timestamp#(EXIT_DATETIME, 'YYYY-MM-DD hh:mm:ss') As EXIT_DATETIME,

ENTRY_DATETIME As sENTRY_DATETIME,

Timestamp#(ENTRY_DATETIME , 'YYYY-MM-DD hh:mm:ss') As ENTRY_DATETIME,

Expression:

=Interval( Timestamp#(EXIT_DATETIME, 'YYYY-MM-DD hh:mm:ss') - Timestamp#(ENTRY_DATETIME , 'YYYY-MM-DD hh:mm:ss'), 'D hh:mm')

Re: Unable to find difference between two dates in chart

Wed, 02 Sep 2015 09:49:46 GMT

Thanks for the assistance, I already have in my load script the following line to convert ENTRY_DATETIME to an actual date, if that's one of the options you mean:

LOAD

....

Date#(MID(DATE,2,10) & ENTRY,'YYYY-MM-DD hh:mm:ss') AS ENTRY_DATETIME,

....

FROM

ETC....

I am using MID to leave out some characters at the end of the DATE field before concatenating it to an ENTRY field which has the time component. All my dates come out as YYYY-MM-DD hh:mm:ss with no leading or trailing spaces. This is also what my standard date format is set to in my load script. However this doesn't make a difference

If I instead convert to Date explicitly in my chart, it doesn't work either:

=Interval(Date#(EXIT_DATETIME,'YYYY-MM-DD hh:mm:ss') - Date#(ENTRY_DATETIME,'YYYY-MM-DD hh:mm:ss'),'D hh:mm')

Thanks

Re: Unable to find difference between two dates in chart

maxgro — Wed, 02 Sep 2015 20:37:47 GMT

maybe

- your datetime aren't datetime (check they are right aligned)

- in your chart you have more entry and/or exit datetime for dimension (try to add a rowno() to the load and use it in the chart)

Re: Unable to find difference between two dates in chart

Thu, 03 Sep 2015 06:22:38 GMT

Thanks, I found the problem, it wasn't either of these but I appreciate your help 🙂