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.