Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
davyqliks
New Contributor II

Difference between two dates

Hi,

I am struggling in my current doc (import from excel) to fin the difference between two dates. i have this working fine in 1 doc

date((date("step_date",'MM/DD/YYY')-date(create_date,'MM/DD/YYYY')),'DD')*1

However, when i transpose this to another doc and change fields it returns null -

here is the transposed code:

date((date(Date_Imported,'MM/DD/YYY')-date([Date Ordered],'MM/DD/YYYY')),'DD')*1

The format of the date for both fields being imported from excel is like so   7/6/2016 12:00:00 AM

I am really struggling to see the difference and why this is not working:

here is the load SET

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

and the sheet it is being loaded from

LOAD TEST_Order_No as [Order Reference], 

     Supplier_Code,

     //[Suplier Name] as [Supplier Name],

     Web_Order_No,

  Date_Imported,

     Date_Order as [Date Ordered],

     order_status,

     Tracking_Status,

     locationCode as [TEST Location],

     [Item No.] as [TEST ITem Ref],

     [Item Type]

FROM

K:\....................

Thank you so much, i really hope you can help with this.....So annoying as i had it working before.

Rgds

Daniel

1 Solution

Accepted Solutions

Re: Difference between two dates

Can you try this:

Interval([step_date] - [create_date],'D') as Difference

13 Replies

Re: Difference between two dates

Can you try this:

Interval([step_date] - [create_date],'D') as Difference

Re: Difference between two dates

Or just this:

Floor(step_date) - Floor(created_date) as Difference

boorgura
Valued Contributor

Re: Difference between two dates

Why not simply use the date#() function!

date#(Date_Imported,'MM/DD/YYYY')-date#([Date Ordered],'MM/DD/YYYY')

Chanty4u
Esteemed Contributor III

Re: Difference between two dates

try

date((date#(Date_Imported,'MM/DD/YYY')-date(Date#([Date Ordered],'MM/DD/YYYY')),'DD'))*1

krishna20
Valued Contributor

Re: Difference between two dates

Hi,

all above solutions are correct

else you try below one

SET DateFormat='DD/MM/YYYY';

davyqliks
New Contributor II

Re: Difference between two dates

Thank you,

This returns a dash

Floor(Date_Imported) - Floor([Date Ordered])

Thank you for the reply Sunny, just trying the other option above

davyqliks
New Contributor II

Re: Difference between two dates

THank you,

I have just tried this and it returns a dash

Interval(Date_Imported - [Date Ordered],'D')

Thank you for the reply..... Any idea what i am doing wrong here?

Thank you

Daniel

davyqliks
New Contributor II

Re: Difference between two dates

Thank you so much for the reply but this returns error in expression :

date((date#(Date_Imported,'MM/DD/YYY')- date(Date#([Date Ordered],'MM/DD/YYYY')),'DD'))*1

')' expected

Rgds

Daniel

davyqliks
New Contributor II

Re: Difference between two dates

Hi,

Thank you for the reply. Please can you explain what this is achieving

SET DateFormat='DD/MM/YYYY';


I have added to the load script

Thank you

Daniel

Community Browser