topic Re: Difference between two dates in QlikView

Difference between two dates

Tue, 16 Apr 2013 23:57:00 GMT

Hi,

I having trouble in getting difference between two dates which is timestamp. I want the difference in "Days" and create a chart.

Example:-

Field 1 :- 4/11/2013 12:40:32

Field 2 :- 4/16/2013 12:00:00

The value should be "5 days"

Re: Difference between two dates

sushil353 — Wed, 17 Apr 2013 04:47:22 GMT

Try this:

date((date(Field1,'MM/DD/YYY')-date(Field2,'MM/DD/YYYY')),'DD')

HTH

Sushil

Re: Difference between two dates

Wed, 17 Apr 2013 05:48:39 GMT

HI ,

Try the below code

fabs(Date(Field1,'DD/MM/YYYY')) - fabs(Date(Field2,'DD/MM/YYYY'))

//Yusuf

Re: Difference between two dates

nirav_bhimani — Wed, 17 Apr 2013 05:49:10 GMT

Hi,

Try this in script

date(timestamp#(date2,'MM/DD/YYYY hh:mm:ss'),'DD/MM/YYYY') as Dt2,

date(timestamp#(date1,'MM/DD/YYYY hh:mm:ss'),'DD/MM/YYYY') as Dt1,

Interval( (date(timestamp#(date2,'MM/DD/YYYY hh:mm:ss'),'DD/MM/YYYY')) - (date(timestamp# (date1,'MM/DD/YYYY hh:mm:ss'),'DD/MM/YYYY')), 'd') as DTDiff

Also find the attachment.

Regards,

Nirav Bhimani

Re: Difference between two dates

sujeetsingh — Wed, 17 Apr 2013 05:59:45 GMT

Just use interval function as

Interval(Date1-Date2,'d')

Thanks

Re: Difference between two dates

jagan — Wed, 17 Apr 2013 06:03:27 GMT

Hi,

Try like this

=Ceil(Interval(Date#('4/16/2013 12:00:00', 'MM/DD/YYYY hh:mm:ss') - Date#('4/11/2013 12:40:32', 'MM/DD/YYYY hh:mm:ss'), 'DD' ))

Hope this helps you.

Regards,

Jagan.

Re: Difference between two dates

Anonymous — Wed, 17 Apr 2013 06:10:35 GMT

Hi Saaru,

i have some douts on you requrement you want to display days only or time also.

what is the result for your test data.

Regards

SHAIK

Re: Difference between two dates

Anonymous — Wed, 17 Apr 2013 06:12:10 GMT

Hi Saaru,

i have some douts on you requrement you want to display days only or time also.

what is the result for your test data.

Regards

SHAIK

Re: Difference between two dates

er_mohit — Wed, 17 Apr 2013 07:22:33 GMT

try this

Make sure that your format of date in both field must be same

Date(Date#(Field2,'MM/DD/YYYY hh:mm:ss')-Date#(Field1,'MM/DD/YYYY hh:mm:ss'),'DD') as RestDays

or you can use interval also

Interval(Field2 -Field1,'DD')as Newdays

Re: Difference between two dates

Wed, 17 Apr 2013 17:08:36 GMT

Thanks Jagan!!

It works when i apply your formula but when I replace with actual field then it is not working like below

=Ceil(Interval(Date#([Field2], 'MM/DD/YYYY hh:mm:ss')-Date#([Field1], 'MM/DD/YYYY hh:mm:ss') , 'DD'))

Am i missing something?

Re: Difference between two dates

jagan — Wed, 17 Apr 2013 17:14:44 GMT

Hi,

Check the date format is same in the field

[Field2] and Field1 is in this format 'MM/DD/YYYY hh:mm:ss', if not modify the format in the expression, hope this is the reason it is not working.

Regards,

Jagan.

Re: Difference between two dates

Wed, 17 Apr 2013 17:19:54 GMT

Ok. The date format is 'MM/DD/YYYY hh:mm:ss AM'. How can i modify the formula now?

Re: Difference between two dates

jagan — Thu, 18 Apr 2013 10:57:36 GMT

Hi,

Try like this

=Ceil(Interval(Date#([Field2], 'MM/DD/YYYY hh:mm:ss TTT') - Date#([Field1], 'MM/DD/YYYY hh:mm:ss TTT'), 'DD' ))

Regards,

Jagan.

Re: Difference between two dates

Mon, 19 May 2014 11:19:48 GMT

Hi Nirav Bhimani,

I used this in script side but if my date is 01/16/2013 42:00:00 then it will round up that date into 01/17/2013 18:00:00

so i don' want to that so what i can do for that

Re: Difference between two dates

nirav_bhimani — Mon, 19 May 2014 12:49:41 GMT

Hi,

I think you need to used the Floor Function.

Regards,

Nirav Bhimani

Re: Difference between two dates

sujeetsingh — Mon, 28 Jul 2014 04:55:26 GMT

Where is Sharu...

Do he is still facing some issue or he forgot the thread.

It should be marked as answered.

Re: Difference between two dates

abhaytrip — Fri, 17 Nov 2017 10:19:09 GMT

Is is possible to have it in this format ?

All I want is to find out the diff between the two(The first col - The second col) and plot them on a graph.

Re: Difference between two dates

VNPython — Tue, 04 Apr 2023 10:06:09 GMT

dear all, after trying all possible suggestions for this date difference yet still unable to get the correct 'days'. Example to find the difference between Invoice Date (29.03.23) and Order Date (2.03.23). the date data are in text format. The closest I get is 54 😅 Shalom and regards to all seniors