Need help computing a weighted average 1

[Malekish]

Hi folks, hope you can help me work out the logic on a problem I have.

Average Speed of Answer (ASA) defined as Answer_Time * Handled_Calls.

Therefore, Answer_Time = ASA * Handled_Calls.

I receive my data in the following format:

Date ASA Calls_Handled
3/1/09 20 100
3/2/09 25 125
3/3/09 23 110
3/4/09 27 150

I want to compute the ASA for the time period of 3/1 - 3/4 therefore I need to do a weighted average of:

Answer_Time / sum(Handled_Calls) =
(20 * 100) + (25*125) + (23*110) + (27*150) / (100+125+110+150)

I'm passing a date range to my sub report in the form of a Start_Date and End_Date, they don't always start on the beginning of a month.

Can anyone think of a good way of looping thru the days computing the Answer_Time?

Crystal Reports version 11

Thanks much for any help!

 

[lbass]

Create a formula {@AnswerTime}:

{table.ASA}*{table.callshandled}

Then use a formula like this:

sum({@AnswerTime})/sum({table.callshandled})

This assumes you are limiting the data to the relevant dates.

-LB

 

[Malekish]

Revisiting an old post of mine because I need to refine it further.

Complicating matters not only do I have to compute the weighted average but I also need to have it grouped by project.

Sample Data

Project    Date    ASA   Calls_Handled
A          3/1/09   22             100
B          3/1/09   25             120
A          3/2/09   19              75
B          3/2/09   14              50

So my report I'm hoping to have (across a date range)

Project    ASA            Calls_Handled
A           24                     175
B           21                     170
...

Total       23                     345

ASA is a weighted average, to compute it is the same as above (for each date, AnswerTime := ASA*Calls_Handled)


The solution above from LB solves the problem on the Total line but I also need it by each project.

Thank you to everyone who posts suggestions to these forums. I lurk and learn far more than I ask!

 

[lbass]

Have you tried adding a group condition?

sum({@AnswerTime},{table.project})/sum({table.callshandled},{table.project})

I am unclear how the dates fit into all of this.

-LB

 

[Malekish]

Ok, I need to add the group onto each side.

I tried sum({@AnswerTime},{table.project})/sum({@CallsHandled}) and several variations thereof.

Depending on which system I'm pulling data out of some of them report AnsweredTime and others report Average Answer Time. I'm trying to be able to report on a like basis.

So if my user wants to see March 1-March 14, 2009 and comparing against March 1 - March 14, 2008. The new system is reporting Average Answer Time on a daily level, the old system used to report in AnsweredTime.

In order to compare like-to-like I needed to make a weighted average off the new system.