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.

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.

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

I suggest you to review your data.

Checking i Tableau it seems correct.

Rounding 239/8 gives 30, not 34 as you expect.

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

The fact that Tableau is rounding 239/8 is my problem.  I need to to round by 7... 239/7.  (in my data there are 7 months (Jan-July).  In 2016 the therapist worked at 2 locations in March, so there are 2 lines of data for March.  Tableau is adding up all separate months (Jan, Feb, Mar, Mar, Apr, May, Jun, Jul), duplicating Mar, and dividing by 8.

I am attaching a screen shot from the calculations in Excel to help clarify.  Thanks for the help.

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

When you say average you count number of records.

If you want divide by distinct months, you have to sum value and divide by distinct number of months.

I did one.

1 of 1 people found this helpful
• ###### 4. Re: Newbie:  Having difficulty with proper calculation of monthly averages when there are 2 line items for same month.

Lucanio,

Thank you!!!  That little gem will help me in so many of my workbooks and calculations.  Appreciate the time.  Thanks for helping.

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

I gave you one because learning is more important than make if you're not in a hurry.

Besides, i believe this solve all your problems.

Regards.