Calculate based on Weekday

[dezmundw15]

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.

[Anil_Babu_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))

[dezmundw15]

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?

[dwforest]

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)

[Anil_Babu_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))

[dezmundw15]

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:

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.

[dwforest]

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)