how to make this sla hour comparison

[rcandeo]

Hi, I have to compare the calculation in hours of my beginning and end date/hour of my calls agains my sla in hours.

I have a field called SLA that is an integer and it is in hours. eg: 7

I have the calculation that I had previously made and it is in hours too: eg: 08:34:25

I just need to comparise if the calculation is bigger than my SLA to show it in red()

It works if it occurs in the same day, but if in my calculation I have the end of the call in a day different than the beggining, qlik view is assuming that I have one day more than the SLA calculated, so it is showing me in red, even if the result is less than SLA.

The calculation formula is like this:

if(DayEndCall=DayOpenCall and MonthEndCall=MonthOpenCall and YearEndCall=YearOpenCall,DateEndCall-DateOpenCall,

  if(num#(HourOpenCall)<num#(vFinalHour),(vFinalHour - HourOpenCall),0) + if(num#(HourCloseCall)>num#(vInitialHour),(HourCloseCall- vInitialHour),0) + ((DayCloseCall - DayOpenCall)-1)))

assuming that vFinalHour = 18:00:00 and vInitialHour = 08:00:00

So If I have DayOpenCall = 09/05/2013 16:05:15 and DayCloseCall = 09/06/2013 10:36:46, it shows me 04:31:30 (correct) but if I compare if is bigger than my SLA (07:00:00), it assumes that it is and this is not what I want.

How could I have the right comparison please?

[Oleg_Troyansky · Accepted solution]

I think it would be easier if you operated in fractions of Days rather than in hours. This way, the Duration calculation doesn't need any IF conditions - simply Timestamp1 - Timestamp2.

The SLA time should also be converted to days: 7 * (1/24)

Then, everything becomes fairly simple...