Calendar fields and time fields can be either cyclic or sequential. The sequential fields have values that are consecutive, never repeat themselves and have an open range. Examples of sequential fields are

 

     Year             [.., 2011, 2012, ..],
     YearMonth    [.., 2011-Dec, 2012-Jan, ..] and
     Date             [.., 2011-12-31, 2012-01-01, ..].

 

This is in contrast to the cyclic fields that have a limited number of values in a closed range, where the values are repeated after a time. Examples of cyclic fields are

 

     Month                [Jan..Dec],
     WeekDay          [Mon..Sun] and
     WeekNumber    [1..53].

 

This may seem obvious. Why do I write a blog post about this?

 

Because I think that we often are caught in the conventional. As QlikView developers, we tend to use the existing functions as they are, without questioning what we really want to show. However, we can create any fields we want. It’s all about how data is best visualized and how the user can best interact with data. Your role as a QlikView developer is to create a user interface that supports a good user experience and enables the user. How the field values are constructed is a very important part of the user experience.

 

This post is about encouraging you to use your imagination to create customized calendar fields.

 

First of all, the same field can be created in two versions: as a cyclic field and as a sequential field. Think of the difference between Month and YearMonth, for example. The first contains cyclic months, the second contains sequential months:

 

     Month(Date) as Month  // Cyclic
     MonthName(Date) as YearMonth // Sequential
     Date(MonthStart(Date), 'YYYY-MM') as YearMonth_Alt2 // Sequential

 

Sequential Months Listbox.png

The above is true for most calendar and time fields: they can be created in pairs – one cyclic and one sequential. For example

 

Quarters:

     Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) as Quarter // Cyclic
     QuarterName(Date) as YearQuarter // Sequential

 

... or Week numbers:

     Week(Date) as WeekNumber // Cyclic
     Dual(WeekYear(Date) & '-W' & Week(Date), WeekStart(Date)) as YearWeek // Sequential

 

... or Hours:

     Hour(Date) as Hour // Cyclic
     Timestamp(Round(Date,1/24), 'MMM DD, hh:mm') as DateHour // Sequential

 

Pairs.png

 

Secondly, you can use the Dual function to create fields that are cyclic and sequential at the same time, i.e. they have a cyclic textual (display) value, but an underlying sequential numeric value. This means that the same cyclic value, e.g. ‘Q1’, will be shown several times in the sequence.

 

     Dual(Month(Date), MonthStart(Date)) as SeqMonth
     Dual('W' & Week(Date), WeekStart(Date)) as SeqWeek
     Dual('Q' & Ceil(Month(Date)/3), QuarterStart(Date)) as SeqQuarter

 

Such fields are very useful in charts. The chart below uses such a field (SeqQuarter) as first dimension and Year as second dimension. This way the color coding and the Year legend are created automatically.

 

Sequential Quarters Chart.png

 

Recommendations:

  • Create many fields in your master calendar. A few extra fields in the calendar table don’t cost very much – neither in terms of script execution time, nor in terms of memory usage.
  • Create both cyclic and sequential fields in your master calendar
  • Use cyclic fields in list boxes
  • If you have a chart with a single dimension, a sequential field is often preferable
  • If you have a chart with several dimensions, cyclic fields are often preferable

 

HIC

 

Further reading related to this topic:

The Master Calendar

Relative Calendar Fields

Fiscal Year