Revision Date: 2005-09-19
Problem:
You may have a need to look at a single data column and create a chart with more than one data line. Imagine a spreadsheet containing many rows in this general form:
|
Date |
Section |
Rating |
|
1/1/2003 |
Admission |
4 |
|
1/1/2003 |
Waiting |
5 |
|
1/3/2003 |
Admission |
3 |
|
1/3/2003 |
Waiting |
4 |
|
1/5/2003 |
Admission |
5 |
On the same chart you might want to compare two data lines; one for Admission and another one for Waiting. | |
Solution:
| If you want CHARTrunner to create two data lines for a multi-line chart your data source must result in two data columns. In this case, rather than use a table as the data source, you must use a custom query. A custom query is written in standard SQL format. Here is a common query example:
SELECT Date, Section, Rating from MyDataTable
To solve this problem we need a special type of query known as a pivot table query or as a transform query. The following query will solve the problem. Do not be intimidated by this – it is only text that you are welcome to cut and paste and alter to fit your situation:
TRANSFORM Sum([Rating])
SELECT [Date]
FROM [Satisfaction$]
GROUP BY [Date]
ORDER BY [Date]
PIVOT "Section " & [Section]
The query will return a set of data like this:
|
Date |
Section Admission |
Section Waiting |
|
1/1/2003 |
4 |
5 |
|
1/3/2003 |
3 |
4 |
|
1/5/2003 |
5 |
4 |
|
1/7/2003 |
3 |
5 |
This will allow you to setup a multi-line chart where Section Admission is the first data line and Section Waiting is the second data line. | |
