带有汇总数据点的折线图仪表板 [英] Line Chart Dashboard with Aggregated Data Points
本文介绍了带有汇总数据点的折线图仪表板的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在使用包含折线图，表格图和两个类别过滤器的仪表板，并且我需要弄清楚如何将多行汇总到可以在折线图上绘制的点。
I am working on a dashboard containing a line chart, Table chart, and two category filters, and I need to figure out how to aggregate multiple rows into points that can be plotted on the line chart.
给出一个保存在google工作表中的数据集，例如下面列出的示例，折线图需要显示X轴上的年份（2014年至2017年），以及所有满意率的平均值
Given a data set held in a google sheet such as the sample I list below, the line graph needs to display years across the X axis (2014 - 2017), and an average of all satisfaction rates among both companies, and all departments.
第一个CategoryFilter允许用户选择其中一个公司。当他们选择一个时，折线图应显示所有部门的合计数量。
The first CategoryFilter allows a user to select one of the companies. When they select one, the line graph should show aggregate numbers of all Departments, combined.
第二个CategoryFilter允许用户选择一个部门，折线图应显示该单个公司/部门的满意度。
The second CategoryFilter allows the user to select a department, and the line graph should show the satisfaction rates for that single company/department.
现在，一旦我向下钻取到单个公司/部门，该图就会正确显示。此时，我的任务是使聚合工作正常，直到使用两个类别过滤器向下钻取到单个部门为止。
As it stands now, once I "drill down" to the single company/department, the graph displays properly. My task at this point is to get the aggregations to work until the two category filters are used to "drill down" to a single department.
有人可以将我指向一个
Company Department Year Satisfaction_Rate
CompA Personnel 2014 0.8542
CompA Personnel 2015 0.8680
CompA Personnel 2016 0.8712
CompA Personnel 2017 0.8832
CompA Sales 2014 0.7542
CompA Sales 2015 0.7680
CompA Sales 2016 0.7712
CompA Sales 2017 0.7832
CompA Labor 2014 0.6542
CompA Labor 2015 0.6680
CompA Labor 2016 0.6712
CompA Labor 2017 0.6832
CompB Personnel 2014 0.9242
CompB Personnel 2015 0.9280
CompB Personnel 2016 0.9312
CompB Personnel 2017 0.9132
CompB Sales 2014 0.8742
CompB Sales 2015 0.8880
CompB Sales 2016 0.8112
CompB Sales 2017 0.8632
CompB Labor 2014 0.7942
CompB Labor 2015 0.8080
CompB Labor 2016 0.8112
CompB Labor 2017 0.8232
尽管此示例数据准确地代表了我正在使用的数据类型的概念，但实际数据却大不相同，正如您将在我的代码中注意到的那样。
Although this sample data accurately represents the concept of the type of data I'm working with, the actual data is quite different, as you'll notice in my code.
// Load the Visualization API and the corechart package.
google.charts.load('current', { 'packages': ['corechart', 'controls'] });
// Set a callback to run when the Google Visualization API is loaded.
google.charts.setOnLoadCallback(drawCharts);
//------------------------------------------------------------------------------
function GetDataFromSheet(URL, queryString, callback) {
var query = new google.visualization.Query(URL);
query.setQuery(queryString);
query.send(gotResponse);
function gotResponse(response) {
if (response.isError()) {
console.log(response.getReasons());
alert('Error in query: ' + response.getMessage() + " " + response.getDetailedMessage());
return;
}
if (response.hasWarning()) {
console.log(response.getReasons());
alert('Warning from query: ' + response.getMessage() + " " + response.getDetailedMessage());
return;
}
callback(response);
}
}
//------------------------------------------------------------------------------
function drawCharts() {
var URL, query;
// PREP DATA SOURCE
URL = 'https://docs.google.com/spreadsheets/d/1QygNPsYR041jat.../gviz/tq?gid=1339946796';
query = 'select A, C, D, H, J, M, P, S LABEL A "AEA", C "District", D "Class of", H "Graduation Rate", J "Post-Secondary Intention Rate", M "Enrollment Rate", P "Persistence Rate", S "Completion Rate"';
GetDataFromSheet(URL, query, handlePrep);
}
//------------------------------------------------------------------------------
// POST SECONDARY READINESS & EQUITY PARTNERSHIP
function handlePrep(response) {
var data = response.getDataTable();
// Attempting to aggregate... grouping on index 2, "class of". Example: 2015
// averaging column 3, Graduation Rate
var result = google.visualization.data.group(
data,
[2],
[{'column': 3, 'aggregation': google.visualization.data.avg, 'type': 'number'}]
);
var container = new google.visualization.Dashboard(document.getElementById('divPrep'));
var AEAControl = new google.visualization.ControlWrapper({
controlType: 'CategoryFilter',
containerId: 'divAEAPicker',
options: {
filterColumnIndex: 0,
ui: {
selectedValuesLayout: 'belowStacked',
label: 'AEA Selector ->',
caption: 'Choose an AEA...',
allowNone: true,
allowMultiple: false
},
}
});
var DistrictControl = new google.visualization.ControlWrapper({
controlType: 'CategoryFilter',
containerId: 'divDistrictPicker',
options: {
filterColumnIndex: 1,
ui: {
label: 'District Selector ->',
caption: 'Choose a District...',
allowNone: true,
allowMultiple: false
},
}
});
var chart = new google.visualization.ChartWrapper({
chartType: 'LineChart',
containerId: 'divPrepChart',
options: {
height: 400,
width: 1200,
pointSize: 5,
title: 'Post-Secondary Readiness & Equity Partnership (PREP) Trendlines',
legend: { position: 'top', maxLines: 3 },
//chartArea: { left: '10%', width: '85%'},
tooltip:{textStyle: {color: '#000000'}, showColorCode: true},
hAxis:{
format: 'yyyy',
title: 'Graduating Class Year'
},
vAxis: {
format: 'percent',
maxValue: 1,
minValue: 0
}
},
view: {'columns': [2, 3, 4, 5, 6, 7]}
});
var table = new google.visualization.ChartWrapper({
chartType: 'Table',
containerId: 'divTable',
options: {
title: 'Post-Secondary Readiness & Equity Partnership ',
legend: { position: 'top', maxLines: 3 },
//chartArea: { left: '10%', width: '85%'},
page: 'enable',
pageSize: 10
}
});
// Create a formatter to display values as percent
var percentFormatter = new google.visualization.NumberFormat({pattern: '0.00%'});
percentFormatter.format(data, 3);
percentFormatter.format(data, 4);
percentFormatter.format(data, 5);
percentFormatter.format(data, 6);
percentFormatter.format(data, 7);
// Configure the controls so that:
// - the AEA selection drives the District one
// - both the AEA and Disctirct selections drive the linechart
// - both the AEA and Districts selections drive the table
container.bind(AEAControl, DistrictControl);
container.bind([AEAControl, DistrictControl], [chart, table]);
// Draw the dashboard named 'container'
container.draw(data);
// Until we figure out how to display aggregated values at the AEA and State levels,
// keep the line graph hidden until both an AEA and District are chosen from the category filters
google.visualization.events.addListener(container, 'ready', function() {
var aeaSelectedValues = AEAControl.getState()['selectedValues'];
var districtSelectedValues = DistrictControl.getState()['selectedValues'];
if (aeaSelectedValues.length == 0 || districtSelectedValues.length == 0) {
document.getElementById('divPrepChart').style.visibility = 'hidden';
} else {
document.getElementById('divPrepChart').style.visibility = 'visible';
}
});
}
这是我的数据当前在使用以下选项选择之前的图形类别过滤器...
This is how my data currently graphs prior to options selected with the category filters...
我需要使其看起来更像这样...
I need to make it look more like this...
推荐答案
您需要分别绘制控件和图表，
，则可以在控件的'statechage'事件触发时绘制图表。
触发后，您可以根据所选值汇总数据，
并重新绘制图表。
you will need to draw the controls and charts independently, without using a dashboard.
then you can draw the charts when the control's 'statechage' event fires.
when the event fires, you can aggregate the data based on the selected values,
and re-draw the charts.
请参阅以下工作摘要...
see following working snippet...
google.charts.load('current', { 'packages': ['corechart', 'controls'] });
google.charts.setOnLoadCallback(handlePrep);
function handlePrep(response) {
var data = google.visualization.arrayToDataTable([
['Company', 'Department', 'Year', 'Satisfaction_Rate'],
['CompA', 'Personnel', 2014, 0.8542],
['CompA', 'Personnel', 2015, 0.8680],
['CompA', 'Personnel', 2016, 0.8712],
['CompA', 'Personnel', 2017, 0.8832],
['CompA', 'Sales', 2014, 0.7542],
['CompA', 'Sales', 2015, 0.7680],
['CompA', 'Sales', 2016, 0.7712],
['CompA', 'Sales', 2017, 0.7832],
['CompA', 'Labor', 2014, 0.6542],
['CompA', 'Labor', 2015, 0.6680],
['CompA', 'Labor', 2016, 0.6712],
['CompA', 'Labor', 2017, 0.6832],
['CompB', 'Personnel', 2014, 0.9242],
['CompB', 'Personnel', 2015, 0.9280],
['CompB', 'Personnel', 2016, 0.9312],
['CompB', 'Personnel', 2017, 0.9132],
['CompB', 'Sales', 2014, 0.8742],
['CompB', 'Sales', 2015, 0.8880],
['CompB', 'Sales', 2016, 0.8112],
['CompB', 'Sales', 2017, 0.8632],
['CompB', 'Labor', 2014, 0.7942],
['CompB', 'Labor', 2015, 0.8080],
['CompB', 'Labor', 2016, 0.8112],
['CompB', 'Labor', 2017, 0.8232],
]);
var AEAControl = new google.visualization.ControlWrapper({
controlType: 'CategoryFilter',
containerId: 'divAEAPicker',
dataTable: data,
options: {
filterColumnIndex: 0,
ui: {
selectedValuesLayout: 'belowStacked',
label: 'AEA Selector ->',
caption: 'Choose an AEA...',
allowNone: true,
allowMultiple: false
},
}
});
var DistrictControl = new google.visualization.ControlWrapper({
controlType: 'CategoryFilter',
containerId: 'divDistrictPicker',
dataTable: data,
options: {
filterColumnIndex: 1,
ui: {
label: 'District Selector ->',
caption: 'Choose a District...',
allowNone: true,
allowMultiple: false
},
}
});
var chart = new google.visualization.ChartWrapper({
chartType: 'LineChart',
containerId: 'divPrepChart',
options: {
height: 400,
width: 1200,
pointSize: 5,
title: 'Post-Secondary Readiness & Equity Partnership (PREP) Trendlines',
legend: { position: 'top', maxLines: 3 },
tooltip:{textStyle: {color: '#000000'}, showColorCode: true},
hAxis:{
format: '0',
title: 'Graduating Class Year'
},
vAxis: {
format: 'percent',
maxValue: 1,
minValue: 0
}
},
});
var table = new google.visualization.ChartWrapper({
chartType: 'Table',
containerId: 'divTable',
options: {
title: 'Post-Secondary Readiness & Equity Partnership ',
legend: { position: 'top', maxLines: 3 },
page: 'enable',
pageSize: 10
}
});
google.visualization.events.addListener(AEAControl, 'statechange', drawDashboard);
google.visualization.events.addListener(DistrictControl, 'statechange', drawDashboard);
function drawDashboard() {
var view = new google.visualization.DataView(data);
var valuesAEA = AEAControl.getState();
var valuesDistrict = DistrictControl.getState();
var viewRows = [];
if (valuesAEA.selectedValues.length > 0) {
viewRows.push({
column: AEAControl.getOption('filterColumnIndex'),
test: function (value) {
return (valuesAEA.selectedValues.indexOf(value) > -1);
}
});
}
if (valuesDistrict.selectedValues.length > 0) {
viewRows.push({
column: DistrictControl.getOption('filterColumnIndex'),
test: function (value) {
return (valuesDistrict.selectedValues.indexOf(value) > -1);
}
});
}
if (viewRows.length > 0) {
view.setRows(data.getFilteredRows(viewRows));
}
result = google.visualization.data.group(
view,
[2],
[{'column': 3, 'aggregation': google.visualization.data.avg, 'type': 'number'}]
);
var percentFormatter = new google.visualization.NumberFormat({pattern: '0.00%'});
percentFormatter.format(result, 1);
chart.setDataTable(result);
chart.draw();
table.setDataTable(result);
table.draw();
}
AEAControl.draw();
DistrictControl.draw();
drawDashboard();
}
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="divPrep">
<div id="divAEAPicker"></div>
<div id="divDistrictPicker"></div>
<div id="divPrepChart"></div>
<div id="divTable"></div>
</div>
这篇关于带有汇总数据点的折线图仪表板的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
