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
http://qlikviewcomponents.org.
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.
$(Include=..qvc_runtimeqvc.qvs);
After loading the fact table, generate a Master Calendar with this call:
CALL Qvc.CalendarFromField(‘OrderDate’);
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.
$(Include=..qvc_runtimelanguageqvc_language_SE.qvs);
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:
vSetYTD
vSetPreviousYearYTD
The documentation for Qvc.Calendar provides a list of all generated vSet* variables.
These variables in a chart expression like:
=Sum(Sum($(vSetPreviousYearYTD) OrderQuantity)
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.
Interesting! Thanks for sharing!
Hi,
does it take leap years into account? Had a problem when using 2012-02-29 as the date. Got no result for vSetPreviousYearYTD since 2011 did not have a Feb 29. Any solution for this?
@Magnus
It should work with the Feb 29 date because the set expression tests for <=. I tested with the Calendar.qvw example selectin 2/29/2008 and it worked fine. Not sure what problem you may be having.
Hi, thanks, great script
one question,i dont understand how you combine this with other restrictions like flag…
for example, when my flag=P, show the month to date value of field Data
sum($(vSetMTD),Flag={‘P’} Data) doesnt work
The vSet variables don’t currently allow for combining with other set modifiers. Adding this capability was discussed on the user forum here https://groups.google.com/forum/?fromgroups=#!topic/qlikview-components-users/a_wkecy1Y3k
but never got fully resolved. I’ll add it as an enhancement request to the issue tracker.
-Rob
Hi,
Following up on Magnus’ question regarding leap year.
I used the vSetYTDModifier and vSetPreviousYearYTDModifier on two cumulative columns, this and previous years resp.
In these modifiers MonthSerial, Year and Date are set. For Date this year is {“<=$(=Max([Date]))”}
and for prev year {“<=$(=AddMonths(Max([Date]), -12))”}.
There is problem since 29 Febr last year will not be included in prev year.
Do I use the QV Components in a wrong way or is this an issue?
rgds
Gunnar R
Great work on this !
Loved the Calendar.
So plug & Play !
I would like to have a Month/Year dimension in the format (MMM/YY) and also another one to get the last six months to date.
What is the best approach ? Customize it someway or make a wish list to the developers ?
Congratulations !
Hi Rob,
I was testing this out today and got some behavior with the vSetYTD expression that I did not expect.
I have a sales table with actual sales by date. I construct the calendar using Qvc.CalendarFromField. All goes well; the calendar and set expressions are constructed.
I have a simple chart with =sum ( $(Sale_vSetYTD) SalesActuals ) as the expression. There is a particular dimension which when selected, has only sales for 2013. The expression works fine until the year 2012 is selected, at which time, the expression goes to 0.
Is this the expected behavior? My intuitive understanding of this set from how it is constructed, is that it should independent of the year selected, since it explicitly sets the Sale Year to nothing.
Hi Kevin,
Yes, that is the expected behavior. Year (and all the other dates fields in the Set) is initially cleared as part of an initialization. That’s followed by modifiers for specific fields, depending on the set.
-Rob
This is fantastic Rob! However, everything we do is by fiscal year. And to complicate matters, we use the 4-4-5 method. Is there anyway to adapt your functions to use this type of calendar?
@Unknown
The Qvc.Calendar routines do provide for fiscal calendars by setting a first month of the year parameter.
You can add an additional field using the Extension file (see the doc) to define the 4-4-5 period.
Are you referring to CalendarExtFields.qvs ? I don’t see how to do it. For example, our fiscal years are as follows, with the fiscal months following a 4-4-5 week pattern:
FY2013 9/2/12 – 8/31/13
FY2014 9/1/13 – 8/30/14
FY2015 8/31/14 – 8/29/15
To use Sept as the Year start as in your example, use a start month of -3.
CALL Qvc.CalendarFromField(‘OrderDate’, ‘Fiscal Calendar’, ‘Fiscal ‘, ‘-3’);
I have to admit I don’t have any experience on generating 4-4-5 periods (I have clients who use these but they pre generate the Calendar elsewhere like SQL Server).
If you search the Community for 4-4-5, you’ll find some QV examples. Any fields you add through the CalExtFields.qvs will be considered in the vSet variables. However, I’m not exactly sure you can get there from here.
You might want to start a topic in the QVC User Forum at https://groups.google.com/forum/#!forum/qlikview-components-users
What’s up to all, as I am really keen of reading this blog’s post to be updated regularly.
It includes fastidious data.
Here is my blog post :: buy fast instagram followers
Hi, I keep getting errors on my script.
i guess this has to do with the format of my date field. How do I find out what the issue is?
Michiel
The QVC User Forum https://groups.google.com/forum/#!forum/qlikview-components-users
is the place to get help. Post your script error there.