I am new to Tableau and Data Analysis in general. I am unfamiliar with the proper terms/language that you might be accustomed to but will try to explain myself as briefly as possible.
Building a YOY Dashboard for several metrics. We have multiple therapists that work at multiple locations. Our EMR provides us line item date for each therapist at each clinic worked. So Therapist A will have 2 lines of data if they work in Clinic A and Clinic B in the same month. If I am looking at Jan-March and the therapist was in Clinic A for 3 months and spend any time in Clinic B during, say March... then my YOY calculations total up all 4 line items and divides by 4 for an average instead of adding all the metrics in March and then dividing by 3, for an accurate number.
Clinic A - Therapist A - 1.1.16 - Evaluations = 27
Clinic A - Therapist A - 2.1.16 - Evaluations = 28
Clinic A - Therapist A - 3.1.16 - Evaluations = 17
Clinic B - Therapist B - 3.1.16 - Evaluations = 1
What I want to see for an average of Evals/Month is 24.3. What I am getting is 18.25.
Our EMR does not generate per Day or per Week numbers. We also do not treat on weekends. So we have assigned .2 = 1 treatment day for our weeks/month column (22 days = 4.4 weeks). Actual amount of treatment days are also included in a separate column. With these numbers we calculate metrics like Evaluations/Week, Visits/Day, Visits/Week. I seem to get the same results in Tableau if I perform the calculations in Excel prior to importing the data as I do if I build the calculations into the workbook.
I am looking for a way to combine the data of visits, evals that are performed by the same therapist in a given month despite how many different clinics they worked. (This would be exhausting to do manually every week/month for our different reports)
I am attaching a Workbook with my efforts and sample data. Our database has many more therapist and clinics as well as metrics... but I think I pared it down to the nuts and bolts.
- In the workbook example: The totals for 2015 are all correct. For 2016 (current result/expected result) - Arrived Visits 1018/1018, Visits/Day - 5.98/6.84, Visits/Wk - 30/34, Evals/Wk - 4.2/4.8
Thanks for any assistance or guidance to resources.