Monday, July 29, 2013

Google Spreadsheet Group By Queries

Question:

( by William Ogle )

 So here is my situation. I would like very much to be able to use data that is collected through a form, then dumped into this spreadsheet, to generate reports based on when the data was entered. Here is an example row from my spreadsheet. 

TimestampWork OrderService LevelService TeamCustomerProject CoordinatorCustomerProject CoordinatorCustomerProject CoordinatorCustomerProject CoordinatorCustomerProject Coordinator
1/1/2013 10:1255567824 HourAlphaGeneral StoreJohn Smith

I have changed most of the information for privacy sake, but the concept is the same. The form I use is set up as a 6 page form. On page 1, it asks for the work order, service level, and service team, then based on the selected service team, it will send the user to one of the pages. Each team gets its own page, because each team has different project coordinators and different customers. That is why you see customer and project coordinator repeated 5 times. Each sheet created 2 columns of its own. Lets say that team Gamma goes to page 2, team Beta goes to page 3, team Alpha goes to page 4, team Theta goes to page 5, and if the users selects "other" as the team, it goes to page 6. You can see a little more detail about how this works below. 

TimestampWork OrderService LevelService TeamCustomerProject CoordinatorCustomerProject CoordinatorCustomerProject CoordinatorCustomerProject CoordinatorCustomerProject Coordinator
1/1/2013 10:135557894 HourBetaJohnson and CoJane Doe
1/1/2013 10:1455512324 HourThetaCivic ExpressBob Builder
1/1/2013 10:1255567824 HourAlphaGeneral StoreJohn Smith

My question is, how can I use google sheets to calculate the following information automatically, based on date ranges entered?
  1. Number of Work orders submitted by team, like a pie chart showing what percent of total volume came from each team, and a table showing how many of each service level were submitted by team 
  2. Number of work orders submitted each day total, probably a bar chart showing number per day, and then a table showing how many of each kind per day. *Note: This will only apply to Friday, Saturday, and Sunday, which are our "After hours" operations. 
  3. Number of work orders submitted by customer, like a pie chart totaling up all the work orders submitted by all customers, showing what portion of total volume came in from each customer.
What I have so far is on a separate sheet. The tables looks like this. 
Breakdown of Work Orders by Service Level per Day
FridaySaturdaySundayMondaySubtotalsTotal
4 Hour0
24 Hour0
2 to 5 Day0
Subtotals0000
Total0


Breakdown of Service Calls by Team
4 Hour24 Hour2 to 5 DaySubtotalTotal
Alpha0
Beta0
Gamma0
Theta0
Other0
Subtotals000
Total0

If I could get those tables to calculate automatically, that would be awesome. I could probably figure out how to get the charts to work myself.

I realize that this is a huge project/question, and if you would prefer to solve it over chat or video or whatever, let me know. I would most definitely be willing to do that.


 Solution:

 Instead of having data in separate columns for each team.<<Each team gets its own page, because each team has different project coordinators and different customers.>>
You can have data in the same columns, as you can differentiate it by Column "service team" that is Column D.

 Have a look at the following screenshot of my Spreadsheet:

 Sheet 'Data':
Sheet 1:
I have the following formula in Cell A1:
=query('Data'!A:F;"select D,count(B) where D<>'' group by D ")
Sheet 2:
I have the following formula in Cell A1:
=query(arrayformula(if('Data'!C:C="";"";iferror(if({1,0,0};choose(weekday('Data'!A:A);"01 Sunday";"02 Monday";"03 Tuesday";"04 Wednesday";"05 Thursday";"06 Friday";"07 Saturday");if({0,1,0};'Data'!C:C;1)))));"select Col2,count(Col3) where Col1<>'' group by Col2 pivot Col1")


Sheet 3:

I have the following formula in Cell A1:
=query('Data'!A:F;"select D,count(E) where D<>'' group by D pivot C")


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

1 comment:

Subscribe to: Post Comments (Atom)