Applying Chart Control only to specific rows

[Ervin Puskar]

Hi,

I am building a simple dashboard. 

Using the ChartRangeFilter to specify a range of dates, I want the sum of these rows to display as one stacked bar. 

On the same chart I want to display 2 other stacked bars that are NOT in that range of dates.

Below is the relevant code that I have working but this only displays the single stacked bar that is the sum of rows. 

I do not know how to add the other two, where the chartrangefilter will not affect them.

Please help.

 

    //-------------------------------------

    var dashboard = new google.visualization.Dashboard(

    document.getElementById('dash'));

    var data = new google.visualization.DataTable();

    data.addColumn('date', 'Date');

    data.addColumn('number', 'E');

    data.addColumn('number', 'D');

    data.addColumn('number', 'O');

   //add a bunch of data

    //add two rows that are 1 year ahead and 1 year behind all the other data

       //these two rows should not be affected by the chart range filter

data.sort([{column: 0}]);

//this intermediate control would be hidden so that the user can only manipulate the date ranges of the 

var intermediate_control = new google.visualization.ControlWrapper({

'controlType': 'ChartRangeFilter',

'containerId': 'hidden_div',

'options': {

// Filter by the date axis.

'filterColumnIndex': 0,

'ui': {

   'chartType': 'ComboChart',

   'chartOptions': {

   'chartArea': {'width': '90%'},

   'hAxis': {'baselineColor': 'none'},

   'seriesType': 'bars',

   'isStacked': true

   },

        // Display a single series that shows the closing value of the sales.

        // Thus, this view has two columns: the date (axis) and the stock value (line series).

        'chartView': {

        'columns': [0,1,2,3],

        },

        // 1 day in milliseconds = 24 * 60 * 60 * 1000 = 86,400,000

        'minRangeSize': 86400000

}

    },

    // Initial range: 2012-02-09 to 2012-03-20.

    'state': {'range': {'start': new Date(today.getFullYear() - 1,0,1), 'end': new Date(today.getFullYear() - 1, 12,31)}}

    

});

    var control = new google.visualization.ControlWrapper({

'controlType': 'ChartRangeFilter',

'containerId': 'control',

'options': {

// Filter by the date axis.

'filterColumnIndex': 0,

'rows' : [1],

'ui': {

   'chartType': 'ComboChart',

   'chartOptions': {

   'chartArea': {'width': '90%'},

   'hAxis': {'baselineColor': 'none'},

   'seriesType': 'bars',

   'isStacked': true

   },

        // Display a single series that shows the closing value of the sales.

        // Thus, this view has two columns: the date (axis) and the stock value (line series).

        'chartView': {

        'columns': [0,

{

   'calc' : function(dT, r) {

var res = 0;

for (var i = 0; i < r; i++){

   res += dT.getValue(i,1);

}

return res;

   },

   'type' : 'number',

   'label' : 'E'

},

{

   'calc' : function(dT, r) {

var res = 0;

for (var i = 0; i < r; i++){

   res += dT.getValue(i,2);

}

return res;

   },

   'type' : 'number',

   'label' : 'D'

},

{

   'calc' : function(dT, r) {

var res = 0;

for (var i = 0; i < r; i++){

   res += dT.getValue(i,3);

}

return res;

   },

   'type' : 'number',

   'label' : 'O'

}

],

        },

        // 1 day in milliseconds = 24 * 60 * 60 * 1000 = 86,400,000

        'minRangeSize': 86400000

}

    },

    // Initial range: 2012-02-09 to 2012-03-20.

    'state': {'range': {'start': new Date(today.getFullYear() - 1,0,1), 'end': new Date(today.getFullYear() - 1, today.getMonth(), today.getDate())}}

    });

   var chart = new google.visualization.ChartWrapper({

     'chartType': 'ComboChart',

     'containerId': 'chart',

     'options': {

       // Use the same chart area width as the control for axis alignment.

       'chartArea': {'height': '80%', 'width': '70%'},

       'hAxis': {'slantedText': false},

       'vAxis': {'title' : 'Sales'},

       'seriesType': 'bars',

'isStacked': true

     },

     // Convert the first column from 'date' to 'string'.

     'view': {

       'columns': [

         {

           'calc': function(dataTable, rowIndex) {

             return dataTable.getFormattedValue(rowIndex, 0) + ' - ' + dataTable.getFormattedValue(dataTable.getNumberOfRows() - 1, 0);//'Last Year YTD';

           },

           'type': 'string'

         }, 

{

   'calc' : function(dT, r) {

var res = 0;

for (var i = 0; i < dT.getNumberOfRows(); i++){

   res += dT.getValue(i,1);

}

return res;

   },

   'type' : 'number',

   'label' : 'E'

},

{

   'calc' : function(dT, r) {

var res = 0;

for (var i = 0; i < dT.getNumberOfRows(); i++){

   res += dT.getValue(i,2);

}

return res;

   },

   'type' : 'number',

   'label' : 'D'

},

{

   'calc' : function(dT, r) {

var res = 0;

for (var i = 0; i < dT.getNumberOfRows(); i++){

   res += dT.getValue(i,3);

}

return res;

   },

   'type' : 'number',

   'label' : 'O'

},

],

'rows' : [0]

     }

   });

   dashboard.bind(intermediate_control, control);

   dashboard.bind(control, chart);

   

   dashboard.draw(data);

   console.log(data);

   console.log(control);

[asgallant]

Ok, let me see if I have this correct: you want users to select a range of data, and you want a chart that has 3 column stacks, 1 for the year prior to the selected range, 1 for the selected range, and one for the year after the selected range, correct?

[Ervin Puskar]

Yes pretty close.

In essence I want 3 separate stacked bars to display on the chart. But I only want the middle one to be determined by the selected range of data. The other two would be the first and last row of the initial data table. 

[Ervin Puskar]

Yea. I found a hacky way to do it changing the function in the columns. 

'calc' : function(dT, r) {

if (r == 1) {return thisYear[0];} else if (r == 2) {return lastYear[0];}

var res = 0;

for (var i = 0; i < dT.getNumberOfRows(); i++){

   res += dT.getValue(i,1);

}

return res;

   },

where lastYear and thisYear are the arrays containing the relevant data. But these rows are no longer in the data table

it's a work around but doesn't answer the question. How to I apply a control only to specific rows within the data table

[asgallant]

That hack isn't going to do what you described earlier - that just takes a filtered data set, overwrites the first and last values, and makes each row in between the sum of all previous rows.  If you select 100 days of data, the first and 100th row get overwritten, and the 98 rows in between will still display as individual columns.  If you want to have a single stacked column for the selected range, you have to group those values into a new DataTable, add rows for the previous and next years, and then draw the chart using the new DataTable.  See this example: http://jsfiddle.net/asgallant/pWbUk/