2 Replies Latest reply: Aug 3, 2011 2:31 PM by ggoyal_123 RSS

    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.

        • Re: Time subtraction problem
          Rob Wunderlich

          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