The Magic of Set Analysis – Point In Time Reporting

Update 2014/03/24: We’ve launched an Online Training Center, a place where QlikView users, developers and enthusiasts can share and learn new skills with top-quality QlikView courses. One of the courses is on Set Analysis. If you want to learn more about how to use this powerful functionality, then please look at the course description here and Join us in one of our upcoming live, online sessions!

Point In Time Reporting

I have always believed that Set Analysis’ Raison d’être is to satisfy a basic need in any BI Tool: the ability to perform “Point In Time” Analysis. But, needless to say, it is also amazingly useful for the fulfillment of a bunch of other special needs. In this post, I will cover the specifics of Point In Time Reporting with Set Analysis.

Update 2012/12/13: Barry Harmsen and I have just released our book QlikView 11 for Developers. While it is not free, it does offer a cost-friendly, guided way to learn QlikView and contains an entire chapter about Set Analysis. More information can be found by clicking here.

Step by Step

We have already reviwed Set Analysis Syntax here and the concept of Dynamic Record Sets here. So now, we will combine the acquired knowledge to deliver a real-life application of Set Analysis.

Let’s say the user has the following Selection State: Year = 2010; Quarter = Q4; Month = November. The user is analyzing Sales and needs to compare this period versus the previous Month (October, in this case). How do we accomplish this? This one is simple.

The basic idea is to tell QlikView to assign new value to the field Month. Remember how we did it in the previous post?

Determine the value to be assigned

We can use an aggregation function to determine the value we want to assign to the field Month, for example: Max(Month) – 1. Depending on the way you generated the field Month, it might give you the correct result (10, for October). But if it does not have a numeric representation, meaning it is just a literal value, it will return null.

However, even if it yields a numeric value, you can not assign that value to the field Month because it does not exist as is, we have to assign the exact value, as it exists in our table, to the appropriate field (‘October’ as a literal, to the field Month). So our approach will be to have another field, named [Month (#)] which will contain the numeric representation of the field Month. That way, we can assign the result of the expression Max([Month (#)]) – 1 to the field [Month (#)].

Our preliminar expression

The Set expression to assign the obtained value to the field [Month (#)] would be:

• {$<[Month (#)] = {$(=Max([Month (#)]) – 1)}>}. (Check here if you are having trouble understanding the syntax).

The next step would be to use this set expression with our Aggregation function:

• Sum({$<[Month (#)] = {$(=Max([Month (#)]) – 1)}>} Sales)

But there is a problem! That expression will return zero because the value we are trying to retrieve is actually excluded by the current selection state of the user: The user has specifically selected November, therefore reducing the record set to only the records associated with that value. How do we retrieve a value that is currently excluded?

A few adjustments

The answer is: ignore the user’s selection for that specific field. This is accomplished by assigning a blank value to that field in our set expression:

• {$<[Month (#)] = {$(=Max([Month (#)]) – 1)}, Month = >}

That will first retrieve the record set for all the available months, unless they are excluded by another field selection, and then assign the value we want (10) to the specified field ([Month (#)]), therefore returning at he end the record set for October only. The order of the field assignments in our Set Expression is not of importance. Our final expression is:

• Sum({$<[Month (#)] = {$(=Max([Month (#)]) – 1)}, Month = >} Sales)

Another example

Easy huh? Great! Now, Let’s say the user’s selection State is: Year = 2010; Quarter = Q4; Month = October. Will the above expression return the sales for September? The answer is No. Can you guess why?

Correct! Because the month we want is not only excluded by the field selection for Month, but also by the field selection for Quarter. So our expression should be:

• Sum({$<[Month (#)] = {$(=Max([Month (#)]) – 1)}, Quarter = , Month = >} Sales)

Yet another inconvenient with the expression above.

When developing an application, you have to account for all the alternative selections the end user can make. So now, let’s assume the selection state is as follows: Year = 2010; Month = January; (Nothing selected in the field Quarter, but as we might expect, only one possible value (Q1).

If we want the sales for the previous month (December, 2009) we need to:

1. Ignore both Year and Month selections.
2. Create an expression that works just as the previous one, but with which we can also obtain the number 12 (for december) whenever the selection is January. (The previous expression (Max([Month (#)] – 1)) returns zero in this scenario, which is not useful.
3. Create an expression that returns the value for the previous Year. Simple task.

A direct approach would be to create an If Expression like this: If((Max([Month (#)]) – 1) = 0, 12, Max([Month (#)] – 1)). But I find it very impractical, so here is another approach:

• In the script, assign a consecutive number for each month, a unique ID, one that you can easily play with. I use the following formula:
  • (Year(Date) – 1)  * 12 + Num(Month(Date))

The above formula will for example, assign the value 24120 to December 2009 and 24121 to January 2010, and so on. Moreover, since these numbers are 5-digit long, you can easily find a more memory-friendly way to assign a unique ID to every month using the same concept. Our friend Barry, from qlikfix.com, recommended here to use the AutoNumber function. I second that, only if your calendar table is loading in ascending order based on the date, which is almost always the case.

So, now that we have the consecutive number for month (let’s call it MonthID), we can use this expression to retrieve the value for the previous MonthID that will work for every scencario:

• Max(MonthID) – 1

The final expression

Our final expression will look like:

• Sum({$<MonthID = {$(=Max(MonthID) – 1)}, Year = , Quarter = , Month = >} Sales)

Note that I’m ignoring Selection made on Year, Quarter and Month.

The same concept used in MonthID can be used for the field Quarter. We can easily and seamlessly create a lot of expressions for Point In Time Reporting after we’ve created the Calendar table with at least the following fields: Date, Year, Month, Quarter, MonthID and QuarterID.

More Examples

Now, lets see some common Set Analysis expressions that we can use when required:

• YTD (Year-To-Date) Sales:
  • Sum({$<MonthID = {“<=$(=Max(MonthID))”},
    Year = {$(=Max(Year))},
    Quarter = ,
    Month = >} Sales)
• QTD  (Quarter-To-Date) Sales:
  • Sum({$<MonthID = {“<=$(=Max(MonthID))”},
    QuarterID = {$(=Max(QuarterID))},
    Year = ,
    Quarter = ,
    Month = >} Sales)
• MTD (Month-To-Date) Sales:
  • Sum({$<MonthID = {$(=Max(MonthID))},
    Year = ,
    Quarter = ,
    Month = >} Sales)
• Previous Month Sales:
  • Sum({$<MonthID = {$(=Max(MonthID) – 1)},
    Year = ,
    Quarter = ,
    Month = >} Sales)
• Previous Quarter Sales:
  • Sum({$<QuarterID = {$(=Max(QuarterID) – 1)},
    Year = ,
    Quarter = ,
    Month = >} Sales)
• Sales for the same Month but Previous Year:
  • Sum({$<MonthID = {$(=Max(MonthID) – 12)},
    Year = ,
    Quarter = ,
    Month = >} Sales)
• Sales for same Quarter of the Previous Year:
  • Sum({$<QuarterID = {$(=Max(QuarterID) – 4)},
    Year = ,
    Quarter = ,
    Month = >} Sales)
• YTD Sales for Previous Year
  • Sum({$<MonthID = {“<=$(=Max(MonthID) – 12)”},
    Year = {$(=Max(Year) – 1)},
    Quarter = ,
    Month = >} Sales)
• Sales for Rolling 12 Months:
  • Sum({$<MonthID = {“>=$(=Max(MonthID) – 11)<=$(=Max(MonthID))”},
    Year = ,
    Quarter = ,
    Month = >} Sales)

So, there you have it, a complete set of formulas to help you create Point In Time Analysis in your QlikView Applications in a very simple fashion.

I hope you have enjoyed this Part IV of the Series ‘The Magic Of Set Analysis’. Next post: Create your Point In Time Reporting Set expressions once and reuse them in every new QlikView app.

As always, comments and feedback are welcome.

Mike.