Hello and Happy New Year! Today we continue with ‘The Magic of Set Analysis’ Series and, in this post, I will share with you a “goodie”, something that has helped me speed up the Development process on new projects, and is specially useful when creating SiB’s (Seeing Is Believing) or Prototypes for prospective clients, where the time is often short. Point In Time Reporting has always been a must-have in every App I develop and, at first, the adaptation for every new application took me precious time to get it set, often reviewing the expressions I had previously used in other apps to adapt them to the one in hand. Then I realized it is basically, sometimes exactly, the same no matter the requirements, no matter the Client’s Line Of Business, it always came down to the same Set expressions. So, I made a “portable”, reusable script that helps me in every new project. It is contained in a text file, and I call it using the include statement in the QlikView Script. That’s it! One line of script and I have everything I need to start creating the Charts and Point In Time Analyses. How it works:
- It creates a Master Calendar, based on two variables you define: Start Date and End Date. This Table will populate some necessary fields to make the next part work.
- It creates a few variables (nine), that I call “Set Variables”, and that are used to build the expressions in our charts.
Once we run the script, we can start building charts with simple expressions like:
- Sum($(vSetYTD) Sales) – This will render the Sales Year To Date.
- (Sum($(vSetYTD) Sales) / Sum($(vSetPreviousYearYTD) Sales)) – 1 – This will return the YTD growth compared to Previous Year.
As you can see, it is very straightforward. The Set portion of the Expression is stored in a variable, and that conveniently help us if we need to modify something in the expression: we change it in the variable itself and will be applied to all the other expressions using it. Things to keep in mind:
- If you don’t declare your initialization variables (vDateMin and vDateMax), a Message Box will warn you and then the execution will end.
- You need to review and understand clearly what the expression is doing before using it on your apps. It is intended to be simple though. If you are having trouble understanding them, please review my previous post.
- You can always adapt it to your needs.
Please download the following file, that contains a qvw with a Demonstration of how the script works and the text file itself. Also there is a sample CSV with Sales Data so you can reload the Demo qvw.
Please leave your comments/suggestions in the area below. I appreciate your feedback. Finally, I wish you all the Greatest Success in 2011! Happy Qliking.
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. Mike.
Update 2019/04/25: If you found this useful, you may want to take a look at the Set Analysis Cheat Sheet and invaluable resource for Qlik Developers.