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: Some Excel Spreadsheet Values Seem To Be Missing
Viewed 2211 times since Fri, Nov 9, 2007
CR: Making a chart transportable
Viewed 2115 times since Fri, Nov 9, 2007
CR: CHARTrunner and SharePoint
Viewed 3970 times since Fri, Mar 9, 2012
CR: Time Display
Viewed 2075 times since Fri, Nov 9, 2007
CR: Error “External table is not in the expected format” using Excel 2007 workbook
Viewed 4035 times since Wed, Oct 14, 2009
CR: Label your X-Axis with more than one identifier
Viewed 2521 times since Tue, Oct 6, 2009
CR: Multiple chart lines from one data column
Viewed 2228 times since Fri, Nov 9, 2007
CR: Changing the chart type will change the Default Chart Style to missing
Viewed 2105 times since Fri, Nov 9, 2007
CR: Charting pass-fail data as a p-chart
Viewed 4450 times since Fri, Feb 29, 2008
CR: Charting counted incidents
Viewed 1935 times since Thu, Feb 23, 2012