How to compare two time duration exactly in minutes

[#1]

Hi,

I have several records with StartDateTime and EndDateTime. I calculated the duration between EndDateTime and StartDateTime.

Key	StartDateTime	EndDateTime	Duration
1	2013/11/13 12:15:00	2013/11/13 12:16:00	0:01
2	2014/1/6 07:44:00	2014/1/6 08:46:00	1:02
3	2014/1/7 12:56:00	2014/1/7 12:57:00	0:01
4	2014/1/13 07:43:00	2014/1/13 08:44:00	1:01
5	2014/1/13 07:41:00	2014/1/13 08:42:00	1:01

I want to count the number of records that are less than or equal to 1 minute. The actual answer is 2, because there are two records with 0:01 minute.

I used the following expression:

=count( If( minute(EndDateTime - StartDateTime)=1,Key)

But QlikView "minute" counts all records that includes 1 minute. QlikView counts 0:01, 1:01. The above expression give 4.

How do I count only those records that are equal to "0:01" minute?

Thanks,

[Nicole-Smith]

=count({<Duration={'<=0:01'}>}Duration)

Example is attached.

[MarcoWedel]

=Count(If(EndDateTime-StartDateTime='00:01:00',Key)

hope this helps

regards

Marco

[#4]

Hi Nicole,

Thanks for your response. I downloaded your QVW. I changed the value in your expression to 1:01, the response was 4. It must be 2 becasue there are two records with duration = 1:01. Please try.

I then changed the value to '1:02' and the response was 5. It must be 1 as there are only one record for duration = 1:02.

[Nicole-Smith · Accepted solution]

I had it as less than or equal to because I thought that was what you wanted.  You can make it just equal to instead:

=count({<Duration={'0:01'}>}Duration)

Example QVW attached.

[#6]

Hi Nicole,

I applied that and it worked. I am not familiar with QlikView Syntax. I assumed <= sign was part fo syntax. Actually the logical operator is very confusing.

In this formula, =count({<Duration={'0:01'}>}Duration)

if you place "<" sign in front of '0.01', then it gives correct answer, but if you place '=' sign it does not work.

Thanks for the response

[#7]

Hi Marco,

Thanks for your repsonse. The formula needs one more right bracket. It did not work. But if you replace the "EndDateTime-StartDateTime" with "Duration", it works. These are some of the quirks of Qlikview. One must know exact syntax and convert the data into righ format.

Thanks,

Raghu