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
];- Drag a line chart object from the Assets panel onto the content area. Title it
Changes in Rank. - Add Year as a dimension.
- Add Fruit as a dimension.
- Add the following expression and label it as
Sales:sum({<Year={">=$(=MAX(Year)-1)<=$(=MAX(Year))"}>}Sales) - Under Appearance | Colors and legend, switch on Show Legend and click on
. - Next, drag a bar chart onto the content area. and title it as
Deviation. - Add Fruit as a dimension.
- Add the following expression and label it as
Sales Change, current year vs previous.sum({<Year={$(=MAX(Year))}>}Sales)-sum({<Year={$(=MAX(Year)-1)}>}Sales) - Select Horizontal under Appearance | Presentation.
- Under Appearance | Colors and legend, toggle the colors button to uncheck Auto colors and switch on custom colors. Select By dimension and check the Persistent colors button.
- Your graphs will look similar to the following image:
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.