topic Re: Difference between Date and Time in QlikView

Difference between Date and Time

qlikview979 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I have data like below format

Closed Date,Open Date

10/10/2017 12:15:00 P.M.,10/10/2017 12:05:00 P.M.

27/01/2018 1:00:00 P.M., 25/10/2018 11:00:00 A.M.

I have to calculate difference in between Closed Open-Open Date in Minutes.

Expected Output:-

1) 10(Minutes)

2) 30000(Minutes) (25,26,27 dates calculation hours 50*60=3000)

omarbensalem‌bpn‌gwassenaar‌mbaeyens‌

Regards

Mahesh

Re: Difference between Date and Time

qlikview979 — Fri, 26 Jan 2018 19:05:32 GMT

25th date:-13 hours

26th date:-24 hours

27th date:-13 hours

Total :-50 hours (50*60=3000 min)

stalwar1‌

Re: Difference between Date and Time

m_woolf — Fri, 26 Jan 2018 19:15:36 GMT

I had some problem with the periods in AM and PM. Once I stripped them out, this worked:

=num((num([Closed Date]) - num([Open Date]))*24*60,'#,##0')

Re: Difference between Date and Time

qlikview979 — Fri, 26 Jan 2018 19:28:44 GMT

can you please change the time format from 12 hours to 24 hours remove the A.M&P.M by using Purgechar.

I tried same thing but i am getting correct output.

-Mahesh

Re: Difference between Date and Time

qlikview979 — Fri, 26 Jan 2018 19:35:35 GMT

sorry,

I am not getting correct output.

Can any one help me on this?

-mahesh

Re: Difference between Date and Time

m_woolf — Fri, 26 Jan 2018 19:36:56 GMT

Can you post a sample qvw showing the problem?

Re: Difference between Date and Time

qlikview979 — Fri, 26 Jan 2018 19:41:47 GMT

Hi Bro,

please find the attached link.

Time Difference

Re: Difference between Date and Time

qlikview979 — Fri, 26 Jan 2018 19:55:40 GMT

Hi Bro,

I tried with your logic it not giving result.

Mahesh

Re: Difference between Date and Time

pradosh_thakur — Fri, 26 Jan 2018 20:05:12 GMT

script:

load timestamp(timestamp#(PurgeChar([Closed Date],' .'),'DD/MM/YYYYhh:mm:ssTT')) AS [Closed Date] ,
timestamp(timestamp#(PurgeChar([Open Date],' .'),'DD/MM/YYYYhh:mm:ssTT')) AS [Open Date] ;
load * inline [
Closed Date,Open Date

10/10/2017 12:15:00 P.M.,10/10/2017 12:05:00 P.M.

27/01/2018 1:00:00 P.M., 25/01/2018 11:00:00 A.M.
]

straight table

dimension

[Open Date]

[Closed Date]

expression

Interval([Closed Date]-[Open Date],'mm')

Re: Difference between Date and Time

m_woolf — Fri, 26 Jan 2018 20:07:08 GMT

See the attachment (uses your data):

Re: Difference between Date and Time

pradosh_thakur — Fri, 26 Jan 2018 20:07:18 GMT

In your sample data you have used the date 25/10/2018 but in sample output used the date as 25/01/2018 . I have used the later to get the calculation. Check my answer below.

Re: Difference between Date and Time

qlikview979 — Fri, 26 Jan 2018 20:12:06 GMT

Hi Bro,

thanks for you help.

I tried with my source file its showing plank.

please try with my data.

Re: Difference between Date and Time

pradosh_thakur — Fri, 26 Jan 2018 20:24:55 GMT

load the files simply and

Use this as expression

Interval([ClosedTime]-[OpenTime],'mm')

Re: Difference between Date and Time

qlikview979 — Fri, 26 Jan 2018 21:04:21 GMT

Hi Bro's

For your reference i am attaching snapshot.

In the above snapshot i have selected close and open dates and the difference between two dates is 91 days

i have to calculate open time from 1:22:51 to End of the day(11:59:00) and Closed time till(9:52:48).

no need to calculate 24 in open and closed days

for example:-

91*24=it will give 2184(hours)

here i want exclude 13 hours 21 min from open time(we need to consider from 1:22:51 P.M.) and we need to add 9 hours 52 min from closed time

final result is

2184*60=131040-800(excluding 800 min from Open time )and including 592 min from Closed time.

=131040+592=131632(minutes)

Regards

Mahesh