Brilliant. That absolutely does what I needed.
Thank you so much. On Thursday, March 6, 2014 2:04:14 PM UTC-5, asgallant wrote: > > Is this what you are looking for? http://jsfiddle.net/asgallant/L7q2X/4/ > > I did that by grouping the data twice: once to get the average staff by > program number and year, then again to sum staff by year: > > var groupedStaffingDataByProgramYear = > google.visualization.data.group(fullTable.getDataTable(), [0, 1], [{ > 'column': 25, > 'aggregation': google.visualization.data.avg, > 'type': 'number' > },{ > 'column': 26, > 'aggregation': google.visualization.data.avg, > 'type': 'number' > },{ > 'column': 27, > 'aggregation': google.visualization.data.avg, > 'type': 'number' > },{ > 'column': 28, > 'aggregation': google.visualization.data.avg, > 'type': 'number' > }]); > var groupedStaffingData = > google.visualization.data.group(groupedStaffingDataByProgramYear, [1], [{ > 'column': 2, > 'aggregation': google.visualization.data.sum, > 'type': 'number' > },{ > 'column': 3, > 'aggregation': google.visualization.data.sum, > 'type': 'number' > },{ > 'column': 4, > 'aggregation': google.visualization.data.sum, > 'type': 'number' > },{ > 'column': 5, > 'aggregation': google.visualization.data.sum, > 'type': 'number' > }]); > > On Thursday, March 6, 2014 12:35:10 PM UTC-5, Christina Sanabria wrote: >> >> I'm not sure how I can do that. >> >> The controls allow the user to filter by state, within state by program >> number, and within program number by service area. Some programs, such as >> program # 436070, have multiple service areas, and therefore have multiple >> rows in the dataset per year, one for each service area. >> >> I think that if I create a DataView using the filtered data, that will >> result in an "overcount" of staff. Unlike the funding and the cases data, >> the staffing data is not specific to either service area. What happens is >> the staffing data repeats itself on each row of data for that program. >> >> Example: >> Program -- Service area -- Total staff >> #1 -- XY-5 -- 10 >> #2 -- AB-1 -- 6 >> #2 -- CD-2 -- 6 >> #3 -- GG-4 -- 8 >> >> I would have to create a DataView that uses the filtered data and >> aggregates by program number, *plus* it would have to weed out the >> second and any subsequent rows of data for each program, so that I don't >> "double count" the staff. That part (*"plus it would have to weed out >> the second and any subsequent rows of data for each program) *is the >> part I'm not sure how to do. >> >> Any suggestions? >> >> Here is my data: >> >> >> https://docs.google.com/a/lsc.gov/spreadsheet/ccc?key=0Aq9IRBUqNvQKdFBFaFlHbjF4S2pWckhrem1xMVNQVEE&usp=drive_web#gid=0 >> >> >> http://jsfiddle.net/christina_sanabria/L7q2X/1/ >> >> Thank you so much in advance and I'm sorry it's so complicated. >> >> >> >> On Thursday, March 6, 2014 11:41:50 AM UTC-5, asgallant wrote: >>> >>> You can create a DataView based on the filtered data that contains only >>> the rows that you want to sum, and group the view. >>> >>> On Thursday, March 6, 2014 11:32:08 AM UTC-5, Christina Sanabria wrote: >>>> >>>> Hi All, >>>> >>>> I have a dashboard that has multiple charts/tables and some controls >>>> that are bound to each other and to the charts/tables. Some of the >>>> charts/tables are displaying aggregated data. >>>> >>>> For one of the charts, I'm currently aggregating data by sum. However, >>>> what I really want to do is to sum only one row of data for each "Program >>>> Number" that is currently selected through the controls. Is there a way to >>>> do something like this through aggregation, or do I need to use query >>>> language to do this? >>>> >>>> http://jsfiddle.net/christina_sanabria/L7q2X/1/ >>>> >>>> var groupedStaffingData - is what I want to change. >>>> >>>> Thanks in advance for your help. >>>> >>> -- You received this message because you are subscribed to the Google Groups "Google Visualization API" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/google-visualization-api. For more options, visit https://groups.google.com/groups/opt_out.
