topic Number of Days between two Dates not working in QlikView

Number of Days between two Dates not working

prees959 — Fri, 16 Mar 2018 16:22:58 GMT

Hi,

I have the following in my load statement :

Timestamp#(Shift_Start_Date & ' ' & Start_Time,'YYYY-MM-DD hh:mm:ss.fff') -

date(Request_Date_Time,'dd/MM/yyyy hh:mm') as temp_days,

Both my source dates aren't the same and are being loaded from a CSV file so Im trying to force them into dates and then subtract 1 from the other.

Can anyone help?

Many thanks,

Phil

Re: Number of Days between two Dates not working

MK_QSL — Fri, 16 Mar 2018 16:26:44 GMT

What is the format of both date fields? Can you provide sample data for 4 to 5 lines?

Re: Number of Days between two Dates not working

Digvijay_Singh — Fri, 16 Mar 2018 16:30:32 GMT

Can you share your source date format? If you show these dates in list box, are they showing up right aligned?

Re: Number of Days between two Dates not working

prees959 — Fri, 16 Mar 2018 16:33:05 GMT

Hi,

Once both fields are converted, the are shown as :

Request Date : 2008-09-08 18:12:04

Start Date : 2008-10-01 16:00:00

Also, when displayed side by side in a text box, the Request Date is aligned to the right and the Start Date is aligned to the left. I'm guessing the field formats are different???

Re: Number of Days between two Dates not working

nagaiank — Fri, 16 Mar 2018 16:44:35 GMT

You may try

Floor([Request Date]) - Floor([Start Date])

Re: Number of Days between two Dates not working

MK_QSL — Fri, 16 Mar 2018 17:20:16 GMT

You can use any one from below depending on your requirements.

=TimeStamp#([Start Date],'YYYY-MM-DD hh:mm:ss') - TimeStamp#([Request Date],'YYYY-MM-DD hh:mm:ss')

=Floor(TimeStamp#([Start Date],'YYYY-MM-DD hh:mm:ss')) - Floor(TimeStamp#([Request Date],'YYYY-MM-DD hh:mm:ss'))

=Floor(TimeStamp#([Start Date],'YYYY-MM-DD hh:mm:ss')) - Floor(TimeStamp#([Request Date],'YYYY-MM-DD hh:mm:ss')) + 1