6 Replies Latest reply on Jan 25, 2019 3:10 PM by Lisa Geller

    Calculating Average # of Patient Arrivals per Hour per Weekday

    Lisa Geller

      I would appreciate any help with this.  I've looked through the forums but cannot seem to find any solutions that work with my dataset.


      I am trying to create an arrival curve for emergency department patients - basically a bar chart that shows the average number of arrivals that come to the emergency department each hour.  The user can adjust the dates they would like to look at with a parameter.  I have a dataset that is arranged by account number and has arrival date and time associated with each account number.


      The formula I am using to calculate the number of patients arriving per hour is:


      COUNTD([Account Number])/DATEDIFF('week',[Parameters].[Start Date],[End Date])  but I am not sure that this formula is calculating what I want it to - specifically, I don't think the denominator is correct.  I suspect I may have to make a LOD calculation instead?


      I cannot include my dataset due to privacy reasons but hopefully the attached screenshot shows what I am trying to achieve.






      Thanks all,


        • 1. Re: Calculating Average # of Patient Arrivals per Hour per Weekday
          Jim Dehner

          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.

          1 of 1 people found this helpful
          • 2. Re: Calculating Average # of Patient Arrivals per Hour per Weekday
            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

              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



              1 of 1 people found this helpful
              • 4. Re: Calculating Average # of Patient Arrivals per Hour per Weekday
                Lisa Geller

                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

                  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



                  • 6. Re: Calculating Average # of Patient Arrivals per Hour per Weekday
                    Lisa Geller



                    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.