CODEWITHSUNDEEP
google-apps-scriptgoogle-visualization
Lean
Lean

Reputation: 101

Create an average line for Google Charts

I am learning to use Google Charts and I'm trying to get an average of all values and show a line on the chart to represent the average.

Below is an of how my chart looks but I need an average line for all the values.

thanks in advance for your attention.

    <html>
<body style="font-family: Arial;border: 0 none;">
<script src="https://www.gstatic.com/charts/loader.js"></script>
        <div id="dashboard" style="width:1300px;overflow:scroll;">
            <div id="chart" style="position: relative; width: 1300px; height: 300px;"></div>
            <div id="control" style="position: relative; width: 1300px; height: 30px;"></div>
        </div>



    <script type="text/javascript">



    google.charts.load('current', {
      callback: function () {
        var query = new google.visualization.Query('xxxxxxx');
        query.setQuery('select A,B,C,D');
        query.send(function (response) {
          if (response.isError()) {
            console.log('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
            return;
          }

          var control = new google.visualization.ControlWrapper({
            controlType: 'ChartRangeFilter',
            containerId: 'control',
            options: {
              filterColumnIndex: 0,
              ui: {
                chartType: 'ScatterChart',
                chartOptions: {
                 pointSize: 2,
                  chartArea: {width: '90%'},
                  hAxis: {format: 'dd/MM/yyyy'}
                },
                chartView: {
                  columns: [ 0, 1, 2]
                }
              }
            }
          });

          var chart = new google.visualization.ChartWrapper({
            chartType: 'SteppedAreaChart',
            containerId: 'chart',
            options: {
              filterColumnIndex: 0,
              pointSize: 2,
              chartArea: {height: '80%', 'width': '90%'},
              hAxis: {format: 'E dd/MMM','textStyle':{'fontSize': 11, 'color': 'black','bold':true},'minTextSpacing': 0, 'slantedText': false},
              vAxis: {format: '0'},          
              legend: {position: 'top'},
              bar: {groupWidth: '100%'},
              isStacked: false
            },
            view: {
              columns: [ 0, 1,2]

            }
          });



        var proxyTable = new google.visualization.ChartWrapper({
            chartType: 'Table',
            containerId: 'TableProxy',
            options: {
                page: 'enable',
                pageSize: 1
            },
            view: {
                columns: [0]
            }
        });



        google.visualization.events.addListener(proxyTable, 'ready', function () {
            var dt = proxyTable.getDataTable();
            var groupedData = google.visualization.data.group(dt, [0], [{
                column: 2,
                type: 'number',
                aggregation: google.visualization.data.avg
            }]);
            chart.setDataTable(groupedData);
            chart.draw();
        });


        google.visualization.events.addListener(proxyTable, 'ready', function () {
            var group = google.visualization.data.group(proxyTable.getDataTable(), [{
                column: 0,
                type: 'date',
                modifier: function () {
                    return 1;
                }
            }], [{
                column: 2,
                type: 'number',
                aggregation: google.visualization.data.avg
            }]);

          });



          dashboard = new google.visualization.Dashboard(document.getElementById('dashboard'));
          dashboard.bind(control, chart);
          dashboard.draw(response.getDataTable());
        });
      },
      packages: ['controls', 'corechart', 'table'], 'language': 'pt-br'
    });



    </script>

    </body>

    </html>

It's possible to group by date (code bellow)...but the main difficult thing to do is how to use the controlType: 'ChartRangeFilter'. Anyone has any idea??

        function floorDate(datetime) {
      var newDate = new Date(datetime);
      newDate.setHours(0);
      newDate.setMinutes(0);
      newDate.setSeconds(0);
      return newDate;
    }

    var columnChart1 = new google.visualization.ChartWrapper({
      'chartType': 'ColumnChart',
      'containerId': 'chart3'
    });
    //      columnChart1.draw();

    // Create the dashboard.
    new google.visualization.Dashboard(document.getElementById('dashboard')).
      // Configure & bind the controls 

    bind(divPicker, [table, columnChart]).
      // Draw the dashboard
    draw(data);

    google.visualization.events.addListener(divPicker, 'ready',

      function(event) {
        // group the data of the filtered table and set the result in the pie chart.
        columnChart1.setDataTable(google.visualization.data.group(
          // get the filtered results
          table.getDataTable(), [{
            'column': 0,
            'modifier': floorDate,
            'type': 'date'
          }], [{
            'column': 2,
            'aggregation': google.visualization.data.sum,
            'type': 'number'
          }]
        ));
        // redraw the pie chart to reflect changes
        columnChart1.draw();
      });

    google.visualization.events.addListener(divPicker, 'statechange',

      function(event) {
        // group the data of the filtered table and set the result in the pie chart.
        columnChart1.setDataTable(google.visualization.data.group(table.getDataTable(), [0], [{
          'column': 2,
          'aggregation': google.visualization.data.avg,
          'type': 'number'
        }]));
        // redraw the pie chart to reflect changes
        columnChart1.draw();
      });

  }

  google.setOnLoadCallback(drawVisualization);

</script>

Upvotes: 4

Views: 10399

Answers (2)

Brian Sherwood
Brian Sherwood

Reputation: 53

I appreciate this is a little old, but my searching found this and wanted to help further.

Adding a trendline gives a data's trend (increasing, decreasing) and not really the average. I cannot claim this answer as mine, please see https://groups.google.com/forum/#!topic/google-chart-api/UOdUFszYSRc

As Tom suggests I actually use the combo chart and compute a second series, but as your charts are quite complex you may wish to use the API method, which his JSFiddle (found in the link above) shows working - thanks Tom.

google.load('visualization', '1.1', {
    'packages': ['corechart']
});
google.setOnLoadCallback(drawStuff);

function drawStuff() {
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'Topping');
    data.addColumn('number', 'Slices');
    data.addRows([
        ['Mushrooms', 3],
        ['Onions', 1],
        ['Olives', 1],
        ['Zucchini', 1],
        ['Pepperoni', 2]
    ]);

    // Create a DataView that adds another column which is all the same (empty-string) to be able to aggregate on.
    var viewWithKey = new google.visualization.DataView(data);
    viewWithKey.setColumns([0, 1, {
        type: 'string',
        label: '',
        calc: function (d, r) {
            return ''
        }
    }])

    // Aggregate the previous view to calculate the average. This table should be a single table that looks like:
    // [['', AVERAGE]], so you can get the Average with .getValue(0,1)
    var group = google.visualization.data.group(viewWithKey, [2], [{
        column: 1,
        id: 'avg',
        label: 'average',
        aggregation: google.visualization.data.avg,
        'type': 'number'
    }]);

    // Create a DataView where the third column is the average.
    var dv = new google.visualization.DataView(data);
    dv.setColumns([0, 1, {
        type: 'number',
        label: 'average',
        calc: function (dt, row) {
            return group.getValue(0, 1);
        }
    }]);

    // Set chart options
    var options = {
        'title': 'How Much Pizza I Ate Last Night',
            'width': 400,
            'height': 300,
        seriesType: "bars",
        series: {
            1: {
                type: "line",
                visibleInLegend: false
            }
        },

        tooltip: {
            trigger: 'selection'
        }
    };

    // Instantiate and draw our chart, passing in some options.
    var chart = new google.visualization.ComboChart(document.getElementById('chart_div'));
    chart.draw(dv, options);
};
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>

Upvotes: 2

Matt Rice
Matt Rice

Reputation: 696

You should be able to make use of a trendline.

A trendline is a line superimposed on a chart revealing the overall direction of the data. Google Charts can automatically generate trendlines for Scatter Charts, Bar Charts, Column Charts, and Line Charts.

Guessing from the given code, you may want to add trendlines: { 0: {} } to the chartOptions for your control variable.

Putting your code into a jsFiddle or a Codepen would make it easier to debug and show you a valid solution to your particular problem.

Upvotes: 3

Related Questions