1 of 1 people found this helpful
Good 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?
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.
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.
1 of 1 people found this helpful
Maybe 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
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!!
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
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.