Conditional Dimensions and Expressions – Tips and Tricks

With the shipment of QlikView 11 comes an incredibly cool feature:  Conditional Enablement of Dimensions and Expressions.  That is “fancy speak” for being able to change the columns in your chart based on any number of conditions.

This can have various purposes but the one I am using most often is the ability for your users to create their own Ad Hoc reports.  Let’s create an example to illustrate some tips I have learned.

You can find the example qvw here.

The first thing you will want is an inline table of the dimensions and expressions you will want the user to be able to select.  I would recommend two columns, one for the descriptor and one to uniquely identify it by a single letter. 

AdHoc_Dimensions:
LOAD * INLINE [
    _Dimension, _Dim_ID
    Order Num, A
    Product, B
    Class, C
    Color, D
    Year, E
    Month, F
];

This table will not be associated to any other tables in your model.  Depending on your need, you might create a table for your selectable dimensions and one for your selectable expressions.

Now that you have your selectable dimensions and/or expressions to choose from, you can go to the layout and create list boxes to show the options.  I like to add the Clear and Select All menu icons to the captions for these lists.  It might also help to add a help message or label the caption with “Pick the columns to display” or similar.

Now create the object that the user will be customizing.  You can put conditional dimensions or expressions in any kind of object.  Here, we will create a straight table.  Add all the fields referenced by your dimension list.  And add the calculated expressions that relate to any selectable expressions you created. 

This is where the magic happens.  In the dimension properties, check the option for “Enable Conditional” and enter the following code:

=SubStringCount(Concat(_Dim_ID, '|'), 'A')

You will want to add similar code to each dimension, changing the letter to correspond with what you used in your inline table.  Then do the same thing for your expressions if desired.

The last thing to do is add a Calculation Condition to the general tab of the object so that the object only displays when at least one dimension and expression is selected:

GetSelectedCount(_Dimension)>0 AND GetSelectedCount(_Expression)>0

I then add a message to Error Messages for “Calculation condition unfulfilled” that reads similar to “Please choose at least one dimension and one expression to display... “

Click OK and now you should have a working Ad Hoc report.  Choose various dimensions and expressions and the chart should change according to your selections.

Some tips and warnings:

• I use a letter ID because there is less potential of accidently displaying more dimensions than intended with the SubStringCount function.  For example, 1 will return a result for 1, 10, 11, 21, 100 etc.  Using the actual dimension names can also be troublesome especially with columns like “Invoice” and “Invoice Line” for example.

• As of this writing (QV11 Initial Release), there is a bug in interactive sort for columns.  I haven’t been able to isolate the behavior.

• Also, I have found that when using a pivot table, there is sometimes an issue when attempting to pivot a dimension horizontally.

Even with the pivot and sortation issue, I am finding that my clients are very happy with this new flexibility.  It was something not easily accomplished in prior versions.

I hope you have success with this and are able to expand the uses further.

QlikView Designer Quick Tips

Here are my favorite QlikView designer tips.  These are just some quick tips and shortcuts.  Most of them are common knowledge.  Maybe there are a few that you might have forgotten about or maybe a few are genuinely new to you.  Feel free to add your own tips.  We could all use them!

Pixel by Pixel Movement – Everybody knows how difficult it is to get the alignment of objects exactly right.  To move an object a pixel at a time, select it and then hold down ctrl while using your arrow keys.

10 Pixel Movement – Hold down ctrl + shift while using your arrow keys to move an object 10 pixels at a time.  Using the ctrl and ctrl + shift techniques give you very flexible yet precise movement.  Try using these after you have zoomed in 200%.  Now you are cooking with grease!

Move Locked Objects – What a pain.  You just finished locking down all your objects by unchecking Allow Move/Size and the boss wants you to change the layout.  Just leave the items locked.  Hold down the alt key and you can freely drag and size objects.

Expression Overview – Go to Settings --> Expression Overview to access all the calculations in your app.  The great thing about this is if you have many expressions that need to be slightly changed in the same way, you can use Find/Replace to locate the part to be changed and replace all of them in one step.  That beats searching every object in every tab to make your changes.

Move Chart Parts – Use Ctrl-Shift when selecting an object to engage the separate parts of a chart.  This allows you to move and resize the legend, axes and chart titles with ease.

Document Properties – Settings --> Document Properties are very valuable, especially if you hid a sheet that you later need to access.  Find all your sheets and access their properties on the Sheets tab.  The General tab houses the styling options and the Triggers tab gives allows you to trigger actions based on many different events.

Double Click Columns – Just like in MS Excel, you can double click a column edge to have it automatically fit your heading and data.  You can also right click and select Fit Columns to Data.

Cool Shortcuts:

Ctrl-T	Table Viewer
Ctrl-E	Script Editor
Ctrl-R	Reload
Ctrl-Alt-D	Document Settings
Ctrl-Alt-V	Variable Overview
Ctrl-O / Ctrl-C / Ctrl-X / Ctrl-V / Ctrl-Z / Ctrl-S	If you aren’t aware of open / copy / cut / paste / undo / save, you are in the wrong business.

QV Source and Google Analytics – Retrieving Data

I have been fortunate enough to work with a new product from Industrial Code Box.  QVSource is a great tool that allows you to connect QlikView with all sorts of web-based data services.  There are some very interesting connectors including Facebook, Twitter, Klout, LinkedIn, OData,  Infochimps and YouTube available with this tool.

But the connector that I have most used so far is the QVSource connector for Google Analytics.  Google rules the web statistics space as it does anywhere else they enter.  And included with Google Analytics is the ability to feed data directly through an API.  QVSource makes use of this feed and converts it into QlikView script that any developer can use to harness your Web Analytics with the power of QlikView.

The first step for creating an application is to open up QVSource and configure the connector.

 You will enter the GA user and credential.  Then click the URL build link.

This will open up the GA Data Query Feed Explorer.  You must be logged in to a gmail account that has read access to the GA account.  If you click in the “click to list your accounts” box and nothing happens, make sure:

• Your gmail account has the correct access.
• Try Logout and Reauthorize.
• Or use Google Chrome as your browser.  The accounts box does not authorize correctly when using IE, even if you are signed into gmail.

Then you can use different combinations of dimensions and expressions to design the queries you need.  Keep in mind that GA only allows a maximum of 7 dimensions and 10 expressions.  Also, GA does not provide a unique user ID (against their policy).  So it can be a bit of a challenge to get down to the lowest detail possible.  As we know, QlikView breathes deeply in that granular level.  I have found that creating multiple queries can be effective if most of the dimensions are repeated to form a key and then you can cycle through any other needed dimensions.  In my case, I used latitude, longitude, date and hour as a key for all my queries.  That allowed me to bring in other dimensions 3 at a time.  Disregard the note that says max 10,000 records.  The query window is limited by 10,000, but the API call is not.

Once you find a query you want to use, simply copy the URL that forms above the query box and paste it into the URL box in QVSource.  Then select the Connector Data tab.  Select Data and then click the QlikView Load Script tab.  You will now see the load script you will need to use in your QlikView application.

Simply paste your script into QlikView and reload.  One thing to remember is that QVSource will need to be open any time you reload the application.  I found it helpful to open QVSource using Windows scheduler so that it works like a service.  Alternatively, you could use QlikView server to open the program before the reload runs.

You now have Google Analytics as a source for QlikView.  I am having lots of fun duplicating GA visualizations and surpassing them with my own creations.  And the client I have developed the QlikView document for is ecstatic with the results.  We are now talking about adding in Twitter and Facebook Page statistics to get a more integrated Web Strategy application.

Hierarchy – Reflexive Transitive Closure

I have always wanted to make more use of the list box with the tree view option.  It has several advantages that make it a great way to select dimensions in QlikView:

•  Ability to fit several selectable fields into a single object.
•  Naturally shows the hierarchal relationship for the displayed fields.
•  Intuitive to navigate for anybody who has come within 100 yards of a computer.

But many people, including myself have shied away from using the Tree View option because it is hard to get right.  What developers quickly realize is that selecting a non-leaf parent does not associate to the children of that parent.  This is because the QlikView function, Hierarchy, creates an expanded nodes table, but does not create the optimal Reflexive Transitive Closure style of this table.

Expanded Nodes table using Hierarchy function alone:

NodeID	Name1	Name2	Path	Depth
1	Agatha		Agatha	1
2	Agatha	Aaron	Agatha/Aaron	2
3	Agatha	Adam	Agatha/Adam	2

In above table if Agatha has no sales because she is the manager, when someone selects her in the Tree View list box the sales for children Aaron and Adam will not appear.  In addition, this behavior is even more confusing because the path changes depending on whether Agatha is expanded or not.  This results in the association sometimes working and sometimes not working.

Reflexive Transitive Closure:

NodeID	Name1	Name2	Path	Depth
2	Agatha	Aaron	Agatha	1
2	Agatha	Aaron	Agatha/Aaron	2
3	Agatha	Adam	Agatha	1
3	Agatha	Adam	Agatha/Adam	2

In this version, the association will work correctly every time.  This is because the NodeID (linking back to your transactions) has extra rows to account for the changing path as one clicks higher in the hierarchy.  Only the leaf nodes (NodeIDs existing in transactions) need to be included in the table.

Now the question is how would you create this table.  I have created a downloadable example with the actual script but here are the steps.

• Load in transactions first because they will be used to identify the leaf nodes.
• Use an exists(NodeID) or a join to add an IsLeaf flag to your adjacent nodes data.  You can load the adjacent nodes table using exists in one step.  I chose to use a join after the adjacent nodes load to keep the solution portable.
• Use the Hierarchy function to flatten your adjacent nodes table into an expanded nodes table.  You will want to use the Path option and remember to leave in your IsLeaf field.  This leaves you with the traditional hierarchy table that includes all nodes. 
• We only need the leaf nodes so now we resident load this table into a new table, only loading the leaf nodes and sorting by the depth of the levels. 
• Now we create a looping structure going from the deepest level and working up to the highest parents.  Each loop adds records up the hierarchy so that a child has a corresponding path all the way up.  The loop will run for as many levels as you have minus 1. 
• Then just drop any of the temporary tables you created and any extraneous fields not needed in the final data structure.

So this will create an expanded nodes table with reflexive transitive closure.  There are still some issues with the way QlikView handles this:

•          You will want to select Suppress Horizontal Scroll Bar in the list box due to a rendering issue in AJAX. 
•           There will still be some confusion in selecting because depending on whether the parent is expanded or not, the children might not look selected.  I have not been able to work through that and have not had successful results trying to tie a trigger to it.
•           The last item is that if you have a model where the parents have transactions as well as the children (different levels), you cannot isolate the parent’s transactions in the list box, since we have forced the association to the children.  The solution here would be to either go back to the traditional hierarchy table or to use an additional list box to select a single parent.  I mostly find this to not be an issue since usually the transactions are only at the lowest level.

Please enjoy and I look forward to any comments or additional analysis on this topic.