topic Another Date Calculation Question in QlikView

Another Date Calculation Question

Wed, 18 Apr 2012 13:46:35 GMT

I am new to QlikView and am using QlikView for Windows version 11. I am having difficulting subtracting 1 date/time from a second date/time. I am using a CSV file as my datasource. Based on my abilty to concatenate the date/times, I believe the date/times are recognized as strings by Qlikview.

I have tried the timestamp#, date#, date functions but always get '-' for my answer. I have perused the QlikView Reference Manual and the QlikView Time and Date primer documents found during my google searches. I have tried converting the string into an actual date/time but have failed miserably.

Examples of my date/time data are: 3/20/2012 11:15 and 3/16/2012 8:47 (I have also uploaded my source file)

I have used the following formats to no avail:

(DATE([Logon time],'M/D/YYYY h:mm')) as LogonTimeDate

(DATE([Logon time],'M/D/YYYY hh:mm')) as LogonTimeDate

(DATE([Logon time],'M/D/YYYY hh:mm:ss')) as LogonTimeDate

(DATE([Logon time],'M/D/YYYY h:mm:ss')) as LogonTimeDate

(DATE([Logon time],'M-D-YYYY h:mm')) as LogonTimeDate

(DATE([Logon time],'M-D-YYYY hh:mm')) as LogonTimeDate

(DATE([Logon time],'M-D-YYYY hh:mm:ss')) as LogonTimeDate

(DATE([Logon time],'M-D-YYYY h:mm:ss')) as LogonTimeDate

Any guidance would be mucho appreciated!

Thanks,

John

Another Date Calculation Question

chematos — Wed, 18 Apr 2012 15:37:01 GMT

Hi John,

try this and let me now what is the result

DATE(floor([Logon time]),'M/D/YYYY h:mm') as LogonTimeDate

Regards,

Chema

Another Date Calculation Question

Wed, 18 Apr 2012 15:49:07 GMT

Hi Jose,

I had not tried the floor function. Unfortunately, I still get the same result the dash.

Thanks,

John

Another Date Calculation Question

jagan — Wed, 18 Apr 2012 15:57:48 GMT

Hi John,

Try this

=Date(Date#('3/20/2012 11:15', 'M/DD/YYYY HH:mm'), 'MM/DD/YYYY HH:mm:ss')

Hope this helps you.

Regards,

Jagan.

Another Date Calculation Question

Wed, 18 Apr 2012 17:57:04 GMT

Thanks Jagan, however it did not work for me. I have given up and converted by source file to an XLS and the timestamp# function works for me. I think the issue with my CSV was that is did not have the AM/PM included. This is simply a guess...

Many thanks for the help!