Adventure Works Year Update

Adventure Works - Our favorite fictional company.

There are many times when we must rely on a sample database to develop our QlikView applications.  I use these DB's for POC's, training, for testing a particular technique, creating examples, blog posts and other situations.

My standard "Go To" has got to be the Adventure Works database that comes with MSSQL.

But the problem with ANY sample database is that the data tends to grow stale in regards to the date columns.  It seems that too quickly, data that felt so fresh in 2008 doesn't make much sense in 2014 or beyond.

So I developed a QlikView application that will take my favorite sample database and update all the date fields to the maximum year of my choosing.  This allows me to get a few more years out of my Adventure Works database without having to make excuses for the age of the data.

This particular application extracts all the tables from AdventureWorksDW2008R2, transforms the date columns in needed tables and then saves all the tables in the schema to the directory of your choice.

Instructions

1. Download the qvw here
2. On the first page of script, create a new connection to match your SQL instance.
3. Adjust the value for vMaxYear to the highest year you want to appear in the sales data.
4. Adjust the value of vQVDPath to the fully qualified path where you wish the transformed qvd's to be deposited.

Notes

• The script is specifically for AdventureWorksDW2008R2.  But feel free to adjust to the version you are using or another db.
• The DimData table is omitted since I usually create my own calendars in QlikView.  All other schema tables are extracted and stored to qvd.

Control Your Date Controls

Do your QlikView applications have date controls?  Almost all of mine do in one way or another.  Most of the time, I have resorted to the traditional Year, Quarter and Month list boxes that we are accustomed to.  Something similar to this:

This is fine for many business users but sometimes users have more exacting needs that cannot be selected with the above controls.  If a user needs to look at the months of November 2012 and Jan 2013 for example, there is no way to make that selection with the above list boxes.  So, we have to add another list box for the Month-Year combinations. 

Then if users have need to look at specific quarters or weeks in the same manner, now we have lots of extra list boxes on the screen that we do not likely have room for.

With the addition of containers, we now have an easy way to provide our users with the best of both worlds.  The key to this idea is that the user likely does not need to utilize both styles of date controls at the same time.  They will need one or the other for any given analysis need.  So hiding one set while the other set is active allows us to reuse the screen area. 

Using nested grid and single-item containers, you can create a very powerful date control set while, preserving the vital screen real-estate for your real data.  You may also incorporate cycle dimensions for a different feel.  Lets look at some examples.  You can find the qvw here.

In the default view, the user sees the traditional date segment view:

If the user selects Range, they will be presented with the ability to select specific year, quarters or months:

 And finally, they can drill down one more step to find specific weeks or dates.

Another option to display the date ranges is to use a list box with a cycle dimension to change the interval type.  This gives us a clean look, allows a larger amount of values to be displayed at once, but limits us to one type of date interval at a time.

There are probably other variations of this idea that may be even more effective and helpful for the user.  Hopefully you can utilize and improve upon this in your own work.

Comments and feedback always welcome.

Look! I can see my qvw from here.

Our friends over at Vizubi, famous for their great NPrinting product, have created a neat little plugin called QlikLook.

Have you ever said to yourself, “Where is that qvw that I did <blank> in?”   Which qvw had that trick for establishing a closed hierarchy?  Where was that expression with the crazy set analysis in it?  Which qvw has that great mini-chart example?  Which qvw out of the 200 scattered throughout my local drive is the one that I am looking for?

QlikLook allows you to preview any QlikView document (qvw) in an Outlook or Windows Explorer preview pane.

In addition, you can browse the sheets and make selections in the preview pane.  Basically you can use it as if you are using the document in a browser from the access point.

You can find the application here .  IT IS FREE.  You will be identified through LinkedIn, thus allowing you to download the executable and license.  The install is straight forward.

Now, if you enable your preview pane in Outlook (in the View menu) and then select an email that has a qvw attachment, the preview pane will fill with the sheets in that qvw.  For Windows Explorer, the preview pane is turned off by default.  To turn it on go to Organize à Layout à and enable Preview Pane. 

Although there is some lag depending on the size of the document, it seems to respond very well and has helped me scan through documents without opening QlikView (or yet another instance of it).

This is an incredibly convenient and useful tool.  I would encourage everyone to check it out.