Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.


Info

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

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

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

Query


Code Block
languagesql
titleQuery
collapsetrue
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".

Warning

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

Code Block
titleTemplate
collapsetrue
#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>



Panel
titleDownload this example!


PQMU-views-over-time.xml


Download the export file with the entities on the left and import it in your PocketQuery administration.


Warning

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.


...