topic Re: Difference between two dates in QlikView

Difference between two dates

abhaytrip — Fri, 17 Nov 2017 08:42:01 GMT

I WANT TO SHOW THE DIFFERENCE BETWEEN THE TWO DATES (The FIRST Col - The SECOND Col) and plot their values on a bar chart.
How can that be done ?
Thanks

Re: Difference between two dates

Anil_Babu_Samineni — Fri, 17 Nov 2017 09:41:34 GMT

Try this?

TimeStamp(Field1, 'MMM D YYYY hh:mmTT') - TimeStamp(Field2, 'MMM D YYYY hh:mmTT')

Re: Difference between two dates

abhaytrip — Fri, 17 Nov 2017 10:10:33 GMT

Hey ,

I tried this but it isn't working at all. shows no values.

Re: Difference between two dates

its_anandrjs — Fri, 17 Nov 2017 11:39:50 GMT

Did you try Interval function.

Re: Difference between two dates

abhaytrip — Fri, 17 Nov 2017 11:43:02 GMT

Yes I did , But I couldn't fetch any results.

Re: Difference between two dates

Anonymous — Fri, 17 Nov 2017 11:55:11 GMT

interval(Col1 - Col2, 'DD hh:mm ')

Re: Difference between two dates

abhaytrip — Fri, 17 Nov 2017 12:04:32 GMT

Tried that as well , 😕

Re: Difference between two dates

Peter_Cammaert — Fri, 17 Nov 2017 16:48:56 GMT

If those are names of fields with multiple values, better create a straight table with a separate row for each value combination for real_survey_date_sent and date_survey_received, and add an expression with one of the suggestions posted before.

Specifying a field name in a text box will return all values at once. And subtracting all values from all values will lead to a NULL result.

Re: Difference between two dates

abhaytrip — Mon, 20 Nov 2017 05:42:34 GMT

Tried this , but No values are shown.

Re: Difference between two dates

Anil_Babu_Samineni — Mon, 20 Nov 2017 05:51:31 GMT

Can you share image simply using

Date1 - Date2 ??

Re: Difference between two dates

abhaytrip — Mon, 20 Nov 2017 05:54:33 GMT

here you go !

Re: Difference between two dates

Anil_Babu_Samineni — Mon, 20 Nov 2017 05:59:31 GMT

Try this?

Interval( (date(timestamp#(Field1,'MMM D YYYY hh:mmTT'),'DD/MM/YYYY')) - (date(timestamp#(Field2,'MMM D YYYY hh:mmTT'),'DD/MM/YYYY')), 'd')

Re: Difference between two dates

abhaytrip — Mon, 20 Nov 2017 06:16:09 GMT

I'm getting the values in Integer.

After Formatting , I get this.

But the diff in days isn't seen here 😕

Re: Difference between two dates

Anil_Babu_Samineni — Mon, 20 Nov 2017 06:18:52 GMT

Try to change format only DD not DD:MM:YYYY

Another way

Date((date(timestamp#(Field1,'MMM D YYYY hh:mmTT'),'DD/MM/YYYY')) - (date(timestamp#(Field2,'MMM D YYYY hh:mmTT'),'DD/MM/YYYY')), 'DD')

Re: Difference between two dates

abhaytrip — Mon, 20 Nov 2017 06:22:43 GMT

Got this using DD hh:mmTT ,Thanks Folks !

Re: Difference between two dates

Anil_Babu_Samineni — Mon, 20 Nov 2017 06:27:23 GMT

Great Qlik Community Tip: Marking Replies as Correct or Helpful