Calculate a Static number

[dezmundw15]

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

[stevedark]

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

[dezmundw15]

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.

[stevedark]

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

[dezmundw15 · Accepted solution]

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.

[dezmundw15]

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

[stevedark]

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.

[dezmundw15]

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.

[dezmundw15]

Steve,

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

[stevedark]

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.