5 Replies Latest reply on Aug 18, 2016 4:37 AM by Luciano Vasconcelos

    Newbie:  Having difficulty with proper calculation of monthly averages when there are 2 line items for same month.

    Matt VanCampen

      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.