Home » Categories » CHARTrunner

CR: A custom query for Length of Stay data

Revision Date: 2005-09-19

This article applies to CHARTrunner versions 1.6 and above.

Many customers have Excel data in a form similar to this:



1/1/2003 3
1/2/2003 2
1/3/2003 4
1/4/2003 2
1/5/2003 1

Imagine that you have data like this covering an entire year or even more. You might be interested to know how the average length of stay is changing throughout the year. There are several ways to go about this using CHARTrunner.

One approach is to define a control chart which treats the LOS column as either a measurement (individuals chart) or a count (c-chart). The problem with this approach is that you may have several thousand rows of data. Each row will create a data point on the chart. Although you will be able to see the average LOS, the chart could be quite congested and it may not show you how LOS is changing over time.

Another approach is to decide on some time interval for grouping the data. For example, with this data you might look at the average LOS by week or by month. To do this you could use the Advanced row selection feature. This is found on the data definition tab of the chart definition form. This allows you to do grouping based on the DischargeDate column and select the grouping units - such as week, month, quarter etc. This is a powerful feature but it does have limitations. For example, if you do advanced row grouping by month, and one of your months contains more than 100 entries the chart will not display. This is due to a subgroup size maximum of 100 for advanced row grouping.

Perhaps the best way to solve this problem is to use a custom SQL query and allow the query itself to do some aggregating of the data. This will be easier if you add a new column to your Excel sheet so that it looks like this:

DischargeDate LOS YearMonth
1/1/2003 3 2003 01
1/2/2003 2 2003 01
1/3/2003 4 2003 01
1/4/2003 2 2003 01
1/5/2003 1 2003 01

The YearMonth column can contain an Excel formula based on the DischargeDate column. Here is the formula used in the example:

    =YEAR(A2) &" " & TEXT(MONTH(A2),"00")

Next, we will create a new CHARTrunner chart that uses this Excel sheet as the data source. On the data definition tab, select Custom query  as the source for data records - then click on the Edit query button and type in the following custom SQL query:

SELECT Min(YearMonth) as MinYrMonth, format(avg(los),"0.00") as AvgLos FROM [Sheet1$] GROUP BY YearMonth ORDER BY YearMonth

Two things about this query will help us: 1) we are using the GROUP BY clause to aggregate the data by month and 2) we are using the avg() function to calculate an average length of stay for each month. When this query is run the result data will look like this:



2003 01 2.89
2003 02 3.07
2003 03 3.18
2003 04 3.11
2003 05 3.12
2003 06 2.94
2003 07 3.35
2003 08 3.18
2003 09 3.19
2003 10 3.30
2003 11 3.09
2003 12 3.09

In CHARTrunner you would treat the MinYrMonth as an identifier and you would treat the AvgLos as a measurement. The resulting chart will contain 12 data points; one for each month found in the data.

Now that you know how the average LOS is changing from month to month - you may also want to know how many data points were used to compute each monthly average. A slight change to the query we will give us this information as well. Here is the SQL statement:

SELECT Min(YearMonth) as MinYrMonth, format(avg(los),"0.00") as AvgLos , Count(YearMonth) as NumCases FROM [Sheet1$] GROUP BY YearMOnth ORDER BY YearMonth

When this query is run the result data will look like this:

MinYrMonth AvgLos NumCases
2003 01 2.89 37
2003 02 3.07 30
2003 03 3.18 38
2003 04 3.11 35
2003 05 3.12 33
2003 06 2.94 33
2003 07 3.35 34
2003 08 3.18 33
2003 09 3.19 31
2003 10 3.30 33
2003 11 3.09 32
2003 12 3.09 33

If you treat the NumCases column as an identifier this information may be displayed above the chart in the identifier grid. This way you will know both the average LOS and the number of cases considered.

In the attached .zip file you will find an Excel sheet and two chart definitions which demonstrate these concepts. Unzip these into some temporary folder, run CHARTrunner, set the working folder to this temporary folder - and then you can display the charts and study how they are defined.

Attachments (1) Attachments
Related Articles RSS Feed
CR: Grouping counts by date in Excel
Viewed 2257 times since Fri, Nov 9, 2007
CR: CR1.5 - My control chart data lines are not printing
Viewed 2262 times since Fri, Nov 9, 2007
CR: Multiple chart lines from one data column
Viewed 2357 times since Fri, Nov 9, 2007
CR: Charts not working on a new PC
Viewed 2118 times since Fri, Nov 9, 2007
CR: Unable to fully display settings and windows
Viewed 2045 times since Fri, Jan 22, 2010
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
CR: Some Excel Spreadsheet Values Seem To Be Missing
Viewed 2306 times since Fri, Nov 9, 2007
CR: The out-of-control (OOC) test may fail on the last sample with valid data
Viewed 2174 times since Fri, Nov 9, 2007
CR: Charting pass-fail data as a p-chart
Viewed 4740 times since Fri, Feb 29, 2008
CR: Too many decimal places in the chart data grid for an Excel column that is calculated by a formula
Viewed 3925 times since Fri, Nov 9, 2007