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.
Related posts:
Comments
Leave a comment Trackback