How to compare Time duration exactly

[#1]

Hi,

I have several records with StartDateTime and EndDateTime. I computed

the duration between EndDateTime and StartDateTime.

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

I want to count the number of records that are equal to 1 minute. The actual answer is 2, because there are two records - Key 1 and Key 3, with duration = 0:01 minute

I used the following expression:

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

I get count as 4. QlikView "minute" counts all records that includes duartion with 1 minute. QlikView counts 0:01 minute and 1:01. The above expression gives count as 4. It includes Key 1 Key 3, Key 4, and Key 5. The duration for Key 4 and Key 5 are 1:01. I do not want to count 1:01.

How do I count only those records that are equal to "0:01" minute? I had posted this question in one my previous discussion, but I had provided the precalcualted "Duration" column which included the exact minute. The solution count({<Duration={'<=0:01'}>}Duration) works on precaluated duration, but does not work on the expression EndDateTime - StartDateTime.

Thanks,

[swuehl]

On your previous thread, wasn't there also a solution using count(if())?

Something like

count( if( (EndDateTime - StartDateTime) <= interval#('1','m'), Key))

[puttemans]

The 'minute' command of your syntax will only keep the minutes part of your equasion result. Therefor you get more than what you want. I'd remove it, have a look at what exactly is the result of 1 minute in your equasion, and replace the '1' with that exact result.

Regards,

johan

[#4]

I appreciate your response. applied your expression and it did not work. I get 0. In the previous discussion, there was a expression

Count(If((EndDateTime - StartDateTime)='0:01',Key)), but it also gives 0. It did not work

[#5]

I tried replacing the 1 minute value exact number value, but it did not work. In reality, I will not have the exact value, beacuse I am computing the time difference between two TimeStamps.

QlikView needs accurate syntax and expression and is really bad in computing duration.

[swuehl · Accepted solution]

Multiple possible reasons, it would best if you upload a small sample QVW.

1) StartDateTime and EndDateTime must have numeric representation (labelled in QV as timestamp fields), to be able to subtract them

2)  Datamodel / context. Are all three fields located in one single table?

Using a slightly modified expression to avoid floating point issues, I do get a count of 2:

=count( if( num#(interval( EndDateTime - StartDateTime,'m')) <= 1, Key))

With data loaded like

SET TimestampFormat = 'YYYY/MM/DD hh:mm:ss';


INTERVALS:
LOAD * INLINE [
Key, StartDateTime, EndDateTime
1, 2013/11/13 12:15:00, 2013/11/13 12:16:00
2, 2014/1/6 07:44:00, 2014/1/6 08:46:00
3, 2014/1/7 12:56:00, 2014/1/7 12:57:00
4, 2014/1/13 07:43:00, 2014/1/13 08:44:00
5, 2014/1/13 07:41:00, 2014/1/13 08:42:00
];

[Kushal_Chawda]

try,

=Count(If( interval(EndDateTime - StartDateTime,'mm')<=interval#('01','mm'),Key))

[#8]

Hi Swuehl,

I corrected the Set Timestampformat and used your formula and it worked. I also uploaded the Qlikview file for reference. I have included other expressions that still does not work. Maybe it is the syntax problem.

Thanks,

Raghu

[sasiparupudi1]

count( if( interval((EndDateTime - StartDateTime),'m') <= 1, Key))