Time subtraction problem

[#1]

Hi All,

I am calculating a time difference column which is the difference of (SLA_time) - (END_time). However, in conditions where the END_time is more than the SLA_time, Qlikview calculates this by subtracting with 12 o clock.

I want to represent it with  a -ve sign in the front. simply.

For eg:

10:00:00 AM - 10:12:10 AM = -00:12:10

And incase of SLA_time > END_time,  10:12:10 AM - 10:00:00 AM = 00:12:10

How can i do this? I tried using < and > signs, and QV doesnt give me correct results. I think it is a problem with the less than and greater than signs.

[rwunderlich · Accepted solution]

You should be able to just subtract SLA_Time from END_Time, but I see that your SLA_Time uses a different date than the END_Time timestamps. I'm guessing you want to assume that SLA and END are on the same date.

In your example you are using the time() function to extract the time portion for the calculation. That won't work. Time() is a formatting function. It only affects display values, not the internal value used in the calculation.

You can extract the time portion for arithmetic using frac(). So try:

frac(SLA_TIME) - frac(END_TIME)

and format as an interval in the Number pane.

-Rob

http://robwunderlich.com

[#3]

That worked great!! I am only trying to subtract time and not the date since this is something which occurs everyday and i just need to see when it finishes!

Thanks once again!