Home » Categories » CHARTrunner

CR: Multiple chart lines from one data column

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.

Attachments Attachments
There are no attachments for this article.
Related Articles RSS Feed
CR: Too many decimal places in the chart data grid for an Excel column that is calculated by a formula
Viewed 3759 times since Fri, Nov 9, 2007
CR: Label your X-Axis with more than one identifier
Viewed 2565 times since Tue, Oct 6, 2009
CR: Making a chart transportable
Viewed 2148 times since Fri, Nov 9, 2007
CR: Unable to fully display settings and windows
Viewed 1970 times since Fri, Jan 22, 2010
CR: CHARTrunner and Open Office
Viewed 2431 times since Fri, Nov 9, 2007
CR: General Filtering Tips
Viewed 2231 times since Fri, Nov 9, 2007
Cr: Charting elapsed time
Viewed 2483 times since Fri, Nov 9, 2007
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: Excel tips
Viewed 3067 times since Tue, Oct 6, 2009
CR: Some Excel Spreadsheet Values Seem To Be Missing
Viewed 2241 times since Fri, Nov 9, 2007