Formatting Intervals

In this post I’m going to show some tips for formatting intervals / durations in Qlik.

Datetimes in Qlik utilize a serial number that equals the number of days since December 30, 1899. The integer value represents the day and the fractional value represents the time of the day. The difference between two datetimes is an interval or duration.

Qlik provides the Interval function to format numeric interval values as dual strings for display. Interval() may be used both in charts and script. The Interval function accepts an optional format string that specifies the output format.

An example interval numeric value could be 3.3414351851898, three days and a few hours. The expression

Interval(3.3844097222245, 'hh:mm:ss')

will yield “81:13:33”. Note the 81 hours representing 3 days and 9 hours. We can exceed 24 hours because interval accumulates overflow in the largest unit. For example, we could show the duration as minutes using

Interval(3.3844097222245, 'm') 

yields “4873”

We can add the format character “D” (or “d”) to indicate days.

Interval(3.3844097222245, 'D hh:mm:ss')

will yield “3 09:13:33”.

Is it clear what this number string represents? The “:” may allow a user to intuit the hh:mm:ss. But what about the “3”? Can we make this clearer by adding some additional text to the output? (From now on I’ll use a field named “Duration” in my examples). Will this work?

Interval(Duration, 'D days hh:mm:ss')

No. We will get some funny looking output because the “d” and “s” in “days” will be interpreted as format characters.

The format pattern does not have a syntax to declare “this is a literal, not a format character”. We can work around this limitation by using a non-format character as a proxy and patching it up with a Replace function. I’ll use “a” to represent the word “days “

Replace(Interval(Duration, 'd a, hh:mm:ss'), 'a', 'days')

You may have noticed that the values are now left justified. This is because Replace() returns a string, whereas Interval() returns a Dual value. We probably want to retain the Dual nature to allow for proper sorting. I’ll fix it by adding a Dual() function.

Dual(Replace(Interval(Duration, ‘d z, hh:mm:ss’), ‘z’, ‘days’), Duration)

How about if I want to show only days and hours?

The format string “d h” will show days and hours. Minutes and seconds will not display. However…the internal numeric value will remain unchanged. Generally not a problem, but may cause some duplicate lines if the value is used as a Dimension or filter. Because of this, I like to round the value to match my display.

Interval(Round(Duration, MakeTime(1)), 'd hh')

MakeTime(1) is the value of one hour. If you want to round to 4 hour segments use MakeTime(4). Instead of Round() you can also use Floor() or Ceil(). For example, to make 15 minute buckets use Floor(Duration, MakeTime(0,15))

Let’s put Round and Replace together to display a clearly formatted days and hours. I’ll use “b” as proxy for “hours”.

Dual(
  Replace(
  Replace(
   Interval(Round(Duration,MakeTime(1)), 'd a, h b')
  ,'a', 'days')
  ,'b', 'hours')
, Duration)

Almost there. I don’t like the “0 days”. How about we don’t display days if zero? Let’s make “days” conditional on Duration being >= 1.

Dual(
  Replace(
  Replace(
    Interval(Round(Duration,MakeTime(1)),
      if(Duration >= 1, 'd a, ', '') & 'h b'.)
  ,  'a', 'days')
  ,  'b', 'hours')
, Duration)

I like it. It has become a bit wordy. I may want to use it elsewhere in this app (or others), perhaps for a different field. I’ll make it reusable by assigning the expression to a Variable with Parameter. Add this statement to the load script.

Set FormatDuration = Dual(
  Replace(
  Replace(
    Interval(Round($1,MakeTime(1)), if($1 >= 1, 'd a, ', '') & 'h b')
  , 'a', 'days')
  , 'b', 'hours')
, $1);

“$1” is a placeholder for the fieldname. The chart expression is now simply:

$(FormatDuration(Duration))

Looking for more tips & tricks? Have some of your own to share? Join us in September at the Masters Summit for Qlik in Vienna. In addition to our focused hands-on workshops on migrating, managing and developing in Qlik Cloud, we’ll be sharing lots of tips and techniques gleaned from years of Qlik experience.

-Rob

Share

One thought on “Formatting Intervals”

Leave a Reply

Your email address will not be published.