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 2019 to those in 2018 and 2017.

 

Select d.showDate, a17.Sales as '2017', a18.Sales as '2018', a19.Sales as '2019'

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`) = '2017'
Group by 2) a17
ON d.Date = a17.Date

LEFT JOIN

(Select COUNT(`Sales`.`SalesID`) as Sales, DATE_FORMAT(`Sales`.`Date`, '%m') as Date
From `Sales`
Where Year(`Sales`.`Date`) = '2018'
Group by 2) a18

ON d.Date = a18.Date

LEFT JOIN

(Select COUNT(`Sales`.`SalesID`) as Sales, DATE_FORMAT(`Sales`.`Date`, '%m') as Date
From `Sales`
Where Year(`Sales`.`Date`) = '2019'
Group by 2) a19

ON d.Date = a19.Date

 

Running this query in datapine and selecting the chart type ‘Line Chart’ will create a chart like this:This script we have created sub-selects for each of the years and simply joined the individual result sets on the date.

 

How to compare different time periods using the SQL box

 

 

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.