Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

dezmundw15
New Contributor III

Calculate based on Weekday

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.

6 Replies

Re: Calculate based on Weekday

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
New Contributor III

Re: Calculate based on Weekday

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
Valued Contributor

Re: Calculate based on Weekday

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

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
New Contributor III

Re: Calculate based on Weekday

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.

dwforest
Valued Contributor

Re: Calculate based on Weekday

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)