Documentation

Compare Different Periods – Time Chart

When monitoring KPIs over time, it might be helpful to compare these metrics to different periods of time. This can not only help you in seeing how your business is scaling, but also how seasonal effects might be impacting your numbers. Showing a trend over time can tell a story (increase, decrease, stability or not), but having something to compare your trend to is even more effective as sometimes the range of time chosen do not tell the viewer anything.

 

With the SQL Box in datapine comparing totals or timely figures with other periods of time or even defined thresholds has never been easier. Using our demo database, you can use the following example on how to compare different periods in datapine.

 

Let us take an example to illustrate that using our demo database. For instance, you want to visualize your sales numbers over the course of the year: it might indeed be helpful to see how you were performing last year at the same time or maybe even the year before. Try the following script in datapine to compare the monthly sales of 2016 to those in 2015 and 2014.

 

Select d.showDate, a14.Sales as ’2014′, a15.Sales as ’2015′, a16.Sales as ’2016′

 

From

 

(Select DATE_FORMAT(`Sales`.`Date`, ‘%m’) as Date, DATE_FORMAT(`Sales`.`Date`, ‘%b’) as showDate

From `Sales`

Group by 1) d

 

LEFT JOIN

 

(Select COUNT(`Sales`.`SalesID`) as Sales, DATE_FORMAT(`Sales`.`Date`, ‘%m’) as Date

From `Sales`

Where Year(`Sales`.`Date`) = ’2014′

Group by 2) a14

 

ON d.Date = a14.Date

 

LEFT JOIN

 

(Select COUNT(`Sales`.`SalesID`) as Sales, DATE_FORMAT(`Sales`.`Date`, ‘%m’) as Date

From `Sales`

Where Year(`Sales`.`Date`) = ’2015′

Group by 2) a15

 

ON d.Date = a15.Date

 

LEFT JOIN

 

(Select COUNT(`Sales`.`SalesID`) as Sales, DATE_FORMAT(`Sales`.`Date`, ‘%m’) as Date

From `Sales`

Where Year(`Sales`.`Date`) = ’2016′

Group by 2) a16

 

ON d.Date = a16.Date

 

This script we have created sub-selects for each of the years and simply joined the individual result sets on the date.

Running this query in datapine and selecting the chart type ‘Line Chart’ will create a chart like this:

 

line chart comparing 2 different time periods

 

Note: This script is rather static and can’t be used in combination with the time filter on the dashboards. Please see the next guide on how to compare to a previous time period to enable the time filter for comparative time charts.