Reputation: 891
Up front...Big Thank You to the forum for help here!
I am trying to construct a chart that has 4 stacked columns and 1 line using the group()
function.
The raw data table looks like this (please ignore goal): [![enter image description here][1]][1]
The grouped data table should look like this (please ignore goal and total goal): [![enter image description here][2]][2]
I can see that the dashboard renders, but it's really skinny and I can't figure out what makes it do this. It looks like it's at least picking up the grouped EZ figures because the mouse over shows the grouped totals as expected.
The goal is behaving oddly as well. I think I will need to do some sort of calculation for the day total in order to fix this part. I will address this after the stacked bars are corrected. (EDIT: I already know what to do with this for now.)
But I can't really tell what it's doing because it's so tiny!
EDIT: Below reply from WhiteHat worked beautifully.
// Load the Visualization API and the corechart package.
google.charts.load('current', {
'packages': ['corechart', 'table', 'gauge', 'controls']
});
// Set a callback to run when the Google Visualization API is loaded.
google.charts.setOnLoadCallback(gChart0);
function gChart0() {
drawDashboard_TEMPLATE4("2", "LineName");
};
function drawDashboard_TEMPLATE4(p1, v1) {
var divID_suffixFunction = '_TEMPLATE';
var divID_suffixParameter1 = '';
var urlParameter1 = '?prodID=';
var urlParameter1_Value = p1; //comes from index
var prodID_Name = v1; //comes from index
//Temporary until final data source. Then use above.
var urlParameter1 = '?ohg='
var urlParameter1_Value = '982A01';
//Temporary until final data source used as filter instead. Use above.
var prodFilter = Number(p1);
var urlString = '../WWIDetailRaws/filterWWIDetails' + urlParameter1 + urlParameter1_Value;
var urlString_temp = 'https://httpbin.org/get'; //HTTP Test server that accepts GET/Post calls
$.ajax({
type: 'GET',
dataType: 'json',
contentType: "application/json",
//url: urlString,
url: urlString_temp,
success: function(result) {
//Manually loaded "result" with JSON that normally comes from "urlString".
result = [{
"prodID": 2,
"calDay": new Date(2017, 10, 15),
"v1": 100,
"goal": 200,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "EZRS",
"p7": "3000",
"p8": "I",
"p9": "6002LG0024"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 15),
"v1": 110,
"goal": 210,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "EZRS",
"p7": "3000",
"p8": "I",
"p9": "6002LG0025"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 16),
"v1": 120,
"goal": 220,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "EZRS",
"p7": "3000",
"p8": "I",
"p9": "6002LG0024"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 16),
"v1": 130,
"goal": 230,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "EZRS",
"p7": "3000",
"p8": "I",
"p9": "6002LG0025"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 15),
"v1": 140,
"goal": 240,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "EZFE",
"p7": "7900",
"p8": "I",
"p9": "0258017423"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 15),
"v1": 150,
"goal": 250,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "EZFE",
"p7": "7900",
"p8": "I",
"p9": "0258017424"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 16),
"v1": 160,
"goal": 260,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "EZFE",
"p7": "7900",
"p8": "I",
"p9": "0258017423"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 16),
"v1": 170,
"goal": 270,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "EZFE",
"p7": "7900",
"p8": "I",
"p9": "0258017424"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 15),
"v1": 180,
"goal": 280,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "FE",
"p7": "7920",
"p8": "I",
"p9": "02580173502MG"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 15),
"v1": 190,
"goal": 290,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "FE",
"p7": "7920",
"p8": "I",
"p9": "02580173502MF"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 16),
"v1": 300,
"goal": 400,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "FE",
"p7": "7920",
"p8": "I",
"p9": "02580173502MG"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 16),
"v1": 310,
"goal": 410,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "FE",
"p7": "7920",
"p8": "I",
"p9": "02580173502MF"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 17),
"v1": 320,
"goal": 420,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "FE",
"p7": "7920",
"p8": "I",
"p9": "02580173502MG"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 17),
"v1": 330,
"goal": 430,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "FE",
"p7": "7920",
"p8": "I",
"p9": "02580173502MF"
}
];
//Create DataTable
var data = new google.visualization.DataTable();
//Add Columns
data.addColumn('number', 'prodID'); //0 prodID
data.addColumn('date', 'Calendar Day'); //1 calDay
data.addColumn('number', 'EZFE'); //2 case from v1
data.addColumn('number', 'EZRS'); //3 case from v1
data.addColumn('number', 'EZ'); //4 case from v1
data.addColumn('number', 'Other'); //5 case from v1
data.addColumn('number', 'Goal'); //6 goal
data.addColumn('string', 'Plant'); //7 p1
data.addColumn('string', 'GB'); //8 p2
data.addColumn('string', 'Overhead Group'); //9 p3
data.addColumn('string', 'Profit Center'); //10 p4
data.addColumn('string', 'MRP'); //11 p5
data.addColumn('string', 'MBR Stock Category'); //12 p6
data.addColumn('string', 'Valuation Class'); //13 p7
data.addColumn('string', 'Inventory Element'); //14 p8
data.addColumn('string', 'Material'); //15 p9
//Format date object into from JSON to ISO
var dateJSON = new Date(); //obj.clnDate
var y = dateJSON.getFullYear();
var m = dateJSON.getMonth();
var d = dateJSON.getDate();
var dateISO = new Date(y, m, d);
//Add Rows
var dataArray = [];
$.each(result, function(i, obj) {
var bar1 = 0; //EZRS
var bar2 = 0; //EZFE
var bar3 = 0; //FE
var bar4 = 0; //Other
switch (obj.p6) {
case 'EZRS':
bar1 = obj.v1;
break;
case 'EZFE':
bar2 = obj.v1;
break;
case 'FE':
bar3 = obj.v1;
break;
default:
bar4 = obj.v1;
}
dataArray.push([
obj.prodID,
//dateISO,
obj.calDay,
bar1, bar2, bar3, bar4, obj.goal,
obj.p1, obj.p2, obj.p3, obj.p4, obj.p5, obj.p6, obj.p7, obj.p8, obj.p9
]);
});
data.addRows(dataArray);
//Create Data View
var view = new google.visualization.DataView(data);
view.setRows(data.getFilteredRows([{
column: 0,
value: prodFilter
}]));
//view.setColumns([0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]);
//Create Data Aggregation
var groupView = google.visualization.data.group(view, [{
column: 1,
type: 'date'
}, //0
{
column: 12,
type: 'string'
} //1 MBR
], [{
column: 2,
aggregation: google.visualization.data.sum,
type: 'number'
}, //2 EZRS
{
column: 3,
aggregation: google.visualization.data.sum,
type: 'number'
}, //3 EZFE
{
column: 4,
aggregation: google.visualization.data.sum,
type: 'number'
}, //4 FE
{
column: 5,
aggregation: google.visualization.data.sum,
type: 'number'
}, //5 OTHER
//{
//column: 6,
//aggregation: google.visualization.data.sum,
//type: 'number'
//} //6
]);
// Create a dashboard.
var dashboard = new google.visualization.Dashboard(
document.getElementById('div_dashboard' + divID_suffixFunction + divID_suffixParameter1));
var categoryPicker1 = new google.visualization.ControlWrapper({
'controlType': 'ChartRangeFilter',
'containerId': 'div_dashboard_categoryPicker1' + divID_suffixFunction + divID_suffixParameter1,
'options': {
filterColumnIndex: 0,
ui: {
chartType: 'ComboChart',
chartView: {
columns: [0, 2, 3, 4, 5] //removed 6 goal
},
chartOptions: {
//seriesType: 'bars',
height: 50,
//width: 600,
chartArea: {
width: '80%'
},
series: {
//column 1 is first v1 - Start couting 0 here for series.
0: {
color: 'rgb(0, 86, 145)'
},
/* Dark Blue */
1: {
color: 'rgb(0, 142, 207)'
},
/* Light Blue */
2: {
color: 'rgb(127, 198, 231)'
},
/* Light Blue Pastell */
3: {
color: 'rgb(191,192,194)'
},
/* Light Gray */
4: {
color: 'rgb(226,0,21)'
},
/* Red */
},
}, //END chartOptions
} //END ui
} //END 'options'
}); //END categoryPicker1
var chart = new google.visualization.ChartWrapper({
'chartType': 'ComboChart',
'containerId': 'div_dashboard_chart' + divID_suffixFunction + divID_suffixParameter1,
'view': {
'columns': [0, 2, 3, 4, 5] //removed 6 goal
},
'options': {
width: '100%',
height: 'auto',
seriesType: 'bars',
isStacked: true,
series: {
//column 1 is first v1 - Start couting 0 here for series.
0: {
color: 'rgb(0, 86, 145)'
},
/* Dark Blue */
1: {
color: 'rgb(0, 142, 207)'
},
/* Light Blue */
2: {
color: 'rgb(127, 198, 231)'
},
/* Light Blue Pastell */
3: {
color: 'rgb(191,192,194)'
},
/* Light Gray */
4: {
color: 'rgb(226,0,21)',
type: 'line',
} /* Red */
},
}
});
//Object binding
dashboard.bind([categoryPicker1], [chart]);
//Draw dashboard
dashboard.draw(groupView);
} //END success: function (result) {
}); //END $.ajax({
} //END function drawChart()
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="div_dashboard_TEMPLATE" style="">
<div id="div_dashboard_table_TEMPLATE" style="width:100%;"></div>
<div id="div_dashboard_chart_TEMPLATE" style="width:100%;"></div>
<div id="div_dashboard_categoryPicker1_TEMPLATE" style="width:100%;"></div>
</div>
Upvotes: 1
Views: 811
Reputation: 61222
date values on the x-axis result in skinny columns,
because each date represents a millisecond in time
if you have 3 dates on the x-axis,
that's a-lot of milliseconds to represent
in my opinion, column charts work better with a discrete axis,
or string values
however, the range filter will not work with string values
so we can leave the data alone,
and add a calculated column to the chart's view
property...
var formatDate = new google.visualization.DateFormat({
pattern: 'MM/dd/yyyy'
});
var chart = new google.visualization.ChartWrapper({
'chartType': 'ComboChart',
'containerId': 'div_dashboard_chart' + divID_suffixFunction + divID_suffixParameter1,
'view': {
columns: [
{
label: groupView.getColumnLabel(0),
type: 'string',
calc: function (dt, row) {
return formatDate.formatValue(dt.getValue(row, 0));
}
}, 1, 2, 3, 4
]
},
...
see following working snippet...
// Load the Visualization API and the corechart package.
google.charts.load('current', {
callback: gChart0,
packages: ['corechart', 'table', 'gauge', 'controls']
});
function gChart0() {
drawDashboard_TEMPLATE4("2", "LineName");
};
function drawDashboard_TEMPLATE4(p1, v1) {
var divID_suffixFunction = '_TEMPLATE';
var divID_suffixParameter1 = '';
var urlParameter1 = '?prodID=';
var urlParameter1_Value = p1; //comes from index
var prodID_Name = v1; //comes from index
//Temporary until final data source. Then use above.
var urlParameter1 = '?ohg='
var urlParameter1_Value = '982A01';
//Temporary until final data source used as filter instead. Use above.
var prodFilter = Number(p1);
var urlString = '../WWIDetailRaws/filterWWIDetails' + urlParameter1 + urlParameter1_Value;
var urlString_temp = 'https://httpbin.org/get'; //HTTP Test server that accepts GET/Post calls
$.ajax({
type: 'GET',
dataType: 'json',
contentType: "application/json",
//url: urlString,
url: urlString_temp,
success: function(result) {
//Manually loaded "result" with JSON that normally comes from "urlString".
result = [{
"prodID": 2,
"calDay": new Date(2017, 10, 15),
"v1": 100,
"goal": 200,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "EZRS",
"p7": "3000",
"p8": "I",
"p9": "6002LG0024"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 15),
"v1": 110,
"goal": 210,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "EZRS",
"p7": "3000",
"p8": "I",
"p9": "6002LG0025"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 16),
"v1": 120,
"goal": 220,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "EZRS",
"p7": "3000",
"p8": "I",
"p9": "6002LG0024"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 16),
"v1": 130,
"goal": 230,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "EZRS",
"p7": "3000",
"p8": "I",
"p9": "6002LG0025"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 15),
"v1": 140,
"goal": 240,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "EZFE",
"p7": "7900",
"p8": "I",
"p9": "0258017423"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 15),
"v1": 150,
"goal": 250,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "EZFE",
"p7": "7900",
"p8": "I",
"p9": "0258017424"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 16),
"v1": 160,
"goal": 260,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "EZFE",
"p7": "7900",
"p8": "I",
"p9": "0258017423"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 16),
"v1": 170,
"goal": 270,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "EZFE",
"p7": "7900",
"p8": "I",
"p9": "0258017424"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 15),
"v1": 180,
"goal": 280,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "FE",
"p7": "7920",
"p8": "I",
"p9": "02580173502MG"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 15),
"v1": 190,
"goal": 290,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "FE",
"p7": "7920",
"p8": "I",
"p9": "02580173502MF"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 16),
"v1": 300,
"goal": 400,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "FE",
"p7": "7920",
"p8": "I",
"p9": "02580173502MG"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 16),
"v1": 310,
"goal": 410,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "FE",
"p7": "7920",
"p8": "I",
"p9": "02580173502MF"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 17),
"v1": 320,
"goal": 420,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "FE",
"p7": "7920",
"p8": "I",
"p9": "02580173502MG"
},
{
"prodID": 2,
"calDay": new Date(2017, 10, 17),
"v1": 330,
"goal": 430,
"p1": "9820",
"p2": "GS",
"p3": "982G01",
"p4": "P00258",
"p5": "315",
"p6": "FE",
"p7": "7920",
"p8": "I",
"p9": "02580173502MF"
}
];
//Create DataTable
var data = new google.visualization.DataTable();
//Add Columns
data.addColumn('number', 'prodID'); //0 prodID
data.addColumn('date', 'Calendar Day'); //1 calDay
data.addColumn('number', 'EZFE'); //2 case from v1
data.addColumn('number', 'EZRS'); //3 case from v1
data.addColumn('number', 'EZ'); //4 case from v1
data.addColumn('number', 'Other'); //5 case from v1
data.addColumn('number', 'Goal'); //6 goal
data.addColumn('string', 'Plant'); //7 p1
data.addColumn('string', 'GB'); //8 p2
data.addColumn('string', 'Overhead Group'); //9 p3
data.addColumn('string', 'Profit Center'); //10 p4
data.addColumn('string', 'MRP'); //11 p5
data.addColumn('string', 'MBR Stock Category'); //12 p6
data.addColumn('string', 'Valuation Class'); //13 p7
data.addColumn('string', 'Inventory Element'); //14 p8
data.addColumn('string', 'Material'); //15 p9
//Format date object into from JSON to ISO
var dateJSON = new Date(); //obj.clnDate
var y = dateJSON.getFullYear();
var m = dateJSON.getMonth();
var d = dateJSON.getDate();
var dateISO = new Date(y, m, d);
//Add Rows
var dataArray = [];
$.each(result, function(i, obj) {
var bar1 = 0; //EZRS
var bar2 = 0; //EZFE
var bar3 = 0; //FE
var bar4 = 0; //Other
switch (obj.p6) {
case 'EZRS':
bar1 = obj.v1;
break;
case 'EZFE':
bar2 = obj.v1;
break;
case 'FE':
bar3 = obj.v1;
break;
default:
bar4 = obj.v1;
}
dataArray.push([
obj.prodID,
//dateISO,
obj.calDay,
bar1, bar2, bar3, bar4, obj.goal,
obj.p1, obj.p2, obj.p3, obj.p4, obj.p5, obj.p6, obj.p7, obj.p8, obj.p9
]);
});
data.addRows(dataArray);
//Create Data View
var view = new google.visualization.DataView(data);
view.setRows(data.getFilteredRows([{
column: 0,
value: prodFilter
}]));
//view.setColumns([0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]);
//Create Data Aggregation
var groupView = google.visualization.data.group(view, [1], [
{
column: 2,
aggregation: google.visualization.data.sum,
type: 'number'
}, //2 EZRS
{
column: 3,
aggregation: google.visualization.data.sum,
type: 'number'
}, //3 EZFE
{
column: 4,
aggregation: google.visualization.data.sum,
type: 'number'
}, //4 FE
{
column: 5,
aggregation: google.visualization.data.sum,
type: 'number'
} //5 OTHER
]);
// Create a dashboard.
var dashboard = new google.visualization.Dashboard(
document.getElementById('div_dashboard' + divID_suffixFunction + divID_suffixParameter1));
var categoryPicker1 = new google.visualization.ControlWrapper({
'controlType': 'ChartRangeFilter',
'containerId': 'div_dashboard_categoryPicker1' + divID_suffixFunction + divID_suffixParameter1,
'options': {
filterColumnIndex: 0,
ui: {
chartType: 'LineChart',
chartOptions: {
height: 50,
chartArea: {
width: '80%'
},
series: {
//column 1 is first v1 - Start couting 0 here for series.
0: {
color: 'rgb(0, 86, 145)'
},
/* Dark Blue */
1: {
color: 'rgb(0, 142, 207)'
},
/* Light Blue */
2: {
color: 'rgb(127, 198, 231)'
},
/* Light Blue Pastell */
3: {
color: 'rgb(191,192,194)'
},
/* Light Gray */
4: {
color: 'rgb(226,0,21)'
},
/* Red */
},
}, //END chartOptions
} //END ui
} //END 'options'
}); //END categoryPicker1
var formatDate = new google.visualization.DateFormat({
pattern: 'MM/dd/yyyy'
});
var chart = new google.visualization.ChartWrapper({
'chartType': 'ComboChart',
'containerId': 'div_dashboard_chart' + divID_suffixFunction + divID_suffixParameter1,
'view': {
columns: [
{
label: groupView.getColumnLabel(0),
type: 'string',
calc: function (dt, row) {
return formatDate.formatValue(dt.getValue(row, 0));
}
}, 1, 2, 3, 4
]
},
'options': {
width: '100%',
height: 'auto',
seriesType: 'bars',
isStacked: true,
series: {
//column 1 is first v1 - Start couting 0 here for series.
0: {
color: 'rgb(0, 86, 145)'
},
/* Dark Blue */
1: {
color: 'rgb(0, 142, 207)'
},
/* Light Blue */
2: {
color: 'rgb(127, 198, 231)'
},
/* Light Blue Pastell */
3: {
color: 'rgb(191,192,194)'
},
/* Light Gray */
4: {
color: 'rgb(226,0,21)',
type: 'line',
} /* Red */
},
}
});
//Object binding
dashboard.bind([categoryPicker1], [chart]);
//Draw dashboard
dashboard.draw(groupView);
} //END success: function (result) {
}); //END $.ajax({
} //END function drawChart()
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="div_dashboard_TEMPLATE" style="">
<div id="div_dashboard_table_TEMPLATE" style="width:100%;"></div>
<div id="div_dashboard_chart_TEMPLATE" style="width:100%;"></div>
<div id="div_dashboard_categoryPicker1_TEMPLATE" style="width:100%;"></div>
</div>
Upvotes: 1