Chapter 11. Set Analysis and Point In Time Reporting
Comparing performance metrics over a period of time is one of the most fundamental tasks expected from any BI solution. There are a few ways to deliver these sort of comparisons in QlikView, but the most flexible and dynamic ones involve the use of Set Analysis. Set Analysis, by itself, is a powerful tool that can be used for not only for Point In Time Reporting, but for many other complex calculations.
In this chapter, we will expand on what we've learned from all of the previous chapters, and introduce the following new concepts:
We will approach these topics with the use of some practical examples. Given the harshness of the syntax we are going to use, and the complexity of the expressions that can be built with Set Analysis, we recommend you to have a lot of patience and dedication to the subject at hand. Even with the best reference...
The magic of Set Analysis
We will now introduce one of the most powerful tools you, as a developer, have at your disposal when creating QlikView documents.
We must say upfront that, as with anything, the excessive use of Set Analysis in chart expressions can yield poor performance or low response times. However, we should also know that, when used effectively, it can have a positive impact in both performance and user experience.
In this section, we will cover topics such as when to use Set Analysis, why you should use it, what the correct syntax is, and we will provide common examples and several tips and tricks for maximizing performance as well.
Set Analysis is a great feature in QlikView that lets you, as a developer, take control over what your charts display and allows calculations that wouldn't be possible otherwise, at least not as dynamically. To understand its inner workings, we can compare it to how selections that are made using listboxes work.
With UI selections...
One of the most common use cases of Set Analysis is
Point In Time Reporting. Having the ability to perform period-over-period analysis is a basic requirement in any BI tool and is easily performed in QlikView with the aid of set expressions. However, needless to say, Set Analysis is also amazingly useful for the fulfillment of other special requirements.
Let's combine the acquired knowledge and apply it to add Point In Time Reporting to our Airline Operations document.
HighCloud Airlines' executives require a dashboard to easily compare different performance indicators in a period-over-period basis. The different period comparisons they need are:
Current Year-To-Date indicators versus the same period last year
Current month versus same month last year
Current month versus previous month
The comparisons should be dynamic and based on the user's selections. So, if the user selects October 2010, the corresponding comparisons should be:
Comparative analysis with alternate states
In addition to time-based comparisons, there are other scenarios in which the comparison of two different sets of data can help enhance the analytical capabilities of a QlikView document. In this section, we will present a feature available in QlikView since version 11 which makes it easy to create highly dynamic comparative scenarios that enables business discovery in an entirely new way.
A comparative analysis example
Let's discuss one of these scenarios by using our Airline Operations document. Suppose we want to compare how the number of international flights arriving at the city of Chicago, IL, and performed by US carriers, compares to the number of domestic flights departing from Chicago and bound to the State of California, performed by US Carriers as well. If we were to see this comparison in a bar chart and over time, we would have the following:
In the preceding example, the total flights in Group A (represented by the blue bars) correspond...
Set expressions and alternate states can can get very complex, so it's a good practice to always validate the results thoroughly. Here are a few ways in which we can do that:
When using calculations in the element list definition, take the individual calculation out of the set expression and enter it into a text object to visualize its result and ensure that it's what you expect.
Once the set expression or state, has been constructed, play around and make a few selections to see how each affects the calculation and ensure it's what the user would expect.
When you've arrived at a result using a set expression, replicate the base expression, without the set portion, and try to arrive at the same result using only selections. If everything is correct, the result should always be the same, unless of course the modified record set cannot be replicated using selections only.
We've come to the end of this chapter in which we've been able to use some advanced expression techniques and Set Analysis to build period-over-period comparisons. We have learned the syntax and variations of a set expression.
We also learned how to construct and use Set Analysis expressions to enable time period comparisons, and also how to use some time-saving techniques to re-use set expressions across different documents.
Finally, we learned how to use alternate states for comparative analysis.
Let's now move on to the next chapter, which expands on the concept of data transformation at an advanced level.
