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

    Lisa Geller

      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