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: Time Display
Viewed 2038 times since Fri, Nov 9, 2007
CR: The out-of-control (OOC) test may fail on the last sample with valid data
Viewed 2056 times since Fri, Nov 9, 2007
CR: General Filtering Tips
Viewed 2163 times since Fri, Nov 9, 2007
CR: CHARTrunner and Open Office
Viewed 2337 times since Fri, Nov 9, 2007
CR: Charting counted incidents
Viewed 1871 times since Thu, Feb 23, 2012
CR: Largest Subgroup Size Supported by CHARTrunner
Viewed 1982 times since Fri, Nov 9, 2007
CR: Label your X-Axis with more than one identifier
Viewed 2470 times since Tue, Oct 6, 2009
CR: Background on out-of-control test rules
Viewed 2261 times since Fri, Nov 9, 2007
CR: Formatting an Excel Date
Viewed 2399 times since Fri, Nov 9, 2007
CR: Making an X-bar chart with a single data column
Viewed 2028 times since Fri, Nov 9, 2007