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
WeekDay [Mon..Sun] and
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
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.
- 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
Further reading related to this topic: