If you think coding complex Set Analysis expressions and writing repetitive script is an efficient use of your QV development time, then this post is not for you. Go back to memorizing pi or whatever it was you were working on. For the rest of us, read on.
With Qlikview Components (Qvc) you can quickly build all the parts needed for many point in time and period over period analyses. Let’s walk through an example.
Download the latest distribution (this article require V0.7+) of Qvc from the download link at
Unzip the distribution file anywhere on your disk. If you are new to Qvc, start by reading the ReadMe file.
Let’s assume our QVW contains a fact table of Orders with a field named “OrderDate” that we will use as the date for period reporting.
Add this include at the beginning of the script to bring in the Qvc routines.
After loading the fact table, generate a Master Calendar with this call:
The Qvc.CalendarFromField routine will build a calendar with dimension fields named OrderDate, Day, Month, Year, Year-Month and Quarter. You can optionally include a language file to use your local language for the fieldnames — in this example Swedish.
Now that we have a calendar, we can use any of the calendar fields in listboxes or chart dimensions. And… Qvc.CalendarFromField() also creates a collection of set analysis expressions stored in variables. The variables follow the naming convention vSetPeriod. For example:
The documentation for Qvc.Calendar provides a list of all generated vSet* variables.
These variables in a chart expression like:
which will sum up OrderQuantity for YTD of the previous year. The actual set analysis expression is rather lengthy but the variable is compact.
I’d like to credit Mike over at iQlik for first teaching me the set analysis variable idea at http://iqlik.wordpress.com/2011/01/01/point-in-time-reporting-out-of-the-box/
Now we can quickly whip up a chart like this without coding any of the complex stuff:
The documentation supplied with Qvc provides examples for all Qvc routines. On the Documentation.qvw “Details” sheet select Qvc.CalendarFromField. You’ll be able to open a sample qvw where you can explore the vSet variables and learn how they interact with selections.
If you have comments or questions about Qvc, use the User Forum found on the project page.