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.