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. | |
