Display of trendline with a pocketquery template

I want to display a trendline in pocketquery template:

PocketQuery.chart('LineChart',
{
title: 'Case Trendline',
width: 1000,
heigth: 500,
vAxis: {title: 'Number'},
trendlines: { 0: {
type: 'linear',
color: 'green'},
}
});

The SQL query returns the date as string in the form '2015-01'.

A trendline in only displayed if the date column is of object Date.

Any idea how I can convert the string to a Date object in the pocketquery template, so that a trendline is displayed?

Here is the sql query:

select cast(year(Createdon) as varchar(10)) + '-' + right('0' + cast(month(Createdon) as varchar),2) as [The Month],
count(*) as 'Opened Cases'
from IncidentBase
where casetypecode = 1
and Createdon >= '2012-01-01'
and Createdon <= getdate()
group by cast(year(Createdon) as varchar(10)) + '-' + right('0' + cast(month(Createdon) as varchar),2), convert(varchar(6), Createdon, 112)
order by convert(varchar(6), Createdon, 112)

2 answers

1 accepted

0 votes

Answer accepted

Hi Keith,

Thank you for your interest in PocketQuery. I suggest try to use the dataTable option and do some JS transformation such that your result will be accepted by the chart API. It works like this...

&lt;script&gt;
(function() {
  var result = PocketQuery.queryArray(); // result array of the query
  var columnsArray = PocketQuery.queryColumns(); // result columns
  var dataTable = [columnsArray]; // start with one row that contains the query columns
  jQuery.each(result, function(index, row) { // for each row in the result
  	// do some transformation and push adequate data into your dataTable like this:
  	var myDate = new Date(row.column2Name);
    dataTable.push([row.column1Name, myDate, row.whateverColumnName]);
  });
  PocketQuery.chart('Timeline', {
  	// ...your options...
    dataTable: dataTable
  });
}())
&lt;/script&gt;

Let me know if you need further help!

Regards, Felix (Scandio)