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: Formatting an Excel Date
Viewed 2625 times since Fri, Nov 9, 2007
CR: Excel tips
Viewed 3125 times since Tue, Oct 6, 2009
CR: Collating sequence not supported
Viewed 2800 times since Tue, Dec 11, 2007
CR: Grouping counts by date in Excel
Viewed 2257 times since Fri, Nov 9, 2007
CR: Making an X-bar chart with a single data column
Viewed 2160 times since Fri, Nov 9, 2007
CR: CR1.1 - Notes and Causes Problem
Viewed 2166 times since Fri, Nov 9, 2007
CR: Charts with large amounts of data
Viewed 2021 times since Fri, Nov 9, 2007
CR: Show every date on the chart
Viewed 2301 times since Thu, Jan 31, 2008
CR: A simple query pattern for summarizing raw data
Viewed 3029 times since Tue, Oct 6, 2009
CR: Why is my first row of data not included when I get my chart from Excel
Viewed 2202 times since Fri, Nov 9, 2007