Home » Categories » CHARTrunner

CR: Grouping counts by date in Excel

Revision Date: 2005-09-19

Problem:

Several customers have asked about counting by date. Imagine that you are collecting data where each row contains a date along with other information. You want to create a chart showing how many records you have for a given period of time. For example, how many records are we collecting per week or how many records have the response “poor” per month?

Solution:

Structured Query Language (SQL) contains powerful features for grouping data based on dates. In the attached file you will find an example Excel spreadsheet and several CHARTrunner chart definitions. Unzip this file into a temporary folder on your PC and then set this as your working folder within CHARTrunner. Study the Excel sheet and then look at the example charts. Each chart uses a different custom query to count data based on the date column. Do not be intimidated by these SQL queries. They can probably be cut and pasted into your situation with only minor changes for column names.

The Excel sheet has user entered data only in the first two columns. The remaining columns contain formulas based on the date column. You would never need all of these columns in a spreadsheet – but they are a good source of reference for various ways you might want manipulate a date in an Excel formula.

Attachments (1) Attachments
Related Articles RSS Feed
CR: Data grouping tardiness
Viewed 3424 times since Tue, Oct 6, 2009
CR: Background on out-of-control test rules
Viewed 2385 times since Fri, Nov 9, 2007
CR: Histogram bars beyond specification lines
Viewed 2404 times since Fri, Nov 9, 2007
CR: Charting counted incidents
Viewed 2190 times since Thu, Feb 23, 2012
Cr: Charting elapsed time
Viewed 2558 times since Fri, Nov 9, 2007
CR: Unable to fully display settings and windows
Viewed 2045 times since Fri, Jan 22, 2010
CR: Collating sequence not supported
Viewed 2801 times since Tue, Dec 11, 2007
CR: Error “External table is not in the expected format” using Excel 2007 workbook
Viewed 4239 times since Wed, Oct 14, 2009
CR: CHARTrunner and Open Office
Viewed 2492 times since Fri, Nov 9, 2007
CR: Show every date on the chart
Viewed 2302 times since Thu, Jan 31, 2008