A common request from customers is to ask about historic crime trends. They usually want to know something like “How many break and enters have we had in District Y since 2010?” and what they likely want to do is get a sense of how things are moving; are things trending up and should they start a project or are they trending down and they can calm the nerves of a local council person.
Getting the data is usually straightforward and the most helpful presentation of the numbers is a chart but there’s always the wrinkle of how to show this year’s data compared to previous years. Unless the customer is asking for the data on the day after New Year’s the count for the current year is going to be Year-to-Date (YTD) only and comparing this YTD numbers to previous years is a bit complicated, especially once the chart leaves your hands and they start sharing it around. Having run into this request many times I’ve developed the following visualization method that uses a stacked column chart to present YTD data in an intuitive, readable format.
In the example spreadsheet I’ve created for this post I’ve generated some random reported dates dating back to 2007. In columns B and C I’ve broken out the reported year and month for the incident using Excel’s built in YEAR() and MONTH() functions. Real life data will likely have a lot more columns but these are the only ones you really need to make this technique work.
Next I create a pivot table with the data and I drag the “Reported” field from column A into the Values area and the “Reported Year” field into the Rows area. This creates a standard count-by-year table that I can copy over to another worksheet to prepare my chart.
This data covers the full year but because the data I am working with was generated in August of 2016 I want to be able to pull out the January through August data from the previous years for the YTD comparison. To do that I drag the “Reported Month” field into the Filters area and select only months 1 through 8 in the Reported Month filter.
The resulting list of values represents the YTD values for each year and I copy those over to my working sheet. I organize the data in a table with columns for the year, the total for the year and the January through August values. I then create one additional column labelled “Sep – Dec” and subtract the “Jan – Aug” count from the annual total to fill this column. The final table looks like this:
With the data in place we can now chart. Select the last two columns of data and then insert a stacked column chart. You may need to fiddle a bit with the settings (you’ll want to add the year labels in for each column) but what you’ll see is a chart that simultaneously visualizes both the YTD values and the overall annual values.
In my sample chart I’ve selected strongly contrasting colours to aid the YTD comparison. By looking at just the blue portions of the columns it’s possible to see that 2016 has been a good year so far for this kind of incident. We’re down significantly from last year at this time and actually at the lowest YTD levels we’ve seen since 2009. If we consider the orange column we can see that besides 2013 and 2015 the number of incidents have been fairly steady and that 2016 is likely going to come in as a little below average.
Charting historic crime volume is an everyday request for most analysts. By leveraging the convenience of stacked column charts you can create a single visual that helps understand both annual and YTD trends.