Line Chart Dashboard with Aggregated Data Points

2019-08-24 03:07发布

站内文章 / 移动开发
12 0 0

问题:

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.

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.

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.

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.

Can anybody point me to a resource that describes how to accomplish this, or to a working example that I can see how to code it?

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...

回答1:

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>



标签： charts google-visualization
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~