Dealing with Set Analysis can be such a pain, especially if you’re not so familiar with syntax or if you’re new to QlikView. However, there’s a simple but useful tip to debug formulas that contain Set Analysis.
Suppose the following requirement:
If you’re familiar with Set Analysis, then by now you’ve realized that it can be as easy as this:
but sometimes (ok, maybe most of the times), client’s requirements need something more complex, like using variables and date parameters:
Fortunately, there’s a way to debug your formulas, instead of “blind coding”:
In the example used for this post I need:
- A formula for customers who bought footwear but not clothing during current month
- A formula for customers who bought footwear but not clothing during past month
- Find customers who bought footwear in both the past and current month
Simply by creating a straight table with no dimensions and omitting labels for expressions, you can debug your formulas. Just by putting mouse pointer over expression label (which in this case is the expression itself) you’ll see the complete formula but with evaluated variables/functions. In this case I use a variable called vToday in order to store the function Max(OrderDate). So no matter which date is selected, formulas will return customers corresponding to month selected, and for past month. In this example you’ll see that instead of appearing the name of the variable, it will appear the parameter interval of dates that i’m interested in.
To double check that the formula is correct, I create a straight table with details of quantity bought for current and past month:
Looking at the picture, there is only 1 customer that bought footwear but not clothing in both the past and current month.
It’s true that Set Analysis Syntax isn’t the most friendly thing, however tips like these are always welcome.
The complete example can be dowloaded here
Stay tuned for future posts
Evolution hints 