Charts: Difference between revisions

From Data Islands
(Created page with " Examples: # Northwind Orders table SQL select EXTRACT(month from TO_DATE("OrderDate",'MM')) AS "MON", TO_CHAR(TO_DATE("OrderDate",'MM/DD/YYYY'), 'Month') AS "Month", SUM(1) from orders group by  EXTRACT(month from TO_DATE("OrderDate",'MM')) , TO_CHAR(TO_DATE("OrderDate",'MM/DD/YYYY'), 'Month') order by EXTRACT(month from TO_DATE("OrderDate",'MM'))")
 
(chart js examples put in)
Line 20: Line 20:


order by EXTRACT(month from TO_DATE("OrderDate",'MM'))
order by EXTRACT(month from TO_DATE("OrderDate",'MM'))
this.draw('<nowiki><style>canvas {border: 1px dotted red;} .chart-container {position: relative;margin: auto; height:40vh;width:40vw;}</style></nowiki><nowiki><div class="chart-container"><canvas id="chart"></canvas></nowiki><nowiki></div></nowiki>');
//LUIS this is actually how
//var _sampledata=this.querydb("Northwind","Orders","select EXTRACT(month from TO_DATE(\"OrderDate\",'MM')) AS \"MON\", TO_CHAR(TO_DATE(\"OrderDate\",'MM/DD/YYYY'), 'Month') AS \"Month\", SUM(1) from orders group by EXTRACT(month from TO_DATE(\"OrderDate\",'MM')) , TO_CHAR(TO_DATE(\"OrderDate\",'MM/DD/YYYY'), 'Month') order by EXTRACT(month from TO_DATE(\"OrderDate\",'MM'))")
//hard coded sample data
var _sampledata={"returnValue":[{"MON":1.0,"Month":"January  ","sum":88},{"MON":2.0,"Month":"February ","sum":83},{"MON":3.0,"Month":"March    ","sum":103},{"MON":4.0,"Month":"April    ","sum":105},{"MON":5.0,"Month":"May      ","sum":46},{"MON":6.0,"Month":"June     ","sum":30},{"MON":7.0,"Month":"July     ","sum":55},{"MON":8.0,"Month":"August   ","sum":58},{"MON":9.0,"Month":"September","sum":60},{"MON":10.0,"Month":"October  ","sum":64}],"total":12,"message":null,"requestUrl":"<nowiki>https://apidev.crmtogether.com/158f70ae-e272-437b-822f-87797f2dd37e/islands/query?page=1</nowiki>","region":"northwind","regionId":"158f70ae-e272-437b-822f-87797f2dd37e","userName":"crmtogether","page":1,"query":"select EXTRACT(month from TO_DATE(\"OrderDate\",'MM')) AS \"MON\", TO_CHAR(TO_DATE(\"OrderDate\",'MM/DD/YYYY'), 'Month') AS \"Month\", SUM(1)\nfrom orders\ngroup by  EXTRACT(month from TO_DATE(\"OrderDate\",'MM')) , TO_CHAR(TO_DATE(\"OrderDate\",'MM/DD/YYYY'), 'Month') \norder by EXTRACT(month from TO_DATE(\"OrderDate\",'MM'))","island":"TO_DATE","islandDescription":"","createDate":"","updatedDate":""}
var _labels=[];
var _data=[];
for(var x=0;x<_sampledata.returnValue.length;x++)
{
  _labels.push(_sampledata.returnValue[x]["Month"]);
  _data.push(_sampledata.returnValue[x]["sum"]);
}
var data = {
  labels: _labels,
  datasets: [{
   label: "Order Summary",
   backgroundColor: "rgba(255,99,132,0.2)",
   borderColor: "rgba(255,99,132,1)",
   borderWidth: 2,
   hoverBackgroundColor: "rgba(255,99,132,0.4)",
   hoverBorderColor: "rgba(255,99,132,1)",
   data: _data,
  }]
};
var options = {
  maintainAspectRatio: false,
  scales: {
   y: {
     stacked: true,
     grid: {
       display: true,
       color: "rgba(255,99,132,0.2)"
     }
   },
   x: {
     grid: {
       display: false
     }
   }
  }
};
setTimeout(() => {
  new Chart('chart', {
  type: 'bar',
  options: options,
  data: data
});
}, 500);
/////////////////////////
example 2
this.draw('<nowiki><div class="chart-container"><canvas id="chart3"></canvas></nowiki><nowiki></div></nowiki>');
setTimeout(() => {
new Chart('chart3', {
  type: "line",
  data: {
   labels: ["Red", "Blue", "Yellow", "Green", "Purple", "Orange"],
   datasets: [
     {
       label: "# of Votes",
       data: [12, 19, 3, 5, 2, 3]
     }
   ]
  },
  options: {
   scales: {
     y: {
       ticks: {
         beginAtZero: true
       }
     }
   }
  }
});
}, 500);

Revision as of 14:57, 11 August 2022




Examples:

  1. Northwind Orders table


SQL


select EXTRACT(month from TO_DATE("OrderDate",'MM')) AS "MON", TO_CHAR(TO_DATE("OrderDate",'MM/DD/YYYY'), 'Month') AS "Month", SUM(1)

from orders

group by  EXTRACT(month from TO_DATE("OrderDate",'MM')) , TO_CHAR(TO_DATE("OrderDate",'MM/DD/YYYY'), 'Month')

order by EXTRACT(month from TO_DATE("OrderDate",'MM'))


this.draw('<style>canvas {border: 1px dotted red;} .chart-container {position: relative;margin: auto; height:40vh;width:40vw;}</style><div class="chart-container"><canvas id="chart"></canvas></div>');

//LUIS this is actually how

//var _sampledata=this.querydb("Northwind","Orders","select EXTRACT(month from TO_DATE(\"OrderDate\",'MM')) AS \"MON\", TO_CHAR(TO_DATE(\"OrderDate\",'MM/DD/YYYY'), 'Month') AS \"Month\", SUM(1) from orders group by EXTRACT(month from TO_DATE(\"OrderDate\",'MM')) , TO_CHAR(TO_DATE(\"OrderDate\",'MM/DD/YYYY'), 'Month') order by EXTRACT(month from TO_DATE(\"OrderDate\",'MM'))")

//hard coded sample data

var _sampledata={"returnValue":[{"MON":1.0,"Month":"January  ","sum":88},{"MON":2.0,"Month":"February ","sum":83},{"MON":3.0,"Month":"March    ","sum":103},{"MON":4.0,"Month":"April    ","sum":105},{"MON":5.0,"Month":"May      ","sum":46},{"MON":6.0,"Month":"June     ","sum":30},{"MON":7.0,"Month":"July     ","sum":55},{"MON":8.0,"Month":"August   ","sum":58},{"MON":9.0,"Month":"September","sum":60},{"MON":10.0,"Month":"October  ","sum":64}],"total":12,"message":null,"requestUrl":"https://apidev.crmtogether.com/158f70ae-e272-437b-822f-87797f2dd37e/islands/query?page=1","region":"northwind","regionId":"158f70ae-e272-437b-822f-87797f2dd37e","userName":"crmtogether","page":1,"query":"select EXTRACT(month from TO_DATE(\"OrderDate\",'MM')) AS \"MON\", TO_CHAR(TO_DATE(\"OrderDate\",'MM/DD/YYYY'), 'Month') AS \"Month\", SUM(1)\nfrom orders\ngroup by  EXTRACT(month from TO_DATE(\"OrderDate\",'MM')) , TO_CHAR(TO_DATE(\"OrderDate\",'MM/DD/YYYY'), 'Month') \norder by EXTRACT(month from TO_DATE(\"OrderDate\",'MM'))","island":"TO_DATE","islandDescription":"","createDate":"","updatedDate":""}

var _labels=[];

var _data=[];

for(var x=0;x<_sampledata.returnValue.length;x++)

{

  _labels.push(_sampledata.returnValue[x]["Month"]);

  _data.push(_sampledata.returnValue[x]["sum"]);

}

var data = {

  labels: _labels,

  datasets: [{

   label: "Order Summary",

   backgroundColor: "rgba(255,99,132,0.2)",

   borderColor: "rgba(255,99,132,1)",

   borderWidth: 2,

   hoverBackgroundColor: "rgba(255,99,132,0.4)",

   hoverBorderColor: "rgba(255,99,132,1)",

   data: _data,

  }]

};

var options = {

  maintainAspectRatio: false,

  scales: {

   y: {

     stacked: true,

     grid: {

       display: true,

       color: "rgba(255,99,132,0.2)"

     }

   },

   x: {

     grid: {

       display: false

     }

   }

  }

};

setTimeout(() => {

  new Chart('chart', {

  type: 'bar',

  options: options,

  data: data

});

}, 500);


/////////////////////////

example 2


this.draw('<div class="chart-container"><canvas id="chart3"></canvas></div>');

setTimeout(() => {

new Chart('chart3', {

  type: "line",

  data: {

   labels: ["Red", "Blue", "Yellow", "Green", "Purple", "Orange"],

   datasets: [

     {

       label: "# of Votes",

       data: [12, 19, 3, 5, 2, 3]

     }

   ]

  },

  options: {

   scales: {

     y: {

       ticks: {

         beginAtZero: true

       }

     }

   }

  }

});

}, 500);