Cyclic or Sequential? - Qlik Community

hic · ‎2013-01-04

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

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

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.

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