
1. Re: Calculating Average # of Patient Arrivals per Hour per Weekday
Jim Dehner Oct 12, 2018 4:58 AM (in response to Lisa Geller)1 of 1 people found this helpfulGood morning Lisa
your formula is counting all the arrivals on by day of week and by hour and then divides by the number of weeks between the start and end dates  so if on the 3 Sundays between the start and end dates and the total count of arrivals in hour 6 is 15 then the formula will return 15/3 for 5
if that is your expectation it is working properly  you could use an lod expression but you don't need to the way you constructed the viz
Now the question I have is the reference line  if that is an Avg line it is taking the average of "avg hourly arrivals" for the 24 hour period in each weekday
is that what you wanted?
Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.

2. Re: Calculating Average # of Patient Arrivals per Hour per Weekday
Lisa Geller Oct 12, 2018 5:57 AM (in response to Lisa Geller)Thanks Jim! That was exactly my expectation so it is working properly. Great! For some reason  I can't seem to conceptualize in my head how COUNTD [Account Number]/DATEDIFF ('week') [start date], [end date] is actually calculating the average of a particular hour on a particular day. I understand the numerator is summing up the account numbers using my date filters by hour per day (because I have that set in the columns shelf) but the denominator is just baffling to me  it doesn't seem to be summing up a total number of arrivals  just looking at a date. I would think I would need the [Account Number] to appear in the denominator somewhere. Can you explain please?
As for the reference line  yes  I want it to show the daily average patient arrival so it is doing that.
Thanks again!

3. Re: Calculating Average # of Patient Arrivals per Hour per Weekday
Jim Dehner Oct 12, 2018 6:13 AM (in response to Lisa Geller)1 of 1 people found this helpfulMaybe I don't understand the goal
the numerator is going to count (and total) all the patient arrivals that happen on the day of the week  and you further broke that into hours  so it is already determining that on sundays within the date range the total number of admissions in the 10 hour was 60  or another way to say it is that there were 60 arrivals for the 3 week period  per "each Sunday " is then the total / number of Sundays  you determined that with the datediff formula 
The result of the formula in words is "on the average Sunday 10th hour there for the 3 week period there were 20 arrivals"
Sorry but I am an engineer by training  the result we are looking for is average patients / hour  the result of the numerator is avg patients / hour/total weeks  so to convert that to patients per hour we need to divide by the number of total weeks (or 3)
I understand that due to confidentiality you can't share data  but you can test the formula  take the formula apart in calculate the numerator separate from the denominator on a smaller subset of the data and see if you get what yo expect
Jim

4. Re: Calculating Average # of Patient Arrivals per Hour per Weekday
Lisa Geller Oct 12, 2018 6:38 AM (in response to Jim Dehner)100% understand now. I am a physician and new to Tableau and these calculated fields are killing me since I haven't had to do any real calculations since college. The numerator is giving me the number of patients arriving per day per hour and the denominator is simply dividing by the number of discrete days in the time period  which gives me my desired average.
Thanks again  very helpful!!

5. Re: Calculating Average # of Patient Arrivals per Hour per Weekday
Jim Dehner Oct 12, 2018 6:51 AM (in response to Lisa Geller)I understand the pain  Most of us came to Tableau from an excel background  Tableau and all other data base oriented systems are very different
BTW  one of my son's is a Dr  I have the utmost of respect for what y'all (I do live in the south) do
Jim

6. Re: Calculating Average # of Patient Arrivals per Hour per Weekday
Lisa Geller Oct 23, 2018 9:03 AM (in response to Jim Dehner)Jim
Im hoping that you see this because it has been nagging at me since I posted it and I think I know the issue 
You are correct when you say that the formula as written is calculating the avg number of patients per hour/total weeks/# total weeks. The problem is that that is not what I want my viz to show!!! I want it to show the avg number of patients per hour so the numerator is correct. HOWEVER  I need the denominator to divide by the # of the specific week days that are in the time period selected  which is not necessarily the total # of weeks.. For example, If I don't pick dates that include the same number on Sundays, Mondays, Tuesdays, etc. then I am getting an incorrect result.
Here's a screenshot:
See how Mondays average(7.92) is almost twice as big as Sunday's (4.39)? That's because my parameter is set to Jan 1st to Jan 8th, which contains 1 Sunday, 2 Mondays, 1 Tuesday, 1 Wed, 1, etc.. Changing the denominator of my formula to Datediff('weekday') does not word  the averages are very low  around 0.1 patients per day  which is definitely not correct.
I'm not sure if anyone will see this since this thread is so old so I am going to start a new thread also but thought I would try and post here just in case.
Lisa