Monday, January 20, 2014

Alternate States Use-Cases

Not to be confused with the 1980 movie starring William Hurt (Altered States)
I was recently invited to do a presentation on Alternate States and it's use-cases.  So I created an example application and thought it should be shared here as well.  This is a feature that was added in the first version of QV11.  It is extremely powerful especially for Comparative Analysis.

You can download the sample application here.

The concept of Alternate States is that we can create "copies" of the data model within the QlikView application.  These copies can then be referenced at the sheet, object or expression level.  It's like creating parallel universes where we can look at each universe side-by-side, making comparisons.  If the developer uses this concept effectively, users can create truly dynamic side-by-side comparisons.

Sheet Isolation
One use-case of this feature is to isolate sheets from each other.  There are times (infrequently) when users would want one sheet's selections to not propagate to the other sheets.  We can simply create a new state in the document and assign the sheet to the new state.  All objects created in that sheet will only respond to selections made in that state and will ignore selections in the default or other states.

Go to Settings --> Document Properties(ctrl-alt-d) --> General --> Alternate States
Select add and then name your new state.

Now within the sheet properties (ctrl-alt-s), simply pull down the Alternate State drop box and choose the new state. 
Any objects created within this sheet will by default respond only to selections in this state, thereby isolating this page from the rest of your application.  This really occurs because when an object is created on a sheet it will be assigned the <inherited> state.  This means that the object will simply take on the state of the sheet.
So continuing on with this method, you could conceivably create a QlikView application with several sheets that are all completely isolated from each other.  Simply create a new state for each sheet.
Comparative Analysis
The second use-case I have found for using Alternate States is when you want to enable a form of Comparative Analysis.  This allows to basically set up two list boxes containing the exact same field, but assigned to two different states.  Then we can create charts that utilize the selections from those states in expressions.  This allows to compare a mix of products against a mix of other products, for example.  This is extremely powerful as you can compare one product to several other products at the same time, or one grouping of products compared to another grouping of products.
The sample application includes two types of expressions to help explain the syntax. 
Method 1
The first example on sheet Product Bucket Comparison can be set up by following these steps:
Create two alternate states in Document Properties --> Alternate States.  I called mine Group1 and Group2.  Leave your sheet in the default state for this example.
Create two list boxes using Product_Long as the dimension.  The first list box will be assigned a state of Group1 and the second will be assigned Group2.  Note that when making selections in these boxes, they are not reflected in any other object right now, including the current selections box.
Now create a bar chart in the default state with no dimensions.  We will add two expressions:
      Group 1 Products: SUM({<Product_Long=Group1::Product_Long>}ExtendedAmount)
      Group 2 Products: SUM({<Product_Long=Group2::Product_Long>}ExtendedAmount)
Note the use of set analysis in the expressions above.  We are referencing the alternate states in the suffix of the modifier.  In layman's terms we are asking QlikView to sum up the extended amount, respecting the default state of selections where Product_Long corresponds to the selections for that field in the Group1 state.  The "::" allow us to reference and separate a state from the field in the set.  This object and the expression still operate in the default state "$" (implied), but will now respect the selection for that field in the other states.
Doing it this way ensures that the other selections on the sheet still operate at a "global" level.  So we can filter down to a specific universal criteria and then make selections in the Product list boxes to get our comparison.
Method 2
Now we will, in effect, do the same thing but use a different format of expression.  In this second example, the user can select different groups of months and then see the comparisons on the screen.  We could build static set analysis to cover frequent comparisons like this year vs. last ytd, but these dynamic kinds of comparisons would be difficult to achieve without Alternate States.  Using dates in your comparative analysis is a good use-case as I frequently get asked to have the ability to compare this April with February, or April against the prior three months as an example. 
Follow the same steps as above.  The only thing that will be different in this example (besides field names and such) will be the syntax in our expressions:
Group 1 Dates:  SUM({Group3<Category=$::Category,ProductLine=$::ProductLine,Product=$::Product,
[Product Num]=$::[Product Num],SubCategory=$::SubCategory,LastName=$::LastName,
[Home Owner]=$::[Home Owner],NumberCarsOwned=$::NumberCarsOwned,NumberChildrenAtHome=$::NumberChildrenAtHome,
The first method would actually still be preferred, but I wanted to show you another way of achieving the same result.  In this case we have assigned the state in the identifier of the set, and then made exceptions for the "global" list boxes on the page.  In this case they will be directed to the selections in the default state for those fields.
Notice in the straight table we have actually calculated the variance between the two sets in a single expression.  We did this very simply with: (column(1)/column(2))-1
As far as the two methods go, I find the first method to be easier in most cases where we are doing some sort of comparative analysis.  The second would be better if we are disregarding selections in the default state or if there are very few default selections being respected.
Probably the most important thing to remember as a developer is to make sure it is very obvious what the user is supposed to do with these sheets.  Use consistent colors when a group is referenced and include text descriptors to help lead users through the experience.
There are probably other use-cases I have not thought of and definitely much more elaborate examples of this.  I would love to hear about other use-cases for this feature.  Have fun!

Monday, January 13, 2014

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.

  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.

  • 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.

Monday, April 29, 2013

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.

Monday, April 15, 2013

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.

Monday, April 1, 2013

My Help Sheet Needs Some Help

The poor help sheet.  It never gets any attention.  Yet, it is an incredibly important piece of your user experience that should not be overlooked.  Many of us have copy/pasted/adjusted derivatives of this:

Now this is a very old example straight from the QlikView Demo site, but I have used something similar to this for too long, choosing to spend my attention on the other aspects of my design.

The old-school help sheet suffers from several issues. 

First, the browser-agnostic environment of today has brought in issues off differing treatment of alignments and kerning.  So, if we use a traditional layering of icon text objects on top of text objects with paragraphs of text, you will get differing results depending on the browser being used.  Here is an example of a sheet I used that was viewed later in Google Chrome:

Secondly, I have found that the copy/paste method is not scalable.  When the product features change, it would be better to manage the help descriptions and icons from a central location that could then be dispersed to all my apps.

Lastly, many environments still use both the AJAX and the IE Plugin client.  This creates yet another issue in that the menu icons look completely different for each client.  It sure would be convenient if we could tell which client is being used and then display the appropriate icon set.

I have handled these issues for my own development, so I thought I would share J

Help Template

You can find the template and the files I am using to populate it here:

The solution consists of first cropping pictures of all the different icons you wish to use in your help sheet.  This was completed with an example document opened first in AJAX and then with the IEPlugin so I could get all the possible icons. 

Then, a spreadsheet was created to house the names of all the pictures, id, client, category and description.  I also created another worksheet to house my larger chunks of text that would be shown in the help sheet.

Store all of this where your QVWs can reference it. 

Then I had to handle the loading of this.  I chose to create a txt file that I could manage outside of QlikView that could be referenced with an include statement:

$(Include=c:\qlikview documents\qv production documents\qlikview communal\include\help\help_bundle_load.txt);

Within the txt script, we first have to set variables with the paths of the icon repository and the spreadsheet.  I used the full UNC paths since QVWs using it might be scattered about the server.

SET vHelpDataPath = 'C:\QlikView Documents\QV Production Documents\QlikView Communal\Data\Help\Help_Text_Standard.xlsx';
SET vHelpIconPath = 'C:\QlikView Documents\QV Production Documents\QlikView Communal\Graphic\Help\';

Then we load the xls worksheets.  To get the actual pictures loaded into the QVW we use the BUNDLE LOAD.  This actually embeds the pictures into our documents.  Care should be taken with this function to ensure you are not loading a large number or large sized pictures as this would hinder performance.

BUNDLE INFO LOAD Picture_ID, '$(vHelpIconPath)' & [Picture_Name]
RESIDENT Help_Icons;

Lastly, we need to reference all of this data in our help sheet.  To load blocks of text, I created text objects with expressions like this:

=Minstring({<Help_ID = {2}>}Help_Text)

For the icons, I chose to create charts to display them, rather than trying to layer each one in between pieces of text.  You can create a chart with Picture_ID as the dimension, then Hide that column.  I then used two expressions, one to show the icon picture and one for the text descriptor. 

Notice the use of ClientPlatform().  This function will return nothing if the user is on the IEPlugin.  It will return the name of the browser if the user is on the AJAX client.  With this function, we can display only the appropriate set of icons to each user. 

The INFO function tells QlikView to display the embedded file associated with the field, rather than displaying the text.





You will have to set the picture expression to Representation: Image and select “Keep Aspect” from the Image Formatting dropdown.

Final Result

I still manually create example charts and controls representative of my data to fill out the rest of the help sheet, but at least the main portions of my help sheet can be easily created and centrally managed. 

After adding the rest of my data model and the supporting charts, here is my finished product opened in AJAX and then with the IEPlugin:

I always welcome your comments and suggestions.