Tip: Debugging Set Analysis

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:

Escenario

If you’re familiar with Set Analysis, then by now you’ve realized that it can be as easy as this:

Basic Set analysis

but sometimes (ok, maybe most of the times), client’s requirements need something more complex, like using variables and date parameters:

Complex Set Analysis

Fortunately, there’s a way to debug your formulas, instead of “blind coding”:

DebuggingSetAnalysis6In 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:

Quantity bought 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

jaime.aguilar@evolcon.com

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s