Help calculate a percentage with a formula

[cyreports]

I need help with one formula (below) in snippet. I need the percent of Same day appointments by Doctor minus the canceled appointments.

I think the best way to describe this is by telling you about an example I have. I have 16 appointments on my demo schedule. 2 were scheduled appointments and 14 were same day walk in appointments.

2 scheduled appointments
14 same day walk in appointments

16 Total appointments

Of the 14 same day appointments 9 were canceled for various reasons. This means I have 5 same day appointments that did not cancel.

5 same day appointments that did not cancel.
16 total appointments

5/16 % = 31.25% (this is what I need and I am not sure how to do this).

My formula (not working as I need):

if ({#Scheduled Doctor} + {#Same Day Doctor}) <> 0 then
(
({#Scheduled Doctor} + {#Same Day Doctor}) /
(
{#Same Day Doctor} - ({#Reschedule Doctor} +
{#Patient Left Doctor} + 
{#No Show Doctor} +
{#Clinic CX Doctor} + 
{#Completed Appts Doctor} + 
{#Patient CX Doctor}
) 
))*100
else
0

 

[IanWaterman]

YOu will probably find that one or more of your Running totals are null, which will cause formual to fail.

Try changing RTs to return 0 if null encountered.

eg #No Show Doctor

If that is using a field eg NoShow try replacing with formula

@NoShow
If isnull(NoShow) then 0 else NoShow.

Ian

 

[Madawc]

5/16 % would be 5%16. Though 5/16 *100 would also work.

What you've got is a very complex formula - and you've not said exactly what was wrong. I suggest you break it down into individual tests or additions and then display them on a test version of your report, maybe a detail line that you can later suppress and remove.

If you are getting no output at all, you have hit a null. Do an IsNull test first.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

You should be testing whether the denominator is = 0 not the numerator.

This seems overly complex. Do you have some field that identifies an appointment as cancelled?

-LB

 

[cyreports]

LBass,

I will try to explain what I am doing a little better.

I have 8 statuses that I need to total back to 100%.

Scheduled Appointments
Same Day Appointments
Completed Appointments
No Show Appointments
Patient Canceled Appointments
Clinic Canceled Appointments
Patient Left Canceled Appointments
Rescheduled Canceled Appointments

On my schedule example, I have 16 total appointments. Total appointmnets is a count of Scheduled Appoinments + Same Day Appointments.

Scheduled Appoinments = 2
Same Day Appointments = 14

I have the following number of canceled/completed appointments.

Completed Appointments = 1
No Show Appointments = 1
Patient Canceled Appointments = 1
Clinic Canceled Appointments = 2
Patient Left Canceled Appointments = 1
Rescheduled Canceled Appointments = 3

A patient appointment can have a count in either the scheduled appointments or same day appointments slot. If the appointment is canceled or goes to a completed status, it will still have the count in the scheduled appointment or same day appointment and also have a count in that distinct field as well.

Because they can have a count in both, I was trying to deduct the canceled/same day from the Total appointments and then divide that distinct appt status into it. The running totals are nothing more than a sum of that status that resets on the change of the groupings.

I was able to re-work the same day for my Doctor grouping and did it with this formula:

({#Same Day Doctor} - ({#Reschedule Doctor} + {#Patient Left Doctor} +  {#No Show Doctor} + {#Clinic CX Doctor} +  {#Completed Appts Doctor} + {#Patient CX Doctor})) / ({#Scheduled Doctor} + {#Same Day Doctor})  * 100

I thought I could take same logic and create a formula for the Scheduled Appts and did this:

({#Scheduled Doctor} - ({#Reschedule Doctor} + {#Patient Left Doctor} +  {#No Show Doctor} + {#Clinic CX Doctor} +  {#Completed Appts Doctor} + {#Patient CX Doctor})) / ({#Scheduled Doctor} + {#Same Day Doctor})  * 100

I ended up with a % of -43.75 and not 12.5% as I thought I would get.

My number of scheduled was 2
Cancellations are 9

Ultimately what I am needing is to equal back to 100% across all these Appointment Statuses that I have in my formula. I have them all at the correct % with the exception of the Scheduled now.

 

[lbass]

You didn't answer my question.

-LB

 

[cyreports]

Do you have some field that identifies an appointment as cancelled?

Yes. Each of the canceled appointments are set up as Running Totals that sum that status and resets on the group for Doctor, Facility and Day.

The specific fields are:

[ado.Seen] for my Completed Appointments
[ado.No Show User] for my No Show Appointments
[ado.CX By Patient] for my Patient Canceled Appointments
[ado.CX by Clinic] for my Clinic Canceled Appointments
[ado.Patient Left] for my Patient Left Canceled Appointments
[ado.CX Reschedule] for my Rescheduled Canceled Appointments

Was this what you needed addionally from me? If not, I apologize and I will send you whatever you need in addition.

 

[lbass]

What values do they have? Do they show true or 1? Are they null if they don't apply or some other value?

-LB

 

[cyreports]

They show a 1 if they are true and if they are null they show a 0. The running totals I did sum them up and reset on the group.

 

[cyreports]

I ended up getting my percentages correct, however I ran into another issue.

I have {#Scheduled Doctor} - {#Scheduled Doctor New}. If I have nothing in {#Scheduled Doctor New} I am not getting a % back, only a blank space. Can I have it minus zero if null here to give me back a %?

({#Scheduled Doctor} - {#Scheduled Doctor New}) / ({#Scheduled Doctor} + {#Same Day Doctor})  * 100

 

[cyreports]

Please disregard the last question. I created another formula to set to 0 if null and inserted that into this formula. Thanks to all for the help.