10 Replies Latest reply: May 31, 2018 5:32 AM by Steve Dark RSS

    Calculate a Static number

    Desmond Walker

      I am in need of some help relative to a static number I have set in a table.  I am trying to calculate appointments.

       

      I have noted in a table all of the Providers the number of appointments they should have per week.

       

      For Example:

       

      Week of 4.30 - 5.06 = 31 appointments - - - Saw 18 patients

      Week of 5.07 - 5.13 = 31 appointments - - - Saw 25 patients

      Week of 5.14 - 5.20 = 31 appointments - - - Saw 20 patients

       

      I want to calculate Productivity for a Provider:

       

      Total of 93 appointments and Saw 63 patients

       

      How do I add the static Appointment in a Table to sum and then divide the count of patients seen. 

       

      I successfully determined my fill rate:

      Sum ([STATUS]='Seen')*-1/Count([PATIENT/DOB])....however there is another Status of 'Pending Arrival' that I want to add, when I try to add it comes back with nothing ( - ).

       

      Can someone help with both of these situations?

       

      Thanks,

       

      Desmond

        • Re: Calculate a Static number
          Steve Dark

          Hi Desmond,

           

          I can't quite work out what your table structure looks like.

           

          The thing which is failing in your code when you have more than one status is that you are not aggregating the STATUS field, rather referring to it in an expression which will return -1 for True, which you are then reversing.

           

          Try Set Analysis, like this:

           

          count({<STATUS={'Seen'}>}[PATIENT/DOB]) / count([PATIENT/DOB])

           

          To add in the other status then becomes this:

           

          count({<STATUS={'Seen','Pending Arrival'}>}[PATIENT/DOB]) / count([PATIENT/DOB])


          Doing a COUNT is always less efficient, and sometimes less reliable than a SUM, so you may want to consider adding a count to your table in the load script, like this;

           

          LOAD

            1 as PtCount,

            STATUS,

            [PATIENT/DOB],

            ... other fields...

           

          Your expression then becomes:

           

          sum({<STATUS={'Seen','Pending Arrival'}>}PtCount) / sum(PtCount)

           

          Hope that helps,

          Steve

          • Re: Calculate a Static number
            Desmond Walker

            Steve,

             

            Thank you so much for answering Part 2 of my question,  It worked!  I created the measure as a Master Item instead of Set Analysis.

             

            As it relates to the static table...I have all of the Providers noted in a table with the number of appointments they would have based on their Specialty and FTE.

             

            So, we have Coach Therapist X, 1.0 FTE would have 32 appointments per week (Static Field in Table), Coach Therapist Z .8 FTE, would have 28 (Static Field in Table) Appointments per week.  So the table for each week will have a static number of the appointments.  

             

            For Example: Coach Therapist X

             

            Week of 4.30 - 5.06 = 31 appointments - - - Saw 18 patients

            Week of 5.07 - 5.13 = 31 appointments - - - Saw 25 patients

            Week of 5.14 - 5.20 = 31 appointments - - - Saw 20 patients

             

            I want to calculate Productivity for a Provider:

             

            Total of 93 appointments and Saw 63 patients

             

            How do I add the static Appointment in a Table to sum and then divide the count of patients seen.

              • Re: Calculate a Static number
                Steve Dark

                You need to load the table with the appointments and the table with the possible appointments in separately, but ensure the field with the Provider in is named identically on both tables and have exactly the same values, so;

                 

                LOAD

                    Provider,

                    APW

                FROM AppointmentsPerWeek.xlsx;

                 

                LOAD
                  1 as PtCount,

                    Provider,

                    Week,

                    STATUS,

                    [PATIENT/DOB],

                  etc.

                 

                These two tables should then associate on load.

                 

                The expression, provided that you have both Provider and Week as dimensions in the table, would then be something like;

                 

                sum({<STATUS={'Seen','Pending Arrival'}>}PtCount) / max(APW)


                This will divide the number of patients seen by the number of appointments in that week.


                To get a value that aggregates up over a number of weeks you will need to count the APW for each week in play in the expression, so the expression will expand to:

                 

                sum({<STATUS={'Seen','Pending Arrival'}>}PtCount) / sum(aggr(max(APW), Week))


                This will count up the appointments for each week.  By the same token, if you want it to work across different providers, the expression becomes this:

                 

                sum({<STATUS={'Seen','Pending Arrival'}>}PtCount) / sum(aggr(max(APW), Week, Provider))


                This way, the APW for each provider in each week will be totalled up, so the ratio across both weeks and providers can be calculated.

                 

                If you are putting the expression in a master measure then you will need to ensure that it has that Aggr code in there.

                 

                Hope I have understood the requirement and this helps you towards meeting it.

                 

                Regards,

                Steve

                  • Re: Calculate a Static number
                    Desmond Walker

                    Steve,

                     

                    What you have helped me with has been a tremendous.  I have calculated my rates, but there is a small issue.  Related to the appointments per week as indicated in the email tread:

                     

                    Dr. X = 1.0 FTE is scheduled to have 32 appointments per week

                    Dr. Y = .8 FTE is scheduled to have 28 appointments per week.

                     

                    When I calculate a couple rates, it is taking the Total Appointment sum (32) and calculating on that static number.

                     

                    However, I have a schedule for all of the Providers that detail how many appointments a Provider has per day.  For Example, Dr. X schedule is a follows:

                     

                    Monday     Tuesday     Wednesday     Thursday     Friday

                    7                    7               7                    4                    7          = Total 32 Appointments

                     

                    However, if Dr. X does not see patients on Thursday, I want to indicate the Available Appointments for Dr. X for the Week of 5.21 - 5.27 was a Total of 28 appointments for that week.

                     

                    How can I utitlize this calculation?

                     

                           sum({<STATUS={'Seen','Pending Arrival'}>}PtCount) / sum(aggr(max(Monday, Tuesday, Wednesday, Thursday, Friday), Week, Provider))

                     

                    I substituted the static number of 32, to try to utilize the available appointments by weekday, but I get a dash as a result.

                     

                    Any help would be appreciated.

                    • Re: Calculate a Static number
                      Desmond Walker

                      Steve,

                       

                      This a sample of the table that I have in Qlik Sense for the Provider Schedule

                       

                      Clinician                 Total Availability_Productivity   Monday  Tuesday  Wednesday  Thursday  Friday

                      Dr. X Coach Therapist                       32                             7               7                  7                   4          7

                      Dr.Y Coach Therapist                        32                             7               7                  7                   4          7

                      Dr. Z Coach Therapist                       32                             7               7                  7                   4          7

                      Dr.M Coach Therapist                       28                             7               7                  7                   0          7

                      Dr. P Psychiatrist                               24                             8              8                  0                    0         8

                      Dr. O Psychiatrist                               20                            0               8                 8                    0         4

                      Dr. A Coach Therapist                         7                             0              0                 7                    0          0

                      Dr. B Coach Therapist                       11                             0              0                 7                     4         0

                      Dr.C Coach Therapist                        31                             7              7                 8                    4          5

                        • Re: Calculate a Static number
                          Steve Dark

                          The max function does not work over a list of fields like you have.  To get the max you would use RangeMax, but this would just return 7.  To get 28 you would need RangeSum, or put a + symbol between each.

                           

                          Could you not just use TotalAvailability_Productivity though?

                           

                          The best way of doing this - to allow selections and viewing things by day - is to do a Crosstable load to bring each Clinician's data in across seven rows.  Google Crosstable to find details of how to do this.

                           

                          I notice that you have Clinician in your table, but Provider in your AGGR statement.  Make sure you have those fields matched, or you will get the max across all clinicians rather than the max for each clinician.

                            • Re: Calculate a Static number
                              Desmond Walker

                              Steve,

                               

                              Thanks for allowing me to follow you.  I am still very new to Qlik Sense and I am amazed with how great it is.  I am sure I am not using it to its full potential, but I have to start somewhere.

                               

                              The reason I don't want to use TotalAvailability_Productivity is because the calculation only works if the Provider does not take any days off.

                               

                              So if Dr. X is contracted to have 32 appointments per week but saw 22 Patients due to cancellation or No Show then the calculation works, because the amount is static.  However if Dr X was scheduled to have 32 appointments for the week, but was ill on Monday, then the TotalAvailability for that week would be 25, but Dr. X saw 22 patients again, the Rate would be greater, but the correct appointments for the week would be correct.

                               

                              So, instead of using the static number or the weekly contracted appointments, that I could use the actual Provider schedule to get the best Rate.

                                • Re: Calculate a Static number
                                  Steve Dark

                                  In that case you definitely want to crosstable values, so;

                                   

                                  CROSSTABLE (Day, Availability, 2)

                                  LOAD
                                    Clinician,

                                    [Total Availability_Productivity] as [Total Availability],

                                    Monday,

                                    Tuesday,

                                    Wednesday,

                                    Thursday,

                                    Friday

                                  FROM ... your data source ...

                                  ;

                                   

                                  You will then find that you can total availability across days, or pick individual week days.

                                   

                                  Presume that your logic wants to say that if no appointments happened on a day that all appointments are ignored, but if one patient is seen then you assume the clinician is there and therefore that would lower the ratio?

                                   

                                  What you probably need to do is join the availability table on clinician and day of the week.  You can do that with a composite key, you can create that on the crosstable above with a resident load, so...

                                   

                                  tmpAvail:

                                  CROSSTABLE (Day, Availability, 2)

                                  LOAD
                                  ... as above ...

                                  FROM ... your data source ...

                                  ;

                                   

                                  Avail:

                                  LOAD

                                    Left(Day, 3) & Clinician as ClinicianDayKey,

                                    Availability

                                  RESIDENT tmpAvail

                                  ;

                                   

                                  DROP TABLE tmpAvail;

                                   

                                  Then when you load the appointments table you would need to create the same key, something like this:

                                   

                                  LOAD

                                      if(match(STATUS,'Seen','Pending Arrival') > 0, WeekDay(AppointmentDate) & Clinician, null()) as ClinicianDayKey,

                                     Clinician,

                                     Day,

                                     STATUS,

                                     ... rest of data load ...

                                   

                                  This way any appointment that happened would associate with the full number of appointments for that clinician for that day of the week.  If no appointments happen then that key would be missing, so the number of appointments would then not happen.

                                   

                                  Hopefully that points you in the right direction for the next step?

                                • Re: Calculate a Static number
                                  Desmond Walker

                                  Steve,

                                   

                                  Below is the outcome of the formulas.  Can you provide any insight as to why I have unreported areas? 

                                   

                                  Rate Capture.PNG

                                    • Re: Calculate a Static number
                                      Steve Dark

                                      Usually those kind of issues come from having more than one value in a dimension in that cell, and can sometimes be fixed with changing the AGGR statement.


                                      As it is rates that are broken I would add columns that show both the enumerator and the divisor for the rate, so you can see whether it is one or the other that is broken.