How is this month looking compared to the last? This is one of the most common questions asked in BI solutions. In this recipe, we will build two charts and both will compare one year to the other. The first chart expression will limit the range of data and make use of the Year dimension. The second chart will not use the Year dimension but will build the year comparison directly into the expression itself.
For the purpose of this recipe,we will make use of an inline data load which gives yearly sales information for different fruits. Load the following data into the data load editor:
Data: LOAD * INLINE [ Fruit, Year, Sales Apple, 2013, 63 Apple, 2014, 4 Cherry, 2014, 1150 Cherry, 2013, 1180 Fig, 2013, 467 Fig, 2013, 374 Fig, 2014, 162 Fig, 2014, 267 Fig, 2014, 586 Orange, 2013, 10 Orange, 2013, 50 Orange, 2013, 62 Orange, 2013, 131 Orange, 2013, 145 Orange, 2014, 93 Orange, 2014, 102 Pear, 2013, 27 Pear, 2013, 157 Pear, 2013, 384 Pear, 2014, 489 Pear, 2014, 782 Plum, 2013, 148 Plum, 2014, 36 Plum, 2014, 412 Plum, 2012, 700 ];
Changes in Rank
.Sales
:sum({<Year={">=$(=MAX(Year)-1)<=$(=MAX(Year))"}>}Sales)
Deviation
.Sales Change, current year vs previous
.sum({<Year={$(=MAX(Year))}>}Sales)-sum({<Year={$(=MAX(Year)-1)}>}Sales)
The first Set Analysis expression makes use of a search string; thus, defining the set of records we want to calculate across. A pseudo code will read like this.
Sum where the Year = {"Search for records that fulfill a particular requirement "}
Using the double quotes denotes that we will be doing a search starting with <
or >
. Only values that fulfill the numeric requirement will be matched.
In our example, we define the numeric requirement of the search string dynamically using the following code:
={">=$(=MAX(Year)-1)<=$(=MAX(Year))
This code evaluates the max year and the year previous to that. If we changed the -1
in the preceding code to -2
the calculation will cover three years and not just two; this is the benefit of using search strings in Set Analysis. For the second chart, we have not used a search string but specified literals. We have kept the dynamic part of the expression as:
{$(=MAX(Year))}
Now, the max year available will be picked up automatically as opposed to saying Year={2015}
and updating the expression next year.