I was building a QlikView application for financials. My client and I had discussed the idea of this application a year earlier but it was impossible then. Version 8.5 had not been released. The ability to look at dozens of simultaneous selection sets is the key to making this great idea work.

Zoom forward to 2009. Versions 8.5 and then 9.0 are released with features including Set Analysis, unlimited rows, chaining of document selections, data export from the script, and Dynamic Tables. These innovations remove the architectural limitations of QlikView that had tied my hands. A year ago I could not deliver the solution that was in my head and that my client needed. Now these limits are gone and I can build exactly what my client needs.

Build exactly what my client needs? This is the first time that this thought crossed my mind. It’s true! With the release of version 9, QlikView has entered a new phase. One that no other product can match.

QlikView is the first and ONLY tool on the market in which every business analysis that I have been asked to build can be built with confidence and an expectation of success. Dream big!

QlikView is not SPSS or JMP, and it never will be, but since I have never been asked to do anything more complex than a regression, QlikView works perfectly.

QlikView is the tool to turn to. It delivers results. Real value, right now. And you can be confident that it can achieve any business analysis you can think of. To get an idea of what QlikView can do, follow this thread on LinkedIn with over 100 unique uses for QlikView.

Related posts:

1. QlikView 9 Beta!!! There are A TON of new features coming in version...
2. QlikView 9: Export Document Layout to XML One of the new features in QlikView 9 is “Export...

Related posts brought to you by Yet Another Related Posts Plugin.

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.

sum( {$ Customer = P({1<Product={‘Shoe’}>} Customer)>} Sales )

The expression inside P(..) is saying “Start with the complete data set and select the value “Shoe” in the Product field. The list of customers after making that selection is what I want to use to calculate sum(Sales).” Ok, that’s great. It’s dynamically generated based on selections in another field. Now we don’t need a crazy concat() expression to acheive this like we did in 8.5. And by using E(..) instead of P(..), it’s now possible for the first time to select the excluded values! That wasn’t even possible in 8.5!

But the real power is the second Customer. You do not need to return a list of values from Customer in order to make a selection on Customer.

So what? My favorite application of this new ability is in using multiple calendar tables. Let’s say you want to look at sales between two different calendar periods…

• In the pre-8.5 method, without set analysis, you were limited to sum-if logic. In order to pick two dates and calculate over two periods, both calendars needed to be unconnected from the data model. Because these tables are unconnected, other list boxes would not update as the user made date selections.
• The 8.5 approach to multi-time-period analysis has been to use bookmarks and macros. You make one period selection and bookmark it, then make the next selection. One calculation applies the bookmark, the other does not. List boxes for other dimensions continue to update as new selections are made. The user cannot see the bookmarked date selections again without applying the bookmark. The bookmark can also contain additional, unintended selections. The way to avoid this is with macro code that makes selections, which is BAD BAD BAD because it kills the server selections cache.
• THE NEW  QlikView 9 way is to have two calendar tables in the data model, one connected and one unconnected. Using the new implicit selections feature we select the Possible dates in the unconnected table like so: {$Date=P({$} UnconnectedDate)>}. On the unconnected table, the user may have made selections on Year or Month or Day of Week, but we don’t care. We will select whatever UnconnectedDate values remain. By displaying both date tables we maintain visibility for both sets of date selections. Because one date table is connected, all the other fields continue to update. It is best therefore to use the connected date table as the current period and the unconnected table as the past period.

The following paragraph from my first post is not required, but so far in my implementations it has been necessary, either for adequate performance or to calculate at all:

The unconnected table cannot be unconnected. This may be intentional, but QlikView does not seem to be able to make selections in the Date field if the UnconnectedDate field is actually on an unconnected table. The workaround is simple, connect the table into the data model using a dummy link field. All the values on both sides of the link are the number zero, for example.

Hope you got something out of this. Cheers!

Related posts:

1. QlikView 9 Beta!!! There are A TON of new features coming in version...
2. The Year in QlikView A subtle and powerful shift happened last year. I was...

Related posts brought to you by Yet Another Related Posts Plugin.

I exported the blank “New” document to XML. Click here to see what it looks like in XML.

Why add XML export? One of the big-picture features that was missing from QlikView is support for version control. By using XML as the storage format you can use standard version control products and produce accurate differentials.

But now that QlikView has embraced an open file format, there are so many more things we can do…

Track Metadata in Large Deployments

Some QlikView deployments have many HUNDREDS of QVW files. Someone is probably over 1000 by now. These are unique apps; not the result of Publisher distribution loops. It’s a tedious task to manage metadata when it’s locked inside a proprietary file format.

Which apps use the [Salesman] field? Which apps have expressions that fail to multiply [Revenue] by the [Exchange Rate]? Use a tool like Rob Wunderlich’s on the XML versions of all these QVW files and we can track, compare and search hundreds of thousands of expressions, tens of thousands of layout objects, and thousands of scripts as quickly as we would explore any other data in QlikView.

Generate New Applications

Kalido is exporting basic QVWs and QVDs, That feature required custom code integration. Soon any one of us can achieve the same end result: export a complete XML specification, import the XML into QlikView, and hit reload to execute the script. If QlikTech can make this happen from the command-line then the entire process can be hidden and the end user receives a freshly baked QVW (or QVD!).

Dynamically Update Applications

You could generate a full application, but it’s more likely that you will want to make small changes to one that’s already developed.

What do you do when you cannot predict the format of a data update. What if there are new columns added each day? Or what if the headers change? One of the simple things that QlikView can’t handle on its own is the addition or removal of fields from Excel sheets. I don’t know any business intelligence tool that gracefully adapts when a data field is missing.

What if you would like to change the layout every day. Maybe a new field in the Excel sheet needs to be added to a table, or to an expression, or to a drill-down. Maybe you would like to add a chart. Every part of the application is exposed in XML, so you can do anything you want.

Go With The Flow

Speaking more generally about adapting to data… One of the mantras of business intelligence and the database world is “Will the data have this format every time?” If it won’t, no deal. Well, now we can adapt to changing data. We can also deliver dynamic applications that are driven by that data.

And more…

It’s hard to say where this goes but I think it has big potential. Are there any other BI tools that we can look to for inspiration? Do you see yourself using this feature? I’d love to read your ideas!

Related posts:

1. The Year in QlikView A subtle and powerful shift happened last year. I was...

Related posts brought to you by Yet Another Related Posts Plugin.

Click to see the full-size image.

Click for a larger image.

Related posts:

1. QlikView Server 9 Installation Screen Here’s a screenshot from the QV Server Beta 9 installation...

Related posts brought to you by Yet Another Related Posts Plugin.

Related posts:

1. QlikView Server 9 New Look! I’m kicking the tires on this new version and so...

Related posts brought to you by Yet Another Related Posts Plugin.

You can access the beta as a registered customer or partner here: http://www.qlikcommunity.com/qv9beta/

My favorite new features so far:

• iPhone client. Zoom in. Shake to clear. Lots of fun.
• Java client for everyone else, including Blackberry.
• Removal of the 2 billion record limit. Now your record set is limited by RAM and CPU power.
• Dynamic data update. Update data in the QlikView document without a reload through APIs and standard SQL statements. When used on the server, it will PUSH the data out to the clients.
• Export QlikView documents as XML. QlikView documents (no data) can be stored in version control systems. Differentials can be generated. This also means that QlikView documents can be programmatically generated!
• Document chaining. Open one document from another and apply selections. Great for working with segmented QlikView documents when data volumes get very large. Can also be combined with the programmatic QlikView generation in interesting ways!
• Actions and Trigger Actions are replacing macros. Oh please, oh please be the end of macros!!
• Set Analysis can now reference other fields, as in “Select all possible values in Customers based on a Sales last year”.
• Sparklines. Mr. Tufte, your “tiny” contribution is everywhere. Cool.
• Trellis charts. I’m quite proud of this since it was originally my suggestion!! Woo hoo!
• Dynamic background charts. Swap out map backgrounds as selections are made.

Related posts:

1. The Year in QlikView A subtle and powerful shift happened last year. I was...
2. QlikView 9: Export Document Layout to XML One of the new features in QlikView 9 is “Export...
3. What’s “possible” in QlikView 9… QlikView 9 adds a powerful new feature to Set Analysis....

Related posts brought to you by Yet Another Related Posts Plugin.

QlikView’s Rapid Time-to-Implementation Improves BI Value

Related posts:

1. The Year in QlikView A subtle and powerful shift happened last year. I was...

Related posts brought to you by Yet Another Related Posts Plugin.

With competitive offerings in the $10-20k per terabyte, this is an attractive offer and a great way to try before you invest when you have that much data.

I hear Vertica is a screamer, but I can’t imagine getting sub-second results for 3 TB of data on 3 virtualized servers, for the same reasons I gave in my previous post.

Vertica for the Cloud Pricing

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.

The problem is virtualization. Virtualization is valuable to customers and service providers, but it’s also a thief! It adds overhead for the processor, cache and memory–everything that impacts QlikView performance!

The cloud, as in real life, is ever changing. You have no idea how many people are sharing your hardware and what their load will be from second to second. I would bet that nearly all deployed QlikView servers spend most of their time idle and the rest of their time at peak processing power. In the cloud, the goal is to spend as little time as possible idle for which we sacrifice peak processing power. QlikView depends on peak processing power and that type of application will suffer the most in the cloud.

But exactly how much will it suffer? Success in the cloud will need to be measured by the end-user experience. The cost of being in the cloud is vigilant monitoring and smart responses. What’s the right way to monitor the end-user experience in a company that uses OCX vs. AJAX, or is spread out geographically? Will bringing up more servers in the cloud improve response time? Should every QlikView server deliver the same set of apps, or should each app be served by a dynamic set of servers? Similarly, do some apps need sub-second response time while others can wait?

One thing stays the same. If you deploy large QlikView data sets you’re already sensitive to response times and what to consider when designing an app. In the cloud, smaller apps will need to think about costly chart expressions, messy data models and design choices that work fine on dedicated servers.

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.