Introduction

This article shows you how to create a year-over-year (YOY) comparison chart, also sometimes called an index-year-ago (IYA) chart.  A year-over-year chart looks at values over a time period, such as quarterly sales totals, and compares them to the same time period from the previous year.  All you need is a dataset with values recorded over time and Spotfire will do all the aggregating and comparing for you.

First I will show a simple way to visually compare time periods in different years.  Then I will illustrate a more advanced method for doing this using custom expressions where the resulting chart shows the specific computed difference or percentage difference from year to year.

A Simple Way

Starting with a dataset like the one in the illustration, I created a bar chart and set the Y-axis to Sum([Sales Total]), and then set two X-axis elements for the Year and the Quarter components of the [Order Date] field.  Note that Spotfire specially recognizes Date and Date/Time columns and allows you to pick and choose the level of detail you wish to plot.

 At this point, an easy way to get a YOY comparison is to simply change the order of those x-axis selectors, which changes the way the values are grouped.  Drag the "Quarter" selector over to the left of the "Year", and voilà, now you have a YOY chart.  All of the values for a certain quarter are right next to each other and ordered by Year, making them easy to compare visually.

Coloring by quarter will make this even easier to read.  Also, feel free to introduce other variables into the plot, such as a vertical trellis by [Region].  Both these changes are shown below.

A More Advanced Way

In the simple example above we showed the YOY values side by side.  But you may want to illustrate the computed difference, or percentage difference, between time periods from year to year.  This section describes how to do your year-over-year comparison as percentage difference using Custom Expressions.

Using the same basic dataset format shown above, a good start is to create your trend of the value you care about.  It is important to have your Year field as the first item on the X-axis, and then your second X-axis selection can be the sub-period that you care about, whether it is quarters, months, weeks, or anything.  Here is the plot I am going to use for this example:

Now, right-click the y-axis selector and select Custom Expression.  Enter the following statement:

(Sum([Sales Total]) / Sum([Sales Total]) OVER (ParallelPeriod([Axis.X]))) - 1 AS [YOY]

Click Ok and check out your YOY ratio plot.  Since the value returned is a ratio, a more helpful display format for the Y-axis would be as a percentage.  Just right click the Y-axis label region and select Formatting > Percentage.  You should see something like this:

So what is this plot telling us?  In this example, Q4 of 2002 is the lowest bar, and it tells us that sales totals in Q4 of 2002 were more than 40% lower than Q4 in 2001.  Looking at the tallest bar, we see that sales in Q4 of 2004 were more than 40% greater than in Q4 of 2003.  And if you are wondering why 2001 looks so flat, it is because there is no data for the prior year (2000) to compare with.

The key here was to use the ParallelPeriod() node navigation method, which is part of the Custom Expressions grammar in Spotfire.  You can read more about Custom Expressions here on the Spotfire Technology Network.