Documentation

Compare To Previous Period

A great way to add more information to your SQL-based charts is to include the previous period which automatically adjusts to the time filter selection on the dashboard. Let’s assume you want to have a look at the current Year’s sales performance and compare it to the respective previous Year’s performance.  Therefore, just select your current time period such as this Year in the time filter on the dashboard and the SQL script indicated below will do the rest to adjust the previous period.

 

If you wish to use this functionality in datapine, you will need to include a specific date range in the ‘Where’ clauses to make it work. Using our demo database, the following scripts will demonstrate how to use the filterable comparative periods in your SQL query by comparing the currently selected period to the same period one year before.

 

Compare to previous period in a time scale chart

 

Select d.showDate, b.Sales as 'Previous Period', a.Sales as 'Current Period'

From

(Select DATE_FORMAT(`Sales`.`Date`, '%Y %m') as Date, DATE_FORMAT(`Sales`.`Date`, '%b') as showDate
From `Sales`
Where DATE(`Sales`.`Date`) between DATE(TIMESTAMP'2016-01-01 00:00:00.0') 
AND DATE(TIMESTAMP'2016-12-31 23:59:59.999')
Group by 1) d

LEFT JOIN

(Select COUNT(`Sales`.`SalesID`) as Sales, DATE_FORMAT(`Sales`.`Date`, '%Y %m') as Date
From `Sales`
Where DATE(`Sales`.`Date`) between DATE(TIMESTAMP'2016-01-01 00:00:00.0') 
AND DATE(TIMESTAMP'2016-12-31 23:59:59.999')
Group by 2) a

ON d.Date = a.Date

LEFT JOIN
(Select COUNT(`Sales`.`SalesID`) as Sales, DATE_FORMAT(`Sales`.`Date`, '%Y %m') as Date, 
DATE_FORMAT(DATE_ADD(`Sales`.`Date`, INTERVAL 1 YEAR), '%Y %m') as JOINDate
From `Sales`
Where DATE(`Sales`.`Date`) between DATE_ADD(TIMESTAMP'2016-01-01 00:00:00.0', Interval -1 YEAR) 
AND DATE_ADD(TIMESTAMP'2016-12-31 23:59:59.999', Interval -1 YEAR)
Group by 2) b

ON d.Date = b.JOINDate
Group by d.Date
Order by d.Date;

 
In the second sub-query, as a default setting, we’ve set the date to be between the first and last day of 2016. Using the time filter on the dashboard will overwrite this range and set it to any range you wish.

 

Where DATE(`Sales`.`Date`) between DATE(TIMESTAMP'2016-01-01 00:00:00.0') AND DATE(TIMESTAMP'2016-12-31 23:59:59.999')

 
In the third sub-query, we’ve included the same time range, except for deducting one year to make it the corresponding previous period of whatever will be selected in the time filter.

 

Where DATE(`Sales`.`Date`) between DATE_ADD(TIMESTAMP'2016-01-01 00:00:00.0', Interval -1 YEAR) AND DATE_ADD(TIMESTAMP'2016-12-31 23:59:59.999', Interval -1 YEAR)

 

Adding this format to your SQL query will allow you to apply the date filtering on the dashboard.

Illustrating how to compare numbers to previous period

Compare to previous period in a number chart

 

A nice feature to visualize trends and developments in your data is the trend indicator for number charts. However, if you wish to use this function for one of your SQL queries you will need to adjust it to the following format:

 

Select 'current', COUNT(`Sales`.`SalesID`) as Sales
From `Sales`
Where DATE(`Sales`.`Date`) between DATE(TIMESTAMP'2016-01-01 00:00:00.0') AND DATE(TIMESTAMP'2016-06-30 23:59:59.999')

UNION ALL

Select 'previous', COUNT(`Sales`.`SalesID`) as Sales
From `Sales`
Where DATE(`Sales`.`Date`) between DATE_ADD(TIMESTAMP'2016-01-01 00:00:00.0', Interval -1 YEAR) AND DATE_ADD(TIMESTAMP'2016-06-30 23:59:59.999', Interval -1 YEAR)

For each of the periods that you wish to compare you will need to create an individual SELECT Statement and combine these with the UNION operator. Please also specify, which SELECT statements is the current period and which one is the previous period by including these labels in the statements. The previous period thereby builds the basis on which the trend indicator will be calculated.

Running our example statement on our demo database will show the following result in datapine.

 

compare results with previous period with a number chart

 

To enable the trend indicator for number charts click on the Number Options icon in the upper right corner of the chart preview. On the right, you can now choose to display a target value. Select Previous Period from the dropdown and decide whether you wish to display percentage or absolute change.