Account for Zero

[Kmccormic]

Hi There:

I'm sure there is something simple I'm missing here. I want to take a number field (total number of seconds) divide it by a another field (number of calls) to get an average amount of time per call, displaying in hours, minutes and seconds. I keep getting the error "division by zero" and can't figure a way around it. It is possible to have no calls in a specific entry/interval. I'm working with multiple phone queues so I only want data from specific ones included. I keep trying to add an "Else" statement at the end to account for the dividing by zero scenario and get the error "the remaining text does not appear to be part of the formula."

So if I have
1 call wait total 30 seconds
1 call wait total 60 seconds
I should get 90seconds/2calls = ave wait 00:00:45

Here's my code:
------------
numberVar avedelay := ({iApplicationStat.CallsAbandonedDelay}/{iApplicationStat.CallsAbandoned});
numberVar hrs;
numberVar min;
numberVar ss;
Stringvar hhmmss;
hrs:= Truncate(Truncate(Truncate(avedelay/60)/60));
min := Remainder(Truncate(avedelay/60),60);
ss := Remainder(avedelay, 3600);

If {iApplicationStat.Application} = "Cust_Svc" then
If {iApplicationStat.Application} = "VRU_Transfer" then
If {iApplicationStat.CallsAbandoned}>0
Then
hhmmss := totext(hrs,"00") + ":" + totext(min,"00") + ":" + totext(ss,"00");

 

[CoSpringsGuy]

numberVar avedelay;
numberVar hrs;
numberVar min;
numberVar ss;
Stringvar hhmmss;

if {iApplicationStat.CallsAbandoned} > 0 then
avedelay := ({iApplicationStat.CallsAbandonedDelay}/{iApplicationStat.CallsAbandoned});

hrs:= Truncate(Truncate(Truncate(avedelay/60)/60));
min := Remainder(Truncate(avedelay/60),60);
ss := Remainder(avedelay, 3600);

If {iApplicationStat.Application} = "Cust_Svc" then
If {iApplicationStat.Application} = "VRU_Transfer" then
If {iApplicationStat.CallsAbandoned}>0
Then
hhmmss := totext(hrs,"00") + ":" + totext(min,"00") + ":" + totext(ss,"00");

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

sorry ... remove this part from your formula

If {iApplicationStat.Application} = "Cust_Svc" then
If {iApplicationStat.Application} = "VRU_Transfer" then
If {iApplicationStat.CallsAbandoned}>0
Then

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

numberVar avedelay;
numberVar hrs;
numberVar min;
numberVar ss;
Stringvar hhmmss;

if {iApplicationStat.CallsAbandoned} = 0 then
avedelay := 0 else
avedelay := {iApplicationStat.CallsAbandonedDelay}/{iApplicationStat.CallsAbandoned};

if avedelay <> 0 then (
hrs:= Truncate(Truncate(Truncate(avedelay/60)/60));
min := Remainder(Truncate(avedelay/60),60);
ss := Remainder(avedelay, 3600)
);

if avedelay = 0 then
hhmmss := "No results" else
If {iApplicationStat.Application} = "Cust_Svc" and {iApplicationStat.Application} = "VRU_Transfer" then
hhmmss := totext(hrs,"00") + ":" + totext(min,"00") + ":" + totext(ss,"00");

Are your calls abandoned delay and calls abandoned fields already summaries?

-LB

 

[Kmccormic]

Hi LB:

I thought they were but I see what you mean. I had thought the:
If {iApplicationStat.Application} = "Cust_Svc" then
If {iApplicationStat.Application} = "VRU_Transfer" then
section would automatically add them together, since in my mind I was saying "only inclde this field for for these queues.

What if I was to declare 2 more variables and then declare an addition statement for those variables, so something like:
stringvar totalwait;
stringvar totalcalls;

totalcalls := {iApplicationStat.CallsAbandoned} where ({iApplicationStat.Application} in ("Cust_Svc," "VRU_Transfer"))

totalcalls := {iApplicationStat.CallsAbandonedDelay} where ({iApplicationStat.Application} in ("Cust_Svc," "VRU_Transfer"))

Kara

 

[lbass]

Kara,

I wasn't paying attention when I wrote that clause using both instances of one field.

Please show some sample data at the detail level and also indicate what you are grouping on and whether you are averaging at the group or report level. It is unclear what exactly you are averaging.

-LB

 

[Kmccormic]

Hi LB:

I averaging in the group level by a date/time field to get output for each month. I want to know the average amount of time callers wait before they hang up, to get that I divide the wait time (a number field showing the number of seconds) by the number of callers who hung up/abandoned thier call.

Sample Data
11/2/09 8:30 AM
Name Abandoned Calls Abandon Wait Time
ACD_DN_APPLICATION 10 844
CUST_SVC 7 720
MASTER_SCRIPT 10 844
NACD_DN_APPLICATION 10 844
NETWORK_SCRIPT 10 844
OPERATOR 9 235
VRU_TRANSFER 1 93


The data is by 15 minute intervals. There are a lot more fields, but the number of calls and amount of wait time is what I'm looking to use. I need the applications of Cust_Svc and VRU_Transfer to be combined together. This is just one statistic of a larger report. Hope that clears up any confusion.

Kara

 

[lbass]

So you are not looking for an average per month, but instead, an average per row (with the exception of the two rows to be combined? The simplest solution would be to create an inner group based on the following formula {@Applic}:

if {iApplicationStat.Application} in ["Cust_Svc," "VRU_Transfer"] then
"Combined Group Name" else
{iApplicationStat.Application}

Then the average formula would be:

sum({iApplicationStat.CallsAbandonedDelay},{@Applic})/sum({iApplicationStat.CallsAbandoned},{@Applic})

CoSpringsGuy--sorry, I didn't see your posts before responding.

-LB

 

[lbass]

Forgot the zero issue. The formula should be:

if sum({iApplicationStat.CallsAbandoned},{@Applic}) <> 0 then
sum({iApplicationStat.CallsAbandonedDelay},{@Applic})/sum({iApplicationStat.CallsAbandoned},{@Applic})

-LB

 

[Kmccormic]

Thank you LB, thank you CoSpringsGuy:

I think I'm lost. I can see how declaring the variables and doing the truncating/remainder portion of the code works and is necessary to get it to display as a result in time format.

I don't see how this code says "use only the specified queues" but I did set it up as a separate formula, @Combined Applications
if {iApplicationStat.Application} in "Cust_Svc," "VRU_Transfer"] then
"Combined Group Name" else
{iApplicationStat.Application}


I tried to incorporate your formula LB, and I'm getting the error "Remaining text does not appear to be part of the formula" and I can't figure out why it's getting hung up on the last 3 lines.

numberVar avedelay;
numberVar hrs;
numberVar min;
numberVar ss;
Stringvar hhmmss;

If sum({iApplicationStat.CallsAbandoned},{@Combined Applications}) <> 0 Then
avedelay := sum({iApplicationStat.CallsAbandonedDelay},{@Combined Applications})/sum({iApplicationStat.CallsAbandoned},{@Combined Applications}) Else
avedelay := 0;

If sum({iApplicationStat.CallsAbandoned},{@Combined Applications}) <> 0 then (
hrs:= Truncate(Truncate(Truncate(avedelay/60)/60)));
min := Remainder(Truncate(avedelay/60),60);
ss := Remainder(avedelay,3600);
);


If sum({iApplicationStat.CallsAbandoned},{@Combined Applications}) <> 0 Then
hhmmss := totext(hrs,"00") + ":" + totext(min,"00") + ":" + totext(ss,"00");

 

[lbass]

You should have inserted a group on the formula that combines the two.

And you need to remove the extra semicolon from this section so that it reads:

If sum({iApplicationStat.CallsAbandoned},{@Combined Applications}) <> 0 then (
hrs:= Truncate(Truncate(Truncate(avedelay/60)/60)));
min := Remainder(Truncate(avedelay/60),60);
ss := Remainder(avedelay,3600) // remove this ;
);

-LB