A modular, plug-and-play approach to displaying beautiful multiple non-consecutive date ranges in QlikView!
I have been meaning to implement something like this for years, but could not find the time to figure out the technique which is, as you will see, not so simple. However, life is all about balance; since the last blog post was more business-heavy, I figured it was time for an old-fashioned geek-out session.
What the heck am I talking about?
Always a good question. Let's start with the simplest use case. Let's suppose we have an application with a field called MonthYear, that displays all months from 2013 - 2016 in the format MMM YYYY. And let's also assume that the application has a "breadcrumb trail" that displays a user's current selections in a text object. As a user, I then select months Jan 2016, Feb 2016, and Mar 2016. The breadcrumb trail will now display one of two things, depending on the parameters of function GetCurrentSelections(): (1) MonthYear: 3 of 48, or (2) MonthYear: Jan 2016, Feb 2016, Mar 2016. To save real estate and to make a m ore attractive user interface, a QlikView developer may want to replace the latter case with a range using functions min() and max(), so that the breadcrumb trail would display: MonthYear: Jan 2016 - Mar 2016. No problems so far.
But what if the user selects non-consecutive months (Jan 2016, Mar 2016, and Apr 2016)? Clearly, using min() and max() to create a range would be incorrect, since the results string of "MonthYear: Jan 2016 - Apr 2016" would imply the inclusion of Feb 2016, where it is actually excluded from the user's selections. So the developer is back to the original two choices: (1) MonthYear: 3 of 48, or (2) MonthYear: Jan 2016, Mar 2016, Apr 2016. The first option isn't great because it does not tell the user any real information, such as which 3 months are selected. The second option is fine when 3 months are selected, but would quickly become unmanageably long as the user adds more and more distinct values to his selection set. Wouldn't it be great if we could display a string that looks like this: Jan 2016, Mar 2016 - Apr 2016?
A More Advanced Use Case
Not all multiple non-consecutive date range use cases rely on users selections; some represent a reflection of actual associated data in the model. For example, suppose you are running a hospital and assign certain staff surgeons to certain operating rooms for certain months. A single operating room can have multiple surgeons assigned to it in a given month and, likewise, a single surgeon can be assigned to multiple rooms in that month. You can load this data into QlikView, but displaying a comma-delimited list of months for a particular surgeon/room is not very effective. For one thing, the list could be huge (each year has 12 months, and all that jazz), which would make it both costly in terms of screen real estate and hard to read. For another, it would be difficult to spot gaps. Using our technique, though, you could visualize this same data in a much cleaner way:
Download & Instructions
We are pleased to share the technique with all who wish to use it and, if you have a Qool use case of your own, we would love to hear from you in the comments section below!
- For easy reusability (and so we could use sexy catch-phrases like "modular" and "plug-and-play" in this article) the application has been built using variable parameterization. The recommended method is to implement this same technique in your applications by creating the following 5 variables and copy-pasting the definitions from the above app: (a) vMultipleDateRange, (b) vMDR_CheckDims, (c) vMDR_CheckDate, (d) vMDR_CheckDims_OR, and (e) vMDR_DimsNotNull.
- In order for this technique to work, your application must include a calendar that is sorted in ascending order in your data model. Acknowledgement: special thanks to my friend and colleague, Srini Chitrapu, for helping me realize that aggr() sorts fields in load order, rather than alphanumerically.
- The first parameter that should be passed into the variable (and this is the only parameter that is mandatory) should be a simple semicolon-delimited string. This should consist of (a) the name of the date field you wish to use to show ranges, and (b) a one-letter code representing the level of the date field, namely: (Y)early, (Q)uarterly, (M)onthly, (W)eekly, or (D)aily. For instance, you could type in: MonthYear;M or My Year Field;Y. The date field you use needs to have a valid numeric representation in the data model and cannot just be a text string.
- You may optionally pass a second parameter into the variable to represent other dimensions in your straight/pivot table. This should simply be a semicolon-delimited string of all relevant table dimensions (typically, this should include all dimensions that contribute to the granularity of the table in question). In our example above, the second parameter would be: Operating Room;Physician. The order in which you specify these dimensions does not matter for purposes of the solution, although it may come into play if you use a function like dimensionality() to switch between multiple instantiations of the variable, each with a different set of parameters.
- [Square brackets] and "double quotes" should not be included in the variable parameters, even if a field has a special character in the name. These brackets will be automatically added by the formulas in the correct places. There should also not be any extra spaces around comma and semicolon separators.
- Tip: if you will be using this technique within a straight/pivot table, you may want to suppress null dimension values. Although the expression will return an empty string for null dimensions, this empty string will not be interpreted as a null by QlikView, so the row will not be automatically hidden otherwise.
- If you prefer to not use parameterized variables in your application, you can generate a static expression by pressing "Show Help" in the application, following the instructions therein, and then pasting the generated formula into your application in the necessary place(s).