Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Time subtraction problem

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.

1 Solution

Accepted Solutions

Re: Time subtraction problem

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

2 Replies

Re: Time subtraction problem

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

Not applicable

Time subtraction problem

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!

Community Browser