Hourly Census graph

[#1]

I am trying to create a graph from data to show an x-axis that contains Care Units (for Emergency room patients in beds at the time) and the y-axis shows each hour of the day (in military time). So the y-axis will have 00 for midnight hour, 01 for 1am hour, 02 for 2am hour and so on through 23 for 11 pm hour.

The goal is to show a count of patients in the Emergency care rooms for each hour of the day (this will be used for staffing purposes). So, if a patient arrives at midnight and is in Care Unit 2 for 3 1/2 hours, then they will be counted for the 00 hour, 01 hour, 02 hour, and 03 hour on the graph for care unit 2. I have their arrive date/time and discharge date/time as well as their LOS (Length of stay). The length of stay is in hh:mm format. I am having trouble trying to figure out a way to graph this.

Any thoughts would be greatly appreciated since I am fairly new to QlikView.

Regards,

nmellick

[rwunderlich · Accepted solution]

One approach would be to generate a Calendar table of each hour and date. Then use IntervalMatch to connect the patient visits into the Calendar hours. See attached example.

-Rob

[#3]

Thanks so much Rob! This solution appears to work the way I need.

[#4]

I have Personal Edition so I'm unable to open your file. Could you upload a PE version or provide some details of your solution?

Many thanks

Jim

[rwunderlich]

I can't provide a PE copy, but here's the script.

data:

LOAD *,

  // Round down to get starting hour for IntervalMatch

  timestamp(floor(Arrive) + Maketime(hour(Arrive)), 'MM/DD/YYYY hh') as ArriveHour

;

LOAD

  Patient,

  Unit,

  timestamp#(Arrive, 'MM/DD/YYYY hh:mm') as Arrive,

  timestamp#(Discharge, 'MM/DD/YYYY hh:mm') as Discharge

INLINE [

Patient, Unit, Arrive, Discharge

P1, A, 01/01/2010 09:00, 01/01/2010 12:30

P2, A, 01/01/2010 09:30, 01/01/2010 10:30

P3, A, 01/01/2010 11:15, 01/01/2010 16:40

P4, A, 01/01/2010 18:30, 01/02/2010 02:10

P5, A, 01/01/2010 01:15, 01/01/2010 01:30

P6, B, 01/01/2010 10:00, 01/01/2010 11:30

P7, B, 01/01/2010 10:15, 01/01/2010 10:30

P8, B, 01/01/2010 10:30, 01/01/2010 15:15

P9, B, 01/01/2010 13:00, 01/01/2010 15:30

P10,B, 01/02/2010 01:15, 01/02/2010 05:30

]

;

// Get date range

date_temp:

LOAD

  min(floor(Arrive)) as mindate,

  max(floor(Discharge)) as maxdate

RESIDENT data

;

LET vMindate = peek('mindate')-1;

LET vMaxdate = peek('maxdate');

DROP TABLE date_temp;

// Generate a calendar table with one row for each hour each date

FOR i = 0 to 23 // Hour 0 to 23

  Calendar:

  LOAD *,

  timestamp(Date + Maketime($(i)), 'MM/DD/YYYY hh') as DateHour,

  $(i) as Hour

  ;

  LOAD

  date($(vMindate) + IterNo()) as Date

  AUTOGENERATE 1

  WHILE $(vMindate) + IterNo() <= $(vMaxdate)

  ;

NEXT i

// IntervalMatch to connect Patient visits to the Calendar table

IntervalMatch (DateHour)

LOAD ArriveHour, Discharge

RESIDENT data

;

[#6]

Thanks for responding so quickly Rob. I'll try this in my application.

Thanks again

Jim

[#7]

Hell Rob

Thanks again for your help with this however I'm struggling with how my load from a local Excel file fits in with your script. I've replaced your Inline section with my load, ie:

data:

LOAD *,

  // Round down to get starting hour for IntervalMatch

timestamp (floor(Arrive) + Maketime(hour(Arrive)),

'DD/MM/YYYY hh') as ArriveHour

;

LOAD

Patient,

Unit,

timestamp#(Arrive, 'DD/MM/YYYY hh:mm') as Arrive,

timestamp#(Discharge, 'DD/MM/YYYY' 'hh:mm) as Discharge

FROM

[Data_1.xlsx]

coxml, embedded labels, table is Sheet1);

    // Get date range

date_Temp:

....and so on but the vMindate and vMaxdate variables aren't picking anything up which is where I think the problem may be.

Any ideas?

Thanks again

Jim

[rwunderlich]

Hard to say without seeing your entire script. Can you post all of it?

-Rob

[#9]

I'm unable to paste directly into this reply box for some reason so I've attached a Word doc with the script. Hope this is okay.

Jim

[Anonymous]

Hi Rob,

Thanks for post the hourly census qvw, that's very helpful.

I have one question: it works perfectly, only when i select a certain month, the first day of the month wouldn't have the count right, because the census count also need to count in patients admitted from previous date so it's a rolling total count. How can i fix that?

thank you!

LeeAnn