Views over time

Please make sure you have read through the Prerequisites / Basic Set-up .

You will need to fit the name of the datasource to your own Confluence installation

Choose a query and see how many views per day it got over a given timespan.

Query

SQL

SELECT DATE,
       COUNT(ID) AS VIEWS
FROM AO_AD3311_MACRO_USAGE
WHERE QUERY_NAME = :queryname
AND DATE >= DATE_ADD(CURDATE(), INTERVAL -:days DAY)
GROUP BY DATE(DATE)
ORDER BY DATE desc

Do not forget to set the Parameter Type of "days" to "Integer".

Note that this query is MySQL specific since it is using DATE functions. If you are using another kind of database you will probably need to adjust the SQL.

Template

HTML

#set($days = $queryParameters.get('days'))
#set($queryName = $queryParameters.get('queryname'))
 
<script>
(function() {
    function subDays(date, days) {
    var result = new Date(date.valueOf());
    result.setDate(result.getDate() - days);
    return result;
    }
 
  var result = PocketQuery.queryArray();
  var dataTable = [];
 
    dataTable.push(['date', 'views', 'tooltip']);
     
    jQuery.each(result, function(rowNumber, rowObject) {
    var date = new Date(rowObject['DATE']);
    var views = rowObject['VIEWS'];
        var tooltip = views +' views (' + date.toISOString().slice(0, 10) + ')';
        dataTable.push([date, views, tooltip]);
  });
 
    if (dataTable.length > 1) {
    PocketQuery.chart('ScatterChart', {
          tooltip: {isHtml: false, trigger: 'hover'},
          beforeDraw: function(dataTable, options) {
              dataTable.setColumnProperty(2, 'role', 'tooltip');
          },
      title: 'Views of $queryName',
            legend: {position: 'none'},
      hAxis: {format: 'yyyy-MM-dd', minValue: subDays(new Date(), $days), maxValue: new Date()},
      vAxis: {format: '0'},
      dataTable: dataTable     
    });
    }
}(jQuery));
</script>