Authored by Mark Harrington - Consultant - Wipfli's Microsoft Dynamics CRM Team
Welcome to another installment of our “It’s Technical” series of QlikThoughts. As a QlikView developer, a common requirement that I encounter on projects is the need to compare the data from the current year against the same or related data from the same period last year. As is the case with many QlikView features, there are many ways to satisfy this requirement. In the following posting, I will give details on one method that I have found to provide both versatility and great ease of use for the end user.
In the sample data I used for this chart, 2008 is the Current Year, and 2007 is the Prior Year. For the purposes of the chart below, I have used the “Month” field as my dimension. Here is a look at the Sales Trends chart depicting 2008 Costs against 2007 Costs.
Screenshot of Sales Trends Chart:
Take note that I have selected the second Cycle button on the Chart to show the different Expression options that are present in each Cycle Group. This will allow a user to quickly change which expression is plotted on the Chart so that they can compare 2008 Sales vs. 2007 Unbilled Sales, 2008 Sales vs. 2007 Costs, or any other metrics you have defined between the current and prior years.
Now that you have seen a glimpse of what the final chart will look like, let’s take a look at the Chart properties to get an understanding of the formulas at work behind the chart object.
Screenshot of Sales Trends Chart Properties:
Example Chart Expression for first Cycle Group:
Sum({$<Year={$(=Max(Year))}>} Sales)
Example Chart Expression for second Cycle Group:
Sum({$<Year={$(=Max(Year)-1)}>} Sales)
The first thing that you should note is that there are two different groupings of Expressions in the Expression builder window. The first group is our Cycle Group for the Current Year expressions, and the second group contains the same expressions, but for the Prior Year. Each expression formula contains Set Analysis to display only data from the Current Year regardless of any selections that a user may have made in a “Year” list box.
This posting is intended to give a general overview on how to combine Set Analysis formulas and Cycled Expressions in a chart object in QlikView. There are many other scenarios that I can think of where this type of functionality could be deployed and used. If you have any questions regarding this posting, please post a comment to the blog. It’s always great to hear feedback from our readers.
-Mark