crosstab with weighted average problems

[aks12]

Hi, sorry the earlier incomplete post. I accidently hit submit instead of preview.

I am using Crystal 10 and am having a problem with a crosstab. I am calculating capture rates on a daily then weekly basis in any given month. Capture rate is calls captured/calls handled. I have columns for each day's totals, then a weekly column after the 7th, 14th, 21st, and 28th day to sum up the week. Below is example of what the crosstab looks like and sample values.

My formulas are:

{@calls_captured}
if isnull({TABLE.CALLS_CAPTURED}) then
0
else
{TABLE.CALLS_CAPTURED}

{@calls_handled}
if isnull({TABLE.CALLS_HANDLED}) then
0
else
{TABLE.CALLS_HANDLED}

{@calls_handledDen}
if {@calls_handled} > 0 then
{@calls_handled}
else
0.0001 //denominator cannot be zero

{@capture_rate}
if ({@CallsHandled} > 0) and ({@CallsCaptured} > 0) then
{@CallsCaptured} % {@CallsHandled}
else
0

The capture rate is calculated in the crosstab this way: weighted avg of @capture_rate with @calls_handledDen.

The daily percentage come out perfect, but in the weekly capture rate%, the percentage is ignoring calls captured on days where calls handled is 0 and is returning the wrong percentage for the week. In the example below, the weekly % should be 94.12, but it's ignoring the 3 calls captured from April 5.

How can I correct this problem and make the crosstab show the correct weekly percentage in a case like this? Thanks!

[tab][tab][tab]April
[tab][tab][tab][tab][tab][tab][tab]1[tab][tab]2[tab][tab]3[tab][tab]4[tab][tab]5[tab][tab]6[tab][tab]7[tab][tab]Week
-----------------------------------------------------------------------------
Calls Handled[tab][tab]5[tab][tab]3[tab][tab]4[tab][tab]3[tab][tab]0[tab][tab]3[tab][tab]1[tab][tab]17
Calls Captured [tab]3[tab][tab]1[tab][tab]4[tab][tab]1[tab][tab]3[tab][tab]3[tab][tab]1[tab][tab]16
Capture Rate%[tab]60[tab] 25 [tab]100 [tab]25 [tab]0 [tab]100[tab] 100 [tab]76.47

 

[lbass]

I can't recreate your problem in XI or in 8.5. The percentages are always correct. You do not need to use {@calls_handledden}, as your capture rate already takes care of any zeros in the denominator, but even using a formula like that, I still got the correct percentages.

Note that your calls handled row doesn't add to 17, but instead to 19.

Can you explain how you set up the week column versus the daily column?

-LB

 

[aks12]

Hi lbass,

Thanks for replying. You're right, I summed the daily values wrong in the example and the correct weekly percentage would be 84.21.

For the daily and weekly crosstab columns, I have two formulas. Also, note that there's a parameter for the start date.

{@day}
if {TABLE.DATEFIELD} < {?StartDate} then
'Total' //for dates outside the month range
else
totext(day({TABLE.DATEFIELD}),0)


{@weekly}
numbervar delta;
numbervar dayno;

delta := 8 - (DayOfWeek (date(year({TABLE.DATEFIELD}),month({TABLE.DATEFIELD}),1)));

dayno := day({TABLE.DATEFIELD});

if {TABLE.DATEFIELD} < {?StartDate} then 1
else if dayno >= 1 and dayno <= delta then 1
else if dayno >= delta+1 and dayno <= delta+7 then 2
else if dayno >= delta+8 and dayno <= delta+14 then 3
else if dayno >= delta+15 and dayno <= delta+21 then 4
else if dayno >= delta+22 and dayno <= delta+28 then 5
else 6

 

[lbass]

What is your record selection formula? I can't quite follow your logic here. Why are you including dates outside of the specific month? It looks like you are loading prior dates into week 1, but you are also picking up dates from different months in your weekly formula--do you mean to do that?

Maybe it would help if you explained what you are trying to do with the weeks/days. I'm assuming that weekly is your first column field, and that day is your second column field, and that you have no row field.

-LB

 

[aks12]

Here is the selection formula:

{TABLE.DATEFIELD} <= {?EndDate} and
{TABLE.DATEFIELD} >= date(year({?EndDate}),01,01) and
and dayofweek({TABLE.DATEFIELD}) <> 1 and
dayofweek({TABLE.DATEFIELD}) <> 7

As you can see, I'm getting all the data from Jan 1 of the year of the end date and ignoring all data from Saturdays and Sundays. The crosstab does report on dates prior to the specific month also, but just aggregates it all in one column called 'Previous Months.'

The crosstab columns come out in this order (using April 1 as start date and April 11 as end date):

- Total, Weekly, Monthly (under heading of Previous Months; these three columns always have identical values in the crosstab which is ok)
- each day in April (3,4,5,6,7)
- weekly total of these five days *
- each day in April (10, 11)
- weekly total of these two days *
- monthly total of April 1-11 *
- year to date total of Jan. 1 - April 11 *

The problem with the wrong percentages occurs in the columns with the asterisk (*).

The other column fields in the crosstab are:

{@month}
if {TABLE.DATEFIELD} < {?StartDate} then
"Previous Months"
else
["January","February","March","April","May","June","July","August","September","October","November","December"]
[month({TABLE.DATEFIELD})]


{@year}
year({TABLE.DATEFIELD})


In the cross-tab expert, my column fields are ordered
@year
@month
@weekly
@day

I am only using one row field in the crosstab; a ProductID field which is probably irrelevant to the problem at hand.

 

[aks12]

lbass,

I was able to change my @CaptureRate formula a little and get the correct weekly, monthly, and YTD percentages.
I changed it to:

if ({@CallsCaptured} > 0) then
{@CallsCaptured} % {@CallsHandledDen}
else
0

The tradeoff when I do this, however, is that on days where the sum of calls handled is zero, the capture rate% ballons to (@callscaptured % 0.0001.) If there were no calls handled for a day, I need that day's capture rate to show 0%.

In your initial reply, you said your percentages always came out correct. I have a feeling you didn't duplicate my crosstab setup, but if you did I'd love to know how you did it and came up with correct percentages across all the columns.

 

[aks12]

My final solution was to allow a day's capture rate to show 500000.00 in the crosstab when there were, for example, 0 calls captured and 5 calls handled. Then I decreased the column width a little bit so that only 00000.00 shows in the crosstab. Although I would have liked to have shown 0%, the end user of the report said this solution was fine since it is only very rarely that there will be a day with 0 calls captured and >0 calls handled.