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

[Anonymous]

Hi Rob,

Thanks for post the hourly census qvw in Qlik community,  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? Really appreciate your help!

thank you!

LeeAnn

[rwunderlich]

LeeAnn,

It sounds like you have two dates: AdmitDate and DisChargeDate.  Can you post a sample?

-Rob

[Anonymous]

thanks for replying Rob!

Oh... I know why my first selected day is not correct, I have an existing calendar in my data model so when I replicate the hourly census I created a new Calendar.

So in the following look should I replace ' date($(vMindate) + IterNo()) as Date' to the date name in my existing data model?

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

[Anonymous]

Hi Rob

I have attached an example. Basically I already have an existing calendar in my app and it's linked to my 'Data' table by this key: %DateKey. If I were to implement my census using the existing calenda I'm gonna have to break the previous link by %DateKey and only let interval match to link the calendar and Data table. But I do need the previous linkage (%DateKey) for other things...

What should I do?

(in the attach, I commented out %DateKey field in Calendar and census works fine, but how should I modify it so I don't have to lose my previous linkage for it to work?)

thanks a lot for your help!!

LeeAnn