One common question we see a lot is how to compare and analyze data from one year to the next. There are a variety of ways to do this. Let’s assume we have data like shown below, which includes transactional data of sales by sales rep and by region for 2011 and 2012.
If we want to analyze the results per region, for example, we could create a Bar Chart which shows Region on the Category-axis, and Sum ([Total]) on the Value-axis. We can additionally color by Year([Order Date]).
You will notice that the sales are all less in 2012, in some places significantly less. If we take a look at the raw data, we can see it is because the results for 2011 are complete, but at this given point in time, we are still in 2012, so we don’t have data through the end of the year.
What if we want to compare how we are doing so far this year compared to the same time period last year? Using a simple calculation, we can build a calculated column, called ‘Adjusted YTD’ which will show data only if the day of the year for 2011 is before today’s date in 2012.
if(DayOfYear([OrderDate])<=DayOfYear(DateTimeNow()),[Total],0)
If today is October 14, 2012, it will output sales totals for all rows in 2011 where the date is October 14,2011 or earlier, but for October 15th,2011, for example, it will output 0.
We can then update our Bar Chart to use the Adjusted YTD Calculated Column on the Value-axis, so that we can properly show an Actual YTD comparison.
If we now want to show the just a single bar which shows the difference between the Adjusted YTD this year versus last year, we can use the following custom expression on the Value-axis:
Sum([Adjusted YTD]) - Sum([Adjusted YTD]) OVER (Previous([Axis.Color]))
Interested in learning more about calculations and custom expressions in order to generate your own analysis? Please consider taking our SP141 Computational Analytics training course. This is offered onsite with a live instructor, and online, using our new Mentored Online Training format.
