topic Re: Difference between two dates in QlikView

Difference between two dates

davyqliks — Thu, 07 Jul 2016 16:30:24 GMT

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

Re: Difference between two dates

sunny_talwar — Thu, 07 Jul 2016 17:17:51 GMT

Can you try this:

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

Re: Difference between two dates

sunny_talwar — Thu, 07 Jul 2016 17:18:20 GMT

Or just this:

Floor(step_date) - Floor(created_date) as Difference

Re: Difference between two dates

boorgura — Thu, 07 Jul 2016 20:41:39 GMT

Why not simply use the date#() function!

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

Re: Difference between two dates

Chanty4u — Fri, 08 Jul 2016 05:19:51 GMT

try

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

Re: Difference between two dates

krishna20 — Fri, 08 Jul 2016 05:31:31 GMT

Hi,

all above solutions are correct

else you try below one

SET DateFormat='DD/MM/YYYY';

Re: Difference between two dates

davyqliks — Fri, 08 Jul 2016 13:45:50 GMT

Thank you,

This returns a dash

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

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

Re: Difference between two dates

davyqliks — Fri, 08 Jul 2016 13:47:53 GMT

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

Re: Difference between two dates

davyqliks — Fri, 08 Jul 2016 13:52:42 GMT

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

Re: Difference between two dates

davyqliks — Fri, 08 Jul 2016 14:00:51 GMT

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

Re: Difference between two dates

boorgura — Fri, 08 Jul 2016 14:01:45 GMT

Is this not working?

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

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

Re: Difference between two dates

davyqliks — Fri, 08 Jul 2016 14:13:23 GMT

HI,

Thank you all for the replies. I'm sure they all worked but the actual problem stopping me from seeing the corrct result is in the load time stamp. This

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

Was missing the TT from the time format and so date i suppose was not recognised as date.

I have now used the Sunny solution.

Thank you again guys, your help helped me to get there on my own

Re: Difference between two dates

sunny_talwar — Fri, 08 Jul 2016 14:41:40 GMT

Awesome, I am glad you were finally able to figure it out.

Please close the thread down by marking correct and helpful responses if you think you have got what you needed.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: Difference between two dates

davyqliks — Mon, 11 Jul 2016 09:38:25 GMT

Of course, Thank you so much for your assistance Suny