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 3690 times since Fri, Nov 9, 2007
CR: Time Display
Viewed 2075 times since Fri, Nov 9, 2007
CR: The out-of-control (OOC) test may fail on the last sample with valid data
Viewed 2092 times since Fri, Nov 9, 2007
CR: Charting counted incidents
Viewed 1935 times since Thu, Feb 23, 2012
CR: Data grouping tardiness
Viewed 3161 times since Tue, Oct 6, 2009
CR: SQL Server Stored Procedures Returns No Records
Viewed 2269 times since Fri, Nov 9, 2007
CR: Charts with large amounts of data
Viewed 1914 times since Fri, Nov 9, 2007
CR: Background on out-of-control test rules
Viewed 2298 times since Fri, Nov 9, 2007
CR: Unable to fully display settings and windows
Viewed 1919 times since Fri, Jan 22, 2010
CR: Chart [the name of the chart] failed with one or more errors
Viewed 2068 times since Fri, Nov 9, 2007