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.
Related posts: