Sunday, October 14, 2012

yogi_Compute Number Of Sales By Day of Week From A Table Of Dates On Which A Sale Occurred

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #806  Oct 14, 2012    www.energyefficientbuild.com.

user christopher.r.haley said: (http://productforums.google.com/forum/?zx=3rrg4qfnsz5v#!category-topic/docs/spreadsheets/1AIA74jlaiE
Concatenating columns from query results
I sell items on eBay and log them in google spreadsheet.  I'd like to see which weekday is the best day for me in terms of when I make a sale.  In order to do this I have a column where I log the date a sale was made.  For a short example, assume this is my data (2nd column in the formulate below is necessary because you can't aggregate and non-aggregate on the same column in the SELECT query:

06-18-12
06-19-12
06-19-12
06-21-12

I then have a query to determine which day I sell the most on:

=ArrayFormula(Query((A:B),"SELECT dayOfWeek(Col1), COUNT(Col2) WHERE Col1 IS NOT NULL GROUP BY dayOfWeek(Col1) Label dayOfWeek(Col1) 'DoW', COUNT(Col2) 'Occurances'"))

This gives me the following:

DoW | Occurances
2 | 1
3 | 2
5 | 1

What I want to see is something like this:

DoW (Occurances)
2 (1)
3 (2)
5 (1)

Essentiall, I'd like to concatenate the columns from the query's results. Better yet I'd like the DoW to be the 3 letter representation for the weekday (i.e. 2 = "Mon", 3 = "Tue", 5 = "Thu"). I know I can do all of this with multiple rows/columns and either hide them or use another sheet. However, I would LOVE to do it all in one call, if possible.
-----------------------------------------------------------------------------------------------
following is a solution in one cell using only one column of Dates on which a sale occurred


No comments:

Post a Comment