Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

 

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

4 Comments
Or
MVP
MVP

Since you've picked the perfect time of year for it - I do not recommend blindly creating cyclics fields in list boxes. This past week I've been bombarded with people asking me how to select Week 1 (which is partially in 2012 and partially in 2013, since we use ISO weeks) or how to view data for December and January combined. In three months, I will get queried on how to select Q4 2012 and Q1 2013. Cyclic fields, which I use in many of my older QVWs, do not support this, as far as I know.

In recent years, my policy is to use cyclic fields in list boxes only when cross-cyclic selections are likely to be minimal (e.g. people will usually select years, not months and weeks, or will generally only look at a single calendar year rather than rolling periods). I would recommend this approach for everyone.

I like the idea of using dual() for charting - this can work around some of the limitations in using simple cyclic fields or simple sequential fields. I'll have to give it a try.

1,579 Views
hic
Former Employee
Former Employee

Thanks for your input. I think you are totally right: If users want to make cross-cyclic selections (like Dec 2012 and Jan 2013), then you should definitely avoid list boxes with cyclic fields.

The purpose of my blog post was to introduce the concepts of cyclic and sequential and to make people think about what they (or their users) want.

You obviously had thought about these things before...

HIC

1,579 Views
hic
Former Employee
Former Employee

It's quite easy: I have two dimensions, SeqQuarter and Year. SeqQuarter is defined using dual as described in formula in the post. Then it automatically becomes like in the picture.

HIC

0 Likes
1,579 Views
Anonymous
Not applicable

Good article!. When I read your articles I learn the proper terms in Qlikview environment. Thanks Henric

Best regards,

Walid

0 Likes
1,579 Views