6 Replies Latest reply: Jun 20, 2018 3:26 PM by David Forest RSS

    Calculate based on Weekday

    Desmond Walker

      What you have helped me with has been a tremendous.  I have calculated my rates, but there is a small issue.  Related to the appointments per week as indicated in the email tread:

       

      Dr. X = 1.0 FTE is scheduled to have 32 appointments per week

      Dr. Y = .8 FTE is scheduled to have 28 appointments per week.

       

      When I calculate a couple rates, it is taking the Total Appointment sum (32) and calculating on that static number.

       

      However, I have a schedule for all of the Providers that detail how many appointments a Provider has per day.  For Example, Dr. X schedule is a follows:

       

      Monday     Tuesday     Wednesday     Thursday     Friday

      7                    7               7                    4                    7          = Total 32 Appointments

       

      However, if Dr. X does not see patients on Thursday, I want to indicate the Available Appointments for Dr. X for the Week of 5.21 - 5.27 was a Total of 28 appointments for that week.

       

      How can I utitlize this calculation?

       

             sum({<STATUS={'Seen','Pending Arrival'}>}PtCount) / sum(aggr(max(Monday, Tuesday, Wednesday, Thursday, Friday), Week, Provider))

       

      I substituted the static number of 32, to try to utilize the available appointments by weekday, but I get a dash as a result.

       

      Any help would be appreciated.

        • Re: Calculate based on Weekday
          Anil Samineni

          May be this?

           

          sum({<STATUS={'Seen','Pending Arrival'}>}PtCount) / sum(aggr(RangeMax(Only({<Week={'Monday'}>}Week),Only({<Week={'Tuesday'}>}Week),Only({<Week={'Wednesday'}>}Week),Only({<Week={'Thursday'}>}Week),Only({<Week={'Friday'}>}Week)), Provider))

            • Re: Calculate based on Weekday
              Desmond Walker

              Anil,

               

              Thanks for responding.  Your suggestion did not work.  Also, the Weekday is an actual field/column in my table, not a result, so does not need to be in quotes.  Can you give me a summary of what your formula is trying to accomplish?

                • Re: Calculate based on Weekday
                  Anil Samineni

                  If these are field, You may think this?

                   

                  sum({<STATUS={'Seen','Pending Arrival'}>}PtCount) / sum(aggr(RangeMax(RangeSum(Monday,Tuesday,Wednesday,Thursday,Friday),0), Provider))

                    • Re: Calculate based on Weekday
                      Desmond Walker

                      Anil,

                       

                      This is the actual formula that I am using based off or your suggestion:

                       

                      Count([PATIENT/DOB])/sum(aggr(RangeSum(RangeMax(Monday,Tuesday,Wednesday,Thursday,Friday),0),[Week Range],[Clinician-SEEN BY PROVIDER]))

                       

                      This script allows me to isolate a facility based on Patients seen and Available appointments for that site (i.e Clinic Y on Friday has 8 available appointments, 5 patients were seen that day so, 5/8 = 62.5%.

                       

                      The formula calculates that correctly, but for some reason if I want to look at the week totals, I get the following:

                      Capture.PNG

                      I am not sure why it calculates the Fill% as 275% for 1 week.  There are 32 slots for the week, there were 22 appointments for this clinician and 19 appointments were kept.  Any help would be greatly appreciated.

                • Re: Calculate based on Weekday
                  David Forest

                  Max works only on 1 field, since your days are fields, you need to create a measure for each day.

                    sum({<STATUS={'Seen','Pending Arrival'}>}PtCount) /

                  (sum(aggr(max(Monday), Week, Provider)) + sum(aggr(max(Tuesday), Week, Provider))... etc)

                  • Re: Calculate based on Weekday
                    David Forest

                    The formula references [Week Range], but your table shows Month. The level of detail in your table needs to match the level in your Aggr() (Or be at a more granular level, so [Week Range] works if it rolls up to a month in your model)