2 Replies Latest reply on Oct 23, 2018 1:02 PM by Lisa Geller

# Formula to calculate the average # of patients per hour per day of the week

Hello,

I am trying to figure out a formula that will calculate the average # of patients who arrive to an ER per hour per day of the week.  I have a parameter that will allow me to choose any range of dates that I want.  My current formula is this:

COUNTD([Account Number])/DATEDIFF('week',[Parameters].[Start Date],[End Date]) .

I believe the numerator is calculating the avg patients /hour/weekday (I have weekday and hour of arrival on the dimension shelf) and the denominator is dividing by the number of weeks.  My problem is that if the user does not pick parameter dates that do not have an equal number of weekdays (ex - 2 Sundays, 2 Mondays, and so on) then the formula does not work correctly.  See the screen shot below - the parameter dates I picked are Jan1 2019 - Jan 8 2018, which has 1 Sunday, 2 Mondays, 1 Tuesday, 1 Wed, etc....).  This causes my Monday numbers to look like they are twice as big as the rest of the days of the week.

If I set a long range of dates in my parameter then the differences smooth out over time and you can't tell that the formula isn't correct but it is very obvious when you pick a small range of dates (like a period of 10 days or so).

I'm not sure if I need to change my formula or how the parameter is set up.  I am going to attach a workbook of dummy data in hopes that someone can help me figure this one out.

Thanks!

Lisa

• ###### 1. Re: Formula to calculate the average # of patients per hour per day of the week

Hi Lisa,

Please see newly attached 2018.2 workbook. I followed Jonathan Drummey's example for this same issue from this thread: Average Calls per Hour per Weekday  I think I got fairly close to what he'd done.  See if it works for you? I believe Jim Dehner is at the Tableau Conference, so probably unable to respond.  4-5 new calcs in the workbook.  Several Table Calc settings throughout.  Thx, Don

• ###### 2. Re: Formula to calculate the average # of patients per hour per day of the week

Thanks so much for looking Don. I am going to go over it closely tonight. I don't think it's correct though because I know that the arrivals arrange from about 1 patient per hour (in the middle of the night) to about 5 (mid afternoon). All of the values on the Y axis are less than 1 so I don't think it can be correct - but hopefully it will help me arrive at the solution.