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 Patients/Seen
Dr. X Coach Therapist 32 7 7 7 4 7 15
Dr.Y Coach Therapist 32 7 7 7 4 7 25
Dr. Z Coach Therapist 32 7 7 7 4 7 19
Dr.M Coach Therapist 28 7 7 7 0 7 21
Dr. P Psychiatrist 24 8 8 0 0 8 20
Dr. O Psychiatrist 20 0 8 8 0 4 9
Dr. A Coach Therapist 7 0 0 7 0 0 5
Dr. B Coach Therapist 11 0 0 7 4 0 9
Dr.C Coach Therapist 31 7 7 8 4 5 27
I am trying to calculate Appointment statistics for my Providers. A Coach therapist is scheduled/contracted to have 32 Appointments each week, which is noted in the Total Availability_Productivity column. The Provider Schedule is a Static Schedule, but Patients seen will change on a weekly basis, in addition to a Provider taking the day off (i.e. Dr. X, took Thursday off (4 appointments), the Appointments available for that particular week will be 32-4=28 available appointments. Can anyone help with developing this calculation, accurate for the week, month, and year.
I have the following formula, but it's not working:
count([PATIENT/DOB])/Count(aggr (NoDistinct Monday,Tuesday,Wednesday,Thursday,Friday,[Week Range],[Clinician-SEEN BY PROVIDER]))
