13 Replies Latest reply: Oct 8, 2016 8:02 AM by LeeAnn Huang RSS

    Hourly Census graph

      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

       

        • Hourly Census graph
          Rob Wunderlich

          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

            • Hourly Census graph

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

                • Re: Hourly Census graph
                  Jim McHale

                  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

                    • Re: Re: Hourly Census graph
                      Rob Wunderlich

                      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

                      ;

                        • Re: Hourly Census graph
                          Jim McHale

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

                          Thanks again

                          Jim

                          • Re: Hourly Census graph
                            Jim McHale

                            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

                            • Re: Re: Hourly Census graph
                              LeeAnn Huang

                              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

                      • Re: Re: Hourly Census graph
                        LeeAnn Huang

                        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

                          • Re: Hourly Census graph
                            Rob Wunderlich

                            LeeAnn,

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

                             

                            -Rob

                              • Re: Hourly Census graph
                                LeeAnn Huang

                                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

                                • Re: Hourly Census graph
                                  LeeAnn Huang

                                  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