… and points beyond

The positions have been filled so I have removed this post.

QlikView 9 adds a powerful new feature to Set Analysis. In 8.5, you must specify the values to be selected in a field. But how can we dynamically make selections based on another field? QlikView 9 introduces “Set Modifiers with Implicit Field Value Definitions” in section 19.4 of the reference manual. Here’s an example:

sum( {$ Customer = P({1<Product={‘Shoe’}>} Customer)>} Sales )

The expression inside P(..) is saying “Start with the complete data set and select the value “Shoe” in the Product field. The list of customers after making that selection is what I want to use to calculate sum(Sales).” Ok, that’s great. It’s dynamically generated based on selections in another field. Now we don’t need a crazy concat() expression to acheive this like we did in 8.5. And by using E(..) instead of P(..), it’s now possible for the first time to select the excluded values! That wasn’t even possible in 8.5!

But the real power is the second Customer. You do not need to return a list of values from Customer in order to make a selection on Customer.

So what? My favorite application of this new ability is in using multiple calendar tables. Let’s say you want to look at sales between two different calendar periods…

• In the pre-8.5 method, without set analysis, you were limited to sum-if logic. In order to pick two dates and calculate over two periods, both calendars needed to be unconnected from the data model. Because these tables are unconnected, other list boxes would not update as the user made date selections.
• The 8.5 approach to multi-time-period analysis has been to use bookmarks and macros. You make one period selection and bookmark it, then make the next selection. One calculation applies the bookmark, the other does not. List boxes for other dimensions continue to update as new selections are made. The user cannot see the bookmarked date selections again without applying the bookmark. The bookmark can also contain additional, unintended selections. The way to avoid this is with macro code that makes selections, which is BAD BAD BAD because it kills the server selections cache.
• THE NEW  QlikView 9 way is to have two calendar tables in the data model, one connected and one unconnected. Using the new implicit selections feature we select the Possible dates in the unconnected table like so: {$Date=P({$} UnconnectedDate)>}. On the unconnected table, the user may have made selections on Year or Month or Day of Week, but we don’t care. We will select whatever UnconnectedDate values remain. By displaying both date tables we maintain visibility for both sets of date selections. Because one date table is connected, all the other fields continue to update. It is best therefore to use the connected date table as the current period and the unconnected table as the past period.

The following paragraph from my first post is not required, but so far in my implementations it has been necessary, either for adequate performance or to calculate at all:

The unconnected table cannot be unconnected. This may be intentional, but QlikView does not seem to be able to make selections in the Date field if the UnconnectedDate field is actually on an unconnected table. The workaround is simple, connect the table into the data model using a dummy link field. All the values on both sides of the link are the number zero, for example.

Hope you got something out of this. Cheers!