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: Show every date on the chart
Viewed 2243 times since Thu, Jan 31, 2008
CR: Why is my first row of data not included when I get my chart from Excel
Viewed 2146 times since Fri, Nov 9, 2007
CR: The out-of-control (OOC) test may fail on the last sample with valid data
Viewed 2115 times since Fri, Nov 9, 2007
CR: A custom query for Length of Stay data
Viewed 2581 times since Fri, Nov 9, 2007
Cr: Charting elapsed time
Viewed 2483 times since Fri, Nov 9, 2007
CR: Multiple chart lines from one data column
Viewed 2289 times since Fri, Nov 9, 2007
CR: Charts with large amounts of data
Viewed 1950 times since Fri, Nov 9, 2007
CR: Time Display
Viewed 2107 times since Fri, Nov 9, 2007
CR: Collating sequence not supported
Viewed 2693 times since Tue, Dec 11, 2007
CR: Histogram bars beyond specification lines
Viewed 2350 times since Fri, Nov 9, 2007