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: Label your X-Axis with more than one identifier
Viewed 2471 times since Tue, Oct 6, 2009
CR: A custom query for Length of Stay data
Viewed 2451 times since Fri, Nov 9, 2007
CR: CHARTrunner and SharePoint
Viewed 3899 times since Fri, Mar 9, 2012
CR: Background on out-of-control test rules
Viewed 2261 times since Fri, Nov 9, 2007
CR: Too many decimal places in the chart data grid for an Excel column that is calculated by a formula
Viewed 3593 times since Fri, Nov 9, 2007
CR: Time Display
Viewed 2038 times since Fri, Nov 9, 2007
CR: Charting counted incidents
Viewed 1871 times since Thu, Feb 23, 2012
CR: Why is my first row of data not included when I get my chart from Excel
Viewed 2076 times since Fri, Nov 9, 2007
CR: Chart [the name of the chart] failed with one or more errors
Viewed 2033 times since Fri, Nov 9, 2007
CR: Charting pass-fail data as a p-chart
Viewed 4354 times since Fri, Feb 29, 2008