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