16 Replies Latest reply: Mar 1, 2012 7:35 AM by Stefan Wühl RSS

    Pivoting on the count of patients

    John Anderson

      Hi All,

       

      I have been asked to build a table that shows the number of patients who have had their surgery postponed, and how often.

       

      for example, the table below shows that Hospital A had 50 patients who were postponed once, 35 were postponed twich and so on.

       

       

      Number of postponements1234

      Hospital A

      5035155
      Hospital B70402510

       

      This data could change depending on the date range selected.

       

      my data currently sits in table format.

      date
      patientHospital
      Jan-111001Hospital A
      Jan-111001Hospital A
      Feb-111001Hospital A
      Jan-111002Hospital A
      Feb-111002Hospital A
      Jan-111003Hospital A

       

      My question is this:  Keeping in mind that different date ranges can be selected by the user, How do I provide a count of number of patients postponed and pivot or split this by the count of how often?

       

      thank you,

       

      John.

        • Re: Pivoting on the count of patients
          Stefan Wühl

          John,

           

          maybe like attached?

           

          I used a data island for the Number of Postponed dates (top pivot table dimension), since I haven't succeeded using Valueloop() in this case.

           

          So my script is just:

           

          LOAD F1 as Date,

               patient,

               Hospital

          FROM

          [http://community.qlik.com/thread/45773?tstart=0]

          (html, codepage is 1252, embedded labels, table is @3, filters(

          Remove(Row, Pos(Top, 8))

          ));

           

           

          LOAD * INLINE [

          Num Postponed

          1

          2

          3

          4

          5

          ];

           

          And the pivot table has dimensions Hospital and [Num Postponed] and one expression:

          =sum(aggr(if(count(Date)=[Num Postponed] ,1),Hospital,patient,[Num Postponed]))

           

          If you select on different Dates / patients, the numbers seem to be correct to me.

           

          Hope this helps,

          Stefan

          • Re: Pivoting on the count of patients
            John Anderson

            Stefan,

             

            you are a genius!

             

            thank you.

             

            John.

              • Re: Pivoting on the count of patients
                Stefan Wühl

                No, not really ;-)

                 

                Actually, there is another, I believe more standard, way to approach such kind of histogram chart:

                 

                Don't create a data island, then create a pivot table chart with dimension Hospital and another calculated dimension

                =aggr(count(Date),patient)

                 

                and as expression

                =count(distinct patient)

                 

                This way your numbers of postponements are also dynamically calculated as calculated dimension.

                 

                As you see there are often sometime different approaches to achieve the same, each with its advantages and disadvantages.

                Just keep on playing a bit around with the flexibility of QlikView.

                 

                Regards,

                Stefan

                  • Re: Pivoting on the count of patients
                    John Anderson

                    Stefan,

                     

                    Thanks for this.  I tried this method but found the values of the pivot changed as I drilled down.  Ie: selecting the number 4 in the column headers which shows the count of how many patients have been postponed 4 times,  the expression value changes from 2 people to 1.  Whereas your previous method does not change.  When I check the underlying data I find that there is only 1 patient with 4 postponements, not 2.  So the first method you suggested is correct.

                     

                     

                     

                     

                     

                    Thank you again.  This is something new I have learned.

                     

                     

                     

                    Regards,

                     

                     

                     

                    John.

                      • Pivoting on the count of patients
                        Stefan Wühl

                        You're welcome.

                         

                        But it's strange that the value changes... If you like, you can post a sample that demonstrates what you see, I'll take a look then.

                         

                        Have fun with QlikView,

                        Stefan

                          • Re: Pivoting on the count of patients
                            John Anderson

                            Stefan,

                             

                            this issue with the values changing has reared its head again. and I am stumped.  Do you mind if I supply the QV for a second look?

                             

                            My example uses scrambled data and a reduced dataset.  But the example is in tact. 

                             

                            On the screen (with RED title bar) is the table I am interested in.  It should show the number of patients who have been postponed once, twice etc.  Because the dataset is so small we don't see anyone postponed more than twice.  But the total of postponements in this table equals 20, where the lower table which itemises each postponement shows 21.

                             

                            I have tried variations of the AGGR function to no avail.  I think it is related to the Postponement Reason being different for the same patient and that this may be counted as 1 instead of 2.  

                             

                            Regards,

                             

                            John.

                             

                            Message was edited by: janderson New attachment without Section Access. sorry.

                              • Pivoting on the count of patients
                                Stefan Wühl

                                I get 'Access denied to this QlikView document', do you have section access enabled?

                                  • Re: Pivoting on the count of patients
                                    John Anderson

                                    Hi Stefan,

                                     

                                    can you try my attachement again please?  I've removed the Section Access.

                                     

                                    Regards,

                                     

                                    John.

                                      • Pivoting on the count of patients
                                        Stefan Wühl

                                        John,

                                         

                                        I can now open your app, but I don't see postponements equal 20 resp 21, I see larger numbers (97, 105, 106 etc), so I am not sure if I am looking at the right stuff.

                                         

                                        You could try:

                                        =sum(aggr(if(count({$<CancelledProcedureFlag={1},BookingChangeReason={0}>}MonthYear)=NumberPostponed ,1),HospitalName,BookingNumber,BookingCancelReason ,NumberPostponed))

                                         

                                        And you need to consider that one table is showing the count of events per NumberPostponed, the other the number of postponed procedures, so one table will show e.g. 1 event for '2' NumberPostponed, the other 2 for the number of postponed procedures.

                                         

                                        Regards,

                                        Stefan

                                          • Pivoting on the count of patients
                                            John Anderson

                                            Stefan,

                                             

                                            I'm sure I tried this but maybe I didn't - there were a few combinations.  but thanks,  this has corrected the total to 106.

                                             

                                            Regards,

                                             

                                            John.

                                            • Re: Pivoting on the count of patients
                                              John Anderson

                                              Hi, its me again.  The above code gave me a correct answer with the reduced data set that I had at the time, but still comes up short when I reduce the number of filters.

                                               

                                              the current attachment shows the dilemma.  The aggregated table that you helped me build has a total of 1910 postponements but in a separate table I count 1916.

                                               

                                              if you could take one last look that would be great.

                                               

                                              thanks,

                                               

                                              john.

                                               

                                              Try again.  sorry about that.  its default on all our files.

                                                • Re: Pivoting on the count of patients
                                                  Stefan Wühl

                                                  I get 'Access denied' again...

                                                    • Re: Pivoting on the count of patients
                                                      John Anderson

                                                      Hi Stefan,

                                                       

                                                      I've uploaded the qvw again.  would you mind having another glance at it?

                                                       

                                                      thank you very much.

                                                       

                                                      John.

                                                        • Pivoting on the count of patients
                                                          Stefan Wühl

                                                          I have not fully understood the issue (mostly because I don't really know your data model), but it seems the problem arise from the fact that you essentially count MonthYear in your one table, and distinct WaitingListOfferID in the other.

                                                          I noticed that for some BookingNumbers, you have same DateValues on some Cancellations, but the DateValue or MonthYear only gets counted once.

                                                           

                                                          If I change the expression in the first table to

                                                           

                                                          =sum(aggr( if(count({$<CancelledProcedureFlag={1},BookingChangeReason={0}>} distinct WaitingListOfferId)=NumberPostponed ,1),  HospitalName,BookingNumber, NumberPostponed))

                                                           

                                                          then the numbers are consistent between the two tables.

                                                           

                                                          Hope this make any sense to you.

                                                           

                                                          Stefan

                                                            • Pivoting on the count of patients
                                                              John Anderson

                                                              Stefan,

                                                               

                                                              yes thank you that does help.  and it makes me think that maybe I'm growing too reliant on you and this community when I should be using my brain more.  I guess i doomed myself to failure by not fully understanding the Aggr function and how it was working although the logic you've provided isn't that hard to separate out.

                                                               

                                                              I should have done the fault checking more carefully instead of having you do it for me.

                                                               

                                                              Thank you.

                                                               

                                                              John.

                                                                • Pivoting on the count of patients
                                                                  Stefan Wühl

                                                                  No worries, John.

                                                                   

                                                                  It's always a healthy experience to see the effect of not chosing the right field and qualifier to count, also for me.

                                                                   

                                                                  If you get into performance problems using the distinct count on large volumes, you might want to have a look into the performance optimization chapter in the Manual, book III.

                                                                   

                                                                  Regards,

                                                                  Stefan