… and points beyond

A subtle and powerful shift happened last year.

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.

Download it from http://ftp.qliktech.com/qvwebdownloads/

My thanks to Henric Cronström for taking the time to review my post on expression variables. He said the name for this new feature is still being decided. And he had some valuable information for getting started.

Your text is good and everything is correct. But you may want to add the following

1) The “all” qualifier is removed from the documentation and is replaced by “{1} total” which does exactly the same. “all” will still work in formulas in documents, though.

2) The syntax {$<Year=2002>} replaces the list of selected values with “2002″.

3) The syntax {$<Year+=2002>} adds “2002″ to the list of selected values.

4) The syntax {$<Year+=-2002>} removes “2002″ from the list of selected values.

5) The syntax {$<-Year+=2002-2001>} adds “2002″ and removes “2001″ to the list of excluded values. (Forced exclusion – see And-mode or red selection in attached app.)

Cheers

You can download the application here.

One of the more interesting features coming in QlikView 8.5 is “Dollar-Sign Expansion with an Expression”, Section 24.5 in the coming Reference Manual. One place in the manual they are called “on-the-fly variables”, but I prefer “expression variables”.

In many ways expression variables are similar to using the TOTAL qualifier because they are calculated across the set of selections and are not affected by a dimension value in a chart. If you use the ALL qualifier inside an expression variable, it looks at the entire dataset regardless of selections, as ALL normally does. For day-to-day use as part of set-analysis this is all you need to know.

For the most part, you can get the same results by using  the TOTAL or ALL qualifier. What makes expression variables different is that they are optimized for speed by being evaluated only when necessary. This is demonstrated with the expression variable $(=rand()). It is evaluated once and, as far as I can tell, will remain unchanged through reloads, saving and reopening. If you aren’t familiar with rand(), it generates a random decimal number between 0 and 1. Anytime QlikView reevaluates the expression, the value will change and we can clearly see it.

New caching algorithms make expression variables very efficient. Expressions that refer to field names are evaluated for each combination of selections–only as a new combination is queried–and the results are cached. You can see this behavior with the expression $(=Year+rand()). There are additional optimizations so that expressions that do not change are not reevaluated. For example, $(=max(ALL Year)+rand()) is evaluated once because max(ALL Year) will never change.

QlikView organizes the caching scheme by the string value of the expression. $(=rand()) and $(=   rand()) will have different values but both will maintain their values throughout the application. As another example, $(=max({1} Year)+rand()) and $(=max(ALL Year)+rand()) contain an equivalent “max” expression, but yield a different value.

By design, expression variables always give the same result for each row. This is half the reason why set analysis is so much faster than Sum-If logic. The other half is that QlikView compares the expression variable to the whole field contents and not another calculated value. As a result, this reduces the problem of set analysis to making a parallel set of selections, which is what QlikView does best.  On the other hand, an If statement can refer to the current row, and it can be affected by the dimensions for that row, so Sum-If logic must recalculate for each and every row.

This is my own analysis from watching the Beta software. Behavior may change for the final release and I might have misunderstood what’s happening inside the application. I will continue to post what I learn about expression variables. If you can confirm or disprove my analysis, or if you have an interesting use for expression variables, tell me about it.

It is still in development as of the beta. Looks nice.

There needs to be a way to say “apply current selections, but ignore any selections in field A”. I submitted this as a feature request, but I hope someone from QlikTech sees this post. My proposed syntax is sum({$<!Year><!Month><!Week><!Date>} Sales). Otherwise, you need to use a positive assertion that lists each and every value, or calculates it on the fly. See my previous post and the use of concat() within a variable definition.

I guess I’ll eat those words. It seems, though I only have the beta and it may change, that to ignore selections in a given field, you can override the field value with an impossible value. For example: sum({$<Year=-1>} Sales). So far this behavior is consistent and correct.

UPDATE: The syntax in QlikView has changed since this was written at the time of the 8.5 beta release.

“Set analysis is the best feature since the new charting engine in version 4,” says Jonas Nachmanson.

That may not reveal the excitement, but it’s true! Set analysis is a big feature.

Want to sum excluded values that are not a part of current selections? Want a daily sales report that doesn’t break if someone makes a conflicting data selection? Then set analysis is for you! Now you can calculate across any slice of the dataset, regardless of what fields and values are currently selected.

You have fine-grained control over the selections that apply to any expression. Add, change, eliminate, invert or tweak any selections. This is all without the speed penalty of Sum-If logic, which can only see its intersection with current selections and therefore breaks when any conflicting selection is made. The result is faster calculation, predictable dynamic behavior, better support for dynamic graphs and tables, and faster implementation for some very frequent requests.

sum(ALL Sales)-sum(Sales) could be rewritten with set analysis as sum({1} Sales)-sum(Sales) but should be written as sum({1-$} Sales). {1} means the entire data model. {$} means the current selections. {0} means no selections at all.

1) Apply current selections, but act as if the current year is always set to the variable vCurrentYear: sum({$<Year=$(vCurrentYear)>} Sales)

2) Apply current selections, but ignore any selections in the Year field: sum({$<Year=$(=concat(ALL DISTINCT Year,’+'))>} Sales).

I stumbled on a simple way to make QlikView ignore selections in a field. Override the field selection with an impossible value: sum({$<Year=-1>} Sales).

The syntax $(=expression) is also new to 8.5. It adds dynamic variable creation. I plan to cover ideas for this new feature in a future post.

Rather than using concat(), I could have preset a variable in the script. But I want to bring up the point that the example above is pretty kludgy. There should be simpler syntax to say “ignore any selections in the Year field”. If what I wrote is the best option, then I hope that the dynamic variable is expanded once per layout object and not once for each row.

3) Sum sales for the prior year, with the prior year being relative to what is currently selected: sum( {$<Year=$(=Only(Year)-1)>} Sales ). No more clearing dates with macros! (More on the evil of macros coming soon.)

Set analysis immediately sparked some new ideas for interacting with QlikView and I think we’ll see many more. For example, the combination of set analysis using bookmarks and input fields improves QlikView as a financial forecasting tool, the frequent first question from CFOs. In future posts I’ll discuss using bookmarks with set analysis for side-by-side comparison, quantify the speed advantage over Sum-If, demo the new way to create dynamic charts, discuss the benefits for dashboard design, and much more.

Working with variables can be confusing, so I created a demo with 3 variables: a string, a number and a valid function (as a string). It’s worth nothing that the upcoming 8.5 treats sum-if differently than 8.2

Here’s 8.2:

And here is 8.5: