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.

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 empty set. For example: sum({$<Year=>} Sales).

This post has been edited since being published to show the Set Analysis syntax as of QV 9.

“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=>} 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.

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!

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.

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: