Copare time with different format

[stekol61]

Hi!

I have a app where i want to calculate if 'Response time' is within 'Time_SLA' or not.

The problem I have seems to be that the date format is different. Example below:

Time_SLA =00:04:00   (dd:hh:mm)

Response time = 01:22:21  (hh:mm:ss)

[Jacek]

I would suggest convert all times to minutes and then compare. Below I created a code using subfield, should work correctly but maybe there is an easier way:

= if( 
// Time_SLA  (dd:hh:mm)
rangesum( Num(SubField(Time_SLA,':', 1) ) * 24 * 60, num( SubField(Time_SLA,':', 2)) * 60,  Num(SubField(Time_SLA,':', 3)) ) 
// Response time  (hh:mm:ss)
- rangesum( num( SubField(Response_time,':', 1)) * 60,  Num(SubField(Response_time,':', 2)) ) 

>= 0 , 'in time', 'longer'
)

[dplr-rn · Accepted solution]

as this is not a proper date i suggest you covert both those fields into minutes sla 240 mins, response time 82.35 mins
which will make it easier to compare.
so use subfield function to create this
e.g. 

Num#(Subfield(TIME_SLA,':',1)) * 24*60 // days
+ Num#(Subfield(TIME_SLA,':',2)) *60  //hrs
+ Num#(Subfield(TIME_SLA,':',3)) // mins
as TIME_SLA_IN_MINS  

[rwunderlich]

the Interval#() function will convert these time strings into a common number:

Interval([Response time], 'hh:mm:ss') <  Interval#(Time_SLA), 'dd:hh:mm')

Your Response Time field may already be in numeric format.  If you are unsure, put it in a listbox and set the number format to 4 decimals. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com